AI for Spreadsheets: Let AI Write the Formula, Not the Answer
There are two ways to ask AI for help with a spreadsheet, and they look almost identical.
“Here’s my sales data. What was the average deal size in Q2?”
“Here’s my sales data. Write me the formula that calculates average deal size in Q2.”
The first gets you a number. The second gets you something you can check. That difference is the whole skill, and it’s worth more than any prompt you’ll copy off the internet.
A number from a chat window is a guess that looks like arithmetic
Here’s the part most people never get told. When a language model is just generating text, it isn’t calculating — it’s predicting. It produces the next most likely The chunks a model reads and writes in — roughly word-fragments. Numbers get split up too, so “1,284,733” isn’t handled as one quantity but as a handful of pieces. That’s why models can be fluent about a figure and still get it subtly wrong: nothing in that process is doing arithmetic, it’s producing the digits that look most plausible next. given everything before it. For prose, that works remarkably well. For a five-digit multiplication, it means the answer is a very well-informed guess.
And guesses are usually close. That’s precisely the problem. A wildly wrong total gets spotted; a total that’s off by 3% goes in the deck.
A formula is different. =AVERAGEIFS(D:D,A:A,">="&DATE(2026,4,1)) isn’t a
guess about the answer — it’s an instruction, and your spreadsheet does the
actual maths. You can read it, test it, and it recalculates when the data
changes next month. The model is doing the part it’s genuinely good at:
translating what you meant into precise syntax.
Step 1: Describe the columns, not the spreadsheet
Don’t paste the whole sheet and hope. Tell it the shape:
“Column A is the close date. Column D is deal value in dollars. Column F is the rep’s name. About 4,000 rows, one row per closed deal. Some rows have a blank in D where the deal value wasn’t recorded.”
That last sentence is the one people leave out, and it’s the one that
changes the formula. Blanks, text stored as numbers, inconsistent date
formats — these decide whether you need AVERAGEIFS or something that
handles the gaps. A model that doesn’t know about your blanks will write a
formula that silently treats them as zero.
Step 2: Ask for the formula, and ask it to explain itself
“Write an Excel formula for the average of column D where column A falls in Q2 2026, ignoring blank values in D. Then explain what each part does, and tell me what it would do wrong if my dates were stored as text.”
Three things in that prompt earn their place. The formula is the deliverable. The explanation is how you check it without being an Excel expert. And asking what would break it is the highest-value habit in this whole post — it turns the model into something that argues with your data rather than flattering it.
Then paste the formula into one cell and compare it against a small slice you can verify by hand. Ten rows you’ve eyeballed is enough to catch a wrong range or an off-by-one date boundary.
Step 3: Read it cell by cell, because that’s the only thing proven to work
Spreadsheets were the most error-prone thing in the office long before AI arrived. Raymond Panko, who spent years auditing them, summarised fifteen years of research bluntly: studies “have concluded unanimously that spreadsheet errors are both common and non-trivial.”
His finding on fixing them is the useful bit for us. Of all the techniques people try, only one has been demonstrated to be effective: cell-by-cell code inspection. Reading the formulas. Not testing the outputs, not eyeballing the totals — reading them.
Which is a strange piece of luck, because that’s exactly what an AI-generated formula lets you do and an AI-generated number does not. If you ask for the answer, there’s nothing to inspect. If you ask for the formula, the one proven error-catching technique is still available to you.
So read it. If you can’t follow it, that’s not a reason to trust it — it’s a reason to ask for a simpler version. “Rewrite that using helper columns instead of one nested formula” is a completely reasonable request, and the result is usually easier to audit and easier for the next person to maintain.
When to let it compute after all
There’s a real exception, and it matters. Some tools don’t just generate text — they run A sandboxed environment where the assistant writes actual code (usually Python) and runs it on your file, then reports what the code returned. ChatGPT’s data-analysis mode, Claude’s analysis tool, and Copilot’s Python integration in Excel all work this way. The distinction that matters: a real interpreter did the arithmetic, so the number isn’t a prediction — though the code can still be wrong, and you should still read it. . When a real interpreter does the maths, you’re no longer trusting a prediction, and these modes are genuinely good at one-off cleanup, exploratory pivots, and charts.
Two rules if you use them. Know which mode you’re in — if you can’t see that code ran, assume it didn’t. And for anything recurring, still take the formula. A Python script that ran once in a chat window doesn’t recalculate when next month’s data lands; a formula in the workbook does.
The takeaway
Ask for the formula, not the answer. Describe your columns including the messy bits, make it explain the formula and name what would break it, then read it against ten rows you’ve checked yourself.
You’re not doing this because AI is bad at spreadsheets — it’s excellent at the syntax, which is the part most people find hard. You’re doing it because a formula is auditable and a number isn’t, and auditing formulas is the only spreadsheet quality technique with evidence behind it.
The version of this that goes wrong isn’t dramatic. It’s a plausible figure, in a deck, that nobody can trace back.
Sources: Raymond R. Panko, Spreadsheet Errors: What We Know. What We Think We Can Do (2008) — the “concluded unanimously” quote and the cell-by-cell inspection finding are from its abstract; Panko’s own site was unreachable, so specific per-study error percentages circulating in secondary write-ups are deliberately not quoted here. On why generated numbers are predictions rather than calculations, see Evaluating Mathematical Reasoning of Large Language Models. Tool behaviour for code-execution modes is as documented by each vendor and changes often — check what your own tool actually does. The prompts and the formula-not-answer rule are mine.
Related: How to Write a Recurring Report With AI in 10 Minutes is the same handoff for a recurring write-up rather than a calculation, and the “give it one real example” move there works just as well on a sheet. The First Job to Give an AI Agent — and the One to Keep covers the verification habit for work that runs without you watching — which is where a wrong number does the most damage.