Stop memorizing Excel formulas, ask AI instead

Describe the result, not the formula

Almost every bad AI formula I have seen started as a bad ask. People type something like sum the sales by month, get a formula back, paste it in, and then blame the model when the total is off. The model was never told enough to be right. It filled the gaps with something plausible, which is the one thing it will always do when you leave a gap.

A request that produces a formula you can trust carries four things, and it takes about the same effort as writing the vague version.

First, the shape of your data. Real sheet and column names, whether the headers sit in row one, where the data actually starts and ends, and whether it is a proper Excel Table or a plain range. Second, the rule, in one plain sentence, the way you would say it out loud to a colleague. Add up the amount column for rows where the region is North and the invoice date falls in the selected month. Third, the edge cases, which is where most of the value hides. Say what should happen when there is no match, when the cell is blank, when the amount is zero, when a key appears twice. Fourth, the answer shape. Do you want one number in one cell, or one result per row that fills down, or a spilled list. Those three are different formulas and the model cannot see your sheet to guess which you meant.

It also helps to say what you are working in. Excel on Microsoft 365 has functions that an older perpetual version simply does not, so if you are on an older build, say so and ask for a formula that works without the newer ones. Otherwise you get something elegant that your Excel refuses to recognize.

The four ways an AI formula lies quietly

The failures that cost you are never the loud ones. A formula that returns a name error, or refuses a function your version does not have, is doing you a favour. You see it, you fix it, the day continues. The expensive failures are the ones that return a perfectly reasonable number.

The first is a range that stops short. Models love to write concrete ranges, and a concrete range is a decision about how much data exists that was true on the day it was written. Your file grows, rows land under the bottom of the range, and the total quietly stops counting them. Nothing errors. The number just gets a little smaller than the truth every month, which is the problem I tested TRIMRANGE and the B2:.B5000 trim reference against.

The second is a match that is looser than you think. Lookup functions have a mode where they settle for the nearest key instead of demanding the exact one, and in some functions that is the default. So a code that is not in your reference table does not raise a hand, it borrows the value from the row above it. That is how the wrong price attaches itself to the right item.

The third is not the formula at all, it is the data underneath. Numbers and dates that arrived as text look identical on screen and get skipped by anything that adds up, so your total shrinks without a word. Half the formulas I have been asked to fix were correct all along and reading a column that was pretending to be numeric. That is a cleaning problem, and it belongs to the cleaning order, not to the formula.

The fourth is the one people install themselves. Ask for a formula that handles errors gracefully and you will often get the whole thing wrapped so that any failure returns a blank or a zero. That is not error handling, that is a blindfold. Now a missing lookup and a genuine zero look the same, and the sheet has lost the ability to tell you something went wrong.

The two minutes that decide whether it ships

Speed is only worth having if the answer is right, so the verify pass is not optional overhead, it is the part that turns a fast draft into work you can put your name on. Mine is four checks and it takes about two minutes.

Start with one row done by hand. Pick a row in the middle of the data, work the answer out yourself with a calculator or a quick manual sum, and compare. If your hand answer and the formula disagree, one of you is wrong and it is worth knowing which before another five hundred rows inherit the mistake, and the same idea works when the AI hands you a finished total instead of a formula, which is where checking whether ChatGPT or Copilot got your Excel numbers right starts.

Then walk the edges, because the middle of a dataset is where everything behaves. Check the very first row and the very last one. Check a blank cell, a zero, and a key that genuinely has no match anywhere. Those four cases catch most of what a demo would never surface, and they take seconds each.

Third, read the ranges out loud against your data. Click into the formula, look at where each range ends, and compare that to the last row of real data. This is the single check that catches the most silent errors in my experience, and I run it on every headline number before a file leaves my hands, which is the same discipline I described in the spreadsheet audit.

Fourth, break it on purpose. Type a code that does not exist, or point it at an empty month, and watch what happens. You want an error or a clearly wrong looking blank, something that would make a person stop. If it answers with a calm zero instead, the formula has no way of telling you when it fails, and you have just learned that before it mattered rather than after.

One more thing, and it costs nothing. Ask the model to explain its own formula back to you in plain words, argument by argument. Either the explanation matches what you asked for, or the gap between them is exactly the bug. It is also the fastest way to actually learn the functions, because you end up reading many more formulas than you write, which is how the knowledge sticks.

When the right answer is not a formula at all

AI will happily give you a formula for anything, including problems that a formula should not be solving. Knowing when to refuse the offer is most of what separates a spreadsheet that lasts from one that becomes unmaintainable in a quarter.

If the formula you get back is a nested monster nobody can read, split it into two or three helper columns instead. Each column does one step, each step is checkable on its own, and the person who inherits the file can follow it. A single clever cell that only the model understands is a liability dressed as elegance. While you are there, put the data in a proper Excel Table and refer to columns by name, so the formula reads like a sentence and grows on its own when rows are added. That one change removes the range that stops short permanently.

If the same cleaning happens every week before the formulas can even run, formulas are the wrong layer. That is repeatable work, and repeatable work belongs in Power Query or a script that runs the same way every time, which is the whole argument in the piece on report automation. If the numbers are heading for a dashboard people will click and filter, a formula in a cell is the wrong shape entirely, and you want a measure that recomputes under the filter, which is where DAX quietly breaks dashboards. And if you find yourself asking for the same formula for the third time on the third file, the pattern is proven and it is worth encoding once, which is the moment picking up a little Python pays for itself.

And if the file lives in Google Sheets rather than Excel, there is one more layer to weigh, because a prompt typed straight into a cell there does not behave like the formula it resembles. The output is generated once and then sits still while your data moves, which is why choosing between the four AI layers in Google Sheets matters before you build a column on any of them.

Excel had its own version of that idea and it is being withdrawn, which settles the argument fairly neatly. The =COPILOT() worksheet function stops being available on 14 September 2026, so anyone who built a column on it now has an audit to run, and what happens to a workbook that already uses =COPILOT() covers what to do before the date. It changes nothing about the method here. Asking a model to write a formula you then check is still the durable version, because what you keep is an ordinary formula.

I would genuinely rather you learn this than pay anyone for it, because the person who can describe a rule precisely and prove the answer is right gets handed better problems and better pay. But there is a point where the file is large, the numbers feed decisions someone else makes, and nobody in the room can say with confidence where a total comes from. That is the work I take on in my evenings around the day job. If a workbook of yours is producing a number you cannot fully trace, start a project and send me the file, and if the honest answer turns out to be one helper column and no project at all, I will tell you that plainly.