Excel formulas from plain English

2026-10-15

Most people don't struggle with what they want from a spreadsheet. They struggle with how to say it in Excel. "Total sales for each rep in the North region last month" is a perfectly clear question; SUMIFS with five arguments and two date conditions is not.

AI assistants are genuinely good at this translation. They're also capable of giving you a formula that looks right, runs without an error, and returns the wrong number. This guide shows how to get the first without the second.

Why formulas from AI go wrong

When an AI-written formula fails, it's rarely because the model doesn't know Excel. It's because it didn't know your sheet:

• It guessed the layout. It assumed dates were in column A when they're in column C, or that data starts in row 1 when there's a header.
• It guessed the version. It used XLOOKUP or FILTER, which don't exist in Excel 2016.
• It guessed what you meant. "Last month" — calendar month or the last 30 days? "Sales" — including GST or not?
• Your data isn't what it looks like. Dates stored as text, numbers with trailing spaces, "North " with a space. The formula is correct; the data defeats it.

Every one of these is fixed by giving better input and testing the output.

Step 1: Describe the layout precisely

Before you ask for anything, write down:

• Which app and version — Excel 365, Excel 2019, Google Sheets. It decides which functions are available.
• Sheet names and columns — "Sheet Sales: A = Date, B = Region, C = Rep, D = Amount."
• Where data starts — "headers in row 1, data from row 2 to about row 3,000."
• Where the formula goes — "Summary!B2, then copied down for each rep listed in column A."

A screenshot of the top few rows (with any private data blurred) does the same job and catches things you'd forget to mention.

Step 2: Say what you want, including the edge cases

Describe the result in plain words, then add the rules a human would ask about:

Total sales for each rep in the North region in September 2026. Include sales on 30 September. Ignore rows where Amount is blank. Dates are Australian format (DD/MM/YYYY).

That last sentence matters more than people think: Excel interprets dates according to your system's regional settings, and a sheet exported from a US system can quietly turn 3 February into 2 March.

Step 3: Ask for an explanation, not just a formula

A formula you don't understand is a formula you can't check. Ask for a part-by-part breakdown. For the example above, a good answer is:

=SUMIFS(Sales!$D:$D, Sales!$C:$C, $A2, Sales!$B:$B, "North", Sales!$A:$A, ">="&DATE(2026,9,1), Sales!$A:$A, "<"&DATE(2026,10,1))

• SUMIFS(Sales!$D:$D, …) adds up Amount, but only for rows that pass every condition.
• Sales!$C:$C, $A2 — the rep must match the name in column A of the current row. The $ before A keeps the column fixed when you copy across; the row changes when you copy down.
• Sales!$B:$B, "North" — region must be North.
• The two date conditions take everything from 1 September up to, but not including, 1 October. Using "less than the first of next month" avoids missing sales on the 30th.

If any part of the explanation doesn't match what you meant, you've caught the error before it reached a report.

Step 4: Test on a tiny table first

This is the step that turns "looks right" into "is right". Make a small table — six or eight rows — where you can work out the answer in your head, including the awkward cases:

The right answer for Aroha is 1,500 (2 and 30 September only). If your formula says 2,200 or 1,200, a boundary condition is wrong — and you'd never have spotted it in 3,000 rows.

Step 5: When the result is 0 or suspiciously low, check the data

Most "wrong formula" problems are really data problems. The usual suspects:

• Dates stored as text. Test with =ISNUMBER(A2). TRUE means a real date. FALSE means Excel sees text; fix it with Data → Text to Columns → Date (DMY).
• Numbers stored as text. A small green triangle in the cell corner. Convert with the warning menu, or multiply by 1.
• Extra spaces. "Aroha " doesn't match "Aroha". Clean with =TRIM().
• Inconsistent categories. "North", "Nth", "north region". SUMIFS ignores case but not spelling.

If your data has many of these, clean it first. The clean up a messy spreadsheet case takes a CSV export, lists every problem it finds, and returns a cleaned file with a change log so you can see exactly what was altered.

Excel or Google Sheets?

Most everyday formulas — SUMIFS, COUNTIFS, IF, VLOOKUP, INDEX/MATCH — work the same in both. The differences show up with newer functions:

• XLOOKUP exists in Excel 365 and 2021 and in Google Sheets, but not Excel 2016 or 2019. Use INDEX/MATCH there.
• FILTER, UNIQUE and SORT spill results in Excel 365 and Sheets; older Excel has no equivalent.
• Google Sheets has QUERY, which can replace a pivot table with one formula; Excel has no direct equivalent.

Always say which app you're using, and ask for an alternative if you share the file with people on older versions.

A prompt you can reuse

Whatever tool you use, this structure gets reliable answers:

I'm using [app and version]. My data is on sheet [name]: [column letters and what each holds], headers in row [n], data from row [n] to about [n]. In [cell] I want [result in plain words, with edge cases]. Give me the formula with my real cell references, explain each part, give a small test table with the expected result, and list ways my data could make it return the wrong answer.

Our Excel formula case is that prompt with a form around it: fill in the fields, attach a screenshot if you like, and you get the formula, the explanation, an Excel/Sheets alternative and a test table.

When a formula isn't the answer

Sometimes the honest answer to "what's the formula for…" is "you need a pivot table" or "you need a different summary". If you're trying to understand a whole dataset rather than calculate one number — trends, outliers, what changed — export it as CSV and ask for an analysis instead. The CSV insights case gives you the shape of the data, the trends worth noticing and plain-language recommendations.

The short version

1. Describe the app, the layout and where the formula goes.
2. Say what you want in plain words, with edge cases and date format.
3. Ask for an explanation and read it.
4. Test on a tiny table where you know the answer.
5. When it's wrong, suspect the data before the formula.

Do that, and AI turns from a source of plausible-looking formulas into a fast, patient spreadsheet colleague.