How to Use AI to Write Excel or Google Sheets Formulas From Plain English

The problem

A lot of office staff know what they want a spreadsheet to do but do not know the exact formula syntax. They know the business rule. They do not know whether they need SUMIFS, IF, COUNTIF, XLOOKUP, FILTER, or a nested formula.

This workflow is for admins, coordinators, clinic staff, school staff, operations teams, and anyone who works in Excel or Google Sheets but does not want to spend time hunting through formula documentation.

The goal is simple: describe the calculation in normal language, let AI draft the formula, then verify it in a small test range before you fill it down.

Prerequisites

• A ChatGPT plan that supports file uploads and data analysis
• An Excel or Google Sheets file with clear column headers
• A few sample rows you can use to test before applying the formula broadly
• Enough familiarity with your sheet to explain what each column means

The simplest workflow

1. Clean the sheet before you ask for help. Make sure the first row contains clear headers and each row is one record. ChatGPT works best when spreadsheets have descriptive column headers, one row per record, and no extra blank rows or columns.
2. Upload the spreadsheet or paste a small sample table into ChatGPT. If the sheet contains sensitive information, remove it first or replace it with safe sample data.
3. Describe the business rule in plain English. State the columns involved, the desired output, and whether you are using Excel or Google Sheets.
4. Ask for three things in one prompt: the formula, a plain-English explanation, and one or two sample test cases using your column names.
5. Paste the formula into one test cell only. In Excel, formulas use cell references, ranges, and functions. Microsoft’s formula documentation is still useful here because it helps you spot when the AI used the wrong reference style or wrong range.
6. Verify the result against rows where you already know the right answer. Do not fill the formula down until at least three to five sample rows behave exactly as expected.
7. Once the test works, fill down or convert it into a reusable pattern for the rest of the sheet.

Tool-specific instructions

In ChatGPT

Upload the spreadsheet if you have it. If not, paste a small mock table like this:

Employee | Department | Hours | PTO Used | Remaining PTO
Jordan   | Billing    | 40    | 8        | ?
Mina     | Front Desk | 32    | 16       | ?
Luis     | Ops        | 40    | 0        | ?

Then ask for the formula in the exact platform you use. Excel and Google Sheets formulas often overlap, but not always.

In Excel

Microsoft’s formula help pages are the fastest way to sanity-check a formula after AI writes it. Two common issues are:

• relative references changing when copied
• absolute references not being locked with dollar signs

If the formula works in the first row but breaks when copied down, this is usually the problem.

In Google Sheets

Use the same workflow, but tell ChatGPT that the target platform is Google Sheets. If the formula involves array behavior, filter logic, or dynamic ranges, that detail matters.

Copy and paste prompts

Prompt 1: basic formula request

{
  "task": "Write a spreadsheet formula from plain English",
  "platform": "Excel or Google Sheets",
  "instructions": [
    "Use the column names exactly as provided.",
    "Return only one primary formula first.",
    "Explain the logic in plain English.",
    "Then give 3 quick test cases using the sample rows."
  ],
  "sheet_context": {
    "columns": ["Employee", "Department", "Hours", "PTO Used", "Remaining PTO"],
    "goal": "Remaining PTO should equal 80 minus PTO Used.",
    "starting_row": 2,
    "target_column": "Remaining PTO"
  }
}

Prompt 2: conditional logic

{
  "task": "Write a spreadsheet formula with conditional logic",
  "platform": "Excel",
  "instructions": [
    "Use Excel syntax.",
    "Assume the headers are in row 1 and data starts in row 2.",
    "Explain whether the formula uses relative or absolute references.",
    "Point out any reason the formula might fail when copied down."
  ],
  "sheet_context": {
    "columns": ["Status", "Due Date", "Completed Date", "Flag"],
    "goal": "In the Flag column, show Overdue if Status is not Complete and Due Date is before today. Otherwise show blank."
  }
}

Prompt 3: lookup formula

{
  "task": "Write a lookup formula for a spreadsheet",
  "platform": "Google Sheets",
  "instructions": [
    "Use the simplest reliable formula available.",
    "Explain what each part of the formula does.",
    "Show one example with exact cell references."
  ],
  "sheet_context": {
    "sheet_1": "Roster",
    "sheet_2": "Rates",
    "goal": "Pull the hourly rate from Rates into Roster based on Employee ID."
  }
}

Quality checks

Use this quick checklist before you trust the formula:

1. Does the result match a row where you already know the answer?
2. Does the formula still work after copying it down three or more rows?
3. Are the cell references supposed to move, or should some be locked with dollar signs?
4. If it is a lookup, did AI reference the correct key column?
5. If it is a date-based formula, does it behave correctly for blank cells?

A good extra check is to ask ChatGPT to explain the formula step by step. If the explanation does not match your business rule, the formula is probably wrong.

Common failure modes and fixes

The formula works in one row but breaks when copied

Cause: wrong reference type.
Fix: ask ChatGPT to rewrite the formula with the correct mix of relative and absolute references.

The formula returns the wrong answer for blanks

Cause: the formula does not handle empty cells.
Fix: ask for a version that returns blank when required fields are empty.

The formula uses the wrong function for the platform

Cause: Excel and Google Sheets are similar but not identical.
Fix: say explicitly which platform you are using and ask for that exact syntax.

The formula is technically correct but too hard to maintain

Cause: AI sometimes optimizes for cleverness instead of readability.
Fix: ask for the simplest readable version, even if it is longer.

A practical example

Say you need a status formula for invoice follow-up.

You want this rule:

• Show Ready to Chase if invoice status is Sent, payment received is blank, and due date is before today
• Otherwise show blank

That is a great AI formula use case because the rule is easy to describe in English but easy to get wrong by hand if you rarely build formulas.

Final takeaway

The best use of AI here is not replacing your judgment. It is converting your business rule into a first draft formula faster than you could build it from scratch.

Describe the rule clearly, test it on known rows, then fill it down only after it proves itself.

Sources Checked

• OpenAI Help Center, "Data analysis with ChatGPT" - accessed 2026-03-08
• OpenAI Help Center, "File Uploads FAQ" - accessed 2026-03-08
• Microsoft Support, "Use cell references in a formula" - accessed 2026-03-08
• Microsoft Support, "Switch between relative and absolute references" - accessed 2026-03-08

Quarterly Refresh Flag

Review by 2026-06-08 to confirm ChatGPT file upload limits, spreadsheet support details, and any changes to Excel or Google Sheets formula behavior.