Ask ten people what went wrong with an AI-generated formula and nine describe the same thing. It looked right. They pasted it in. It threw an error, or worse, it returned a number that was quietly wrong. Nearly every one of those failures traces back to something the assistant was never told.
So this guide is mostly about the prompt. What a complete one contains, how to check the result in under a minute, and the specific cases where you should expect to finish the job yourself.
What a good formula prompt contains
A complete request answers four questions. Leave one out and the assistant fills it in with a guess, which is where the trouble starts.
| Question | What to say | What happens if you skip it |
|---|---|---|
| Where is the data? | Sheet names and ranges, or the header names | It invents column letters that don't match yours |
| What identifies a row? | The key column, and whether keys repeat | You get the first duplicate, silently |
| What comes out? | A number, a label, a date, a spilled range | You get text where you needed something to sum |
| What about the awkward rows? | Blanks, no match, zero, negatives | Errors turn up later, usually in front of someone |
Sheet `Sales` has headers in row 1: A=Date, B=Rep, C=Region, D=Amount, rows 2:8000. Give me a formula for total Amount for the rep named in G2, only for dates in the current quarter. Treat blank Amounts as zero. Google Sheets.Describe the result, not the function
Most people prompt with the function they half-remember. "Write me a SUMIF." But naming the function constrains the answer, and it's often the wrong constraint. SUMIF can't take two criteria. VLOOKUP can't look left. You've narrowed the search before anyone knew what you needed.
Say what you want instead. "Total revenue for this region in this month." Let the assistant pick the function. If you do have a reason to prefer one, because a colleague has to maintain it or the file opens in an old version, say that as a constraint rather than as the request.
Check it in sixty seconds
A formula that produces a number is not the same as a formula that produces the right number. Three checks catch nearly everything.
Test a row you can do in your head
Pick a case with an obvious answer. If the formula and your mental arithmetic disagree, you've found the bug in ten seconds rather than at quarter end.
Break it deliberately
Feed it a blank, a duplicate key, a value with no match, a zero. All of those exist somewhere in your real data. Watch what comes back.
Check the range reaches the bottom
A range ending at row 500 in a sheet with 900 rows is the classic silent error. Use whole-column or table references where you can.
Where AI-written formulas go wrong
Spill behaviour
Dynamic arrays cause most of the "it works in one cell but not when I drag it" complaints. You may get a spilling formula when you wanted one value per row, or the reverse. Say which you want. "One formula in the top cell that spills down" or "a formula I can fill down each row" is enough.
Numbers that are secretly text
If the column came from a CSV, the dates may be text and the numbers may be carrying invisible non-breaking spaces. The formula is fine, the data isn't. When a SUMIFS returns zero for criteria you can see in the sheet, this is almost always why. Check =ISNUMBER(D2) before you rewrite anything, and see the cleaning checklist for the repair.
Links to other files
External references break in ways that are painful to debug and worse to inherit. If an assistant suggests one, ask whether the data could be imported into a tab instead. A stale link that still returns cached values is one of the nastier things that can happen to a workbook.
Volatile functions in big sheets
OFFSET, INDIRECT, NOW and friends recalculate on every change. Scatter them through 20,000 rows and you get a workbook that hangs for eight seconds after every keystroke. Ask for a non-volatile version. INDEX usually replaces OFFSET without any fuss.
Prompt patterns worth stealing
Return the price from Products!A:D (A=SKU, D=Price) for the SKU in B2. If the SKU isn't there, return "Not listed" rather than an error.Count rows in Tickets!A2:E9000 where E="Open" and the date in A falls in the last 30 days.Compare Emails!B and Signups!C, matching case-insensitively and ignoring leading and trailing spaces on both sides.Explain this formula step by step, then tell me which parts break if someone inserts a column at C.Converting formulas between Excel and Sheets
This is one of the more reliable AI jobs, because the mapping is mechanical rather than creative. Array behaviour, ARRAYFORMULA wrapping, QUERY versus filtering, and the handful of functions with different names. Say which direction you're going and test on a sample before you migrate anything that matters. The formula converter does this in both directions.
When the assistant can see the sheet
Everything above is really about compensating for an assistant that's working blind. Once the tool has your workbook, with real headers, real value types and real row counts, most of that detail stops being necessary. "Total revenue by region for Q3" is enough, because nothing about your structure is a guess anymore.
The error modes change too, which is the part worth knowing. A blind assistant makes plausible mistakes about your structure. One that can read the sheet makes far fewer of them, and it can tell you when your request doesn't match the data at all, which is often the most useful answer available.
Generate a formula against your real columns
SheetAI reads your workbook, writes the formula into the cell, and explains each argument, so you can check it rather than trust it.
Write a formula in SheetAI
