AI Excel Formulas, and How to Check One

A model will write you an Excel formula in about two seconds. Checking whether it is right takes ten minutes, and the ten minutes is the job.

That trade is still worth making, most of the time. Nested XLOOKUPs, awkward date logic and anything involving SUMPRODUCT are genuinely faster to describe than to write. But the failure mode is specific and it is not the one people expect. A wrong formula does not throw an error. It returns a number, the number looks plausible, and it goes in the pack.

This is the AI half of creating formulas in Excel. That page covers the syntax. This one covers writing formulas with a model, auditing one you inherited, and fixing the errors you get, which is most of what AI in Excel turns out to be used for day to day.

The function that is about to disappear

Start here, because it has a date on it. Microsoft ends the =COPILOT function in Excel on 14 September 2026, announced through message center notice MC1454373. It never made general availability. It was a preview feature, it had been scheduled to ship properly in January 2027, and it is cut instead.

If you have workbooks with =COPILOT in a cell, freeze those outputs as values before the date and move the logic somewhere deterministic. The Copilot side pane does most of the same work and it does not leave a live AI call sitting inside your model.

The interesting part is Microsoft’s own guidance while the function existed. Their documentation says to avoid it for numerical calculations and to use native formulas for any task requiring accuracy or reproducibility, and that it suits work where deterministic accuracy is not required. The vendor was telling you not to put it near your numbers.

That is the rule worth keeping after the function is gone. If it sits in a cell, it has to be deterministic. Every other Excel function is a promise that the same inputs give the same output, and that promise is what makes a workbook auditable. There is more on how that reshapes the toolset in the best AI for Excel.

Writing a formula with AI

Describe your data, not the formula you want

The most common reason you get a broken formula back is that you asked for a formula. Ask for the outcome and describe what the model is working with instead.

Weak:

Write me an XLOOKUP that gets the budget number.

Better:

Sheet 'Actuals' has store name in column A, month-end date in B,
account in C, amount in D, rows 2 to 4000.
Sheet 'Budget' has the same four columns, same layout.
In Actuals column E I want the matching budget amount for the same
store, month and account. Return 0 if there is no match, not #N/A.
Excel 365. Tell me if a helper column would be simpler than one
formula, and say which you would use and why.

That last line does more than it looks. A model asked for one formula will produce one formula, however baroque it has to become. Given permission to say “use a helper column”, it often will, and the helper column is usually the right answer for anything somebody else has to maintain.

Name your Excel version too. Half the answers on the internet assume XLOOKUP and LET exist, and a chunk of finance still runs on builds where they do not.

The functions it invents

Models make up Excel functions. Not often, but confidently, and usually when the real answer is awkward. You will get something that reads like a function, sits in a plausible family, and does not exist.

It also gets argument order wrong in the functions that do exist, which is worse, because an invented function fails loudly and a wrong argument order returns a number. The classic is confusing XLOOKUP’s argument order with VLOOKUP’s, or swapping the lookup and return arrays so the formula works on the sample and breaks on the real range.

Both are cheap to catch and neither gets caught by reading the formula, which is what almost everybody does instead.

The ninety-second test

Before a generated formula goes anywhere near a workbook that matters, do this. It is quick enough that there is no excuse for skipping it and it catches nearly everything.

• Build five rows of test data by hand, in a blank sheet, and include the awkward cases. One row that should match, one that should not, one with a blank, one with a zero and one with a duplicate key.
• Work out the answers yourself. Five rows is small enough to do in your head, which is the entire point.
• Run the formula against those five rows and compare.
• Then run it on the real range and check the row count. A lookup that silently drops rows is the failure that costs you a month-end, and the row count catches it in about four seconds.
• Finally, change one input and confirm the output moves the way you expected. A formula that returns a constant no matter what you feed it is more common than it should be.

The duplicate key row is the one people leave out and it is the one that matters. Most real finance data has duplicates you did not know about, and most lookup logic quietly takes the first match. Whether that is right is a decision, and it should be yours rather than the formula’s.

Auditing a formula you inherited

This is the best thing a model does in Excel and almost nobody uses it for this.

You open a workbook somebody else built. Cell F47 holds 180 characters of nested IFs and a hard-coded 0.4 that nobody can explain. Paste it into a model and ask what it does, in plain English, one branch at a time. You get an answer in seconds that would have taken you twenty minutes with the formula bar and a pencil.

Explain this Excel formula one branch at a time, in plain English.
For each branch tell me the condition and what it returns.
Then list anything that looks like a hard-coded assumption, and
anything that will break if a range grows or a column moves.
Do not rewrite it. I want to understand it first.

That final instruction matters. Ask a model about a formula and its instinct is to hand you a better one, which is the wrong output when you are trying to work out what the last person believed. Understand it, then decide whether to replace it. Microsoft has built this directly into the side pane as understand formulas with Copilot, and any general-purpose model does it just as well on a formula you paste in.

Worth knowing what you are walking into. Spreadsheets had a formula problem long before a model wrote any of them. Reviewing the research on spreadsheet errors, Raymond Panko found that field audits using sound methodology turned up errors in at least 86% of the spreadsheets they examined, with cell error rates averaging in the low single digits. The European Spreadsheet Risks Interest Group has been collecting the expensive examples for years.

So the discipline this page is asking for is not a new tax that AI introduced. It is the discipline the work always needed and mostly did not get. What changed is the speed at which unchecked formulas can now arrive.

The four errors you get

Paste the formula, the error, and what you expected. The error text alone is not enough context and it is what most people send.

• #REF! means a reference is pointing at something that no longer exists, usually because a row or column got deleted. Ask what the formula was originally pointing at, because the fix is almost never in the cell showing the error.
• #VALUE! is a type problem. Numbers stored as text, a date that is really a string, a stray space from an ERP export. Ask which argument has the wrong type rather than asking for a fix, and you will find the export is the real problem.
• #N/A from a lookup means no match, and the useful question is why. Trailing whitespace and inconsistent codes cause most of them. Wrapping the whole thing in IFERROR hides the fault and keeps the bad number, which is how a reconciliation ends up silently short.
• Circular reference warnings are worth reading properly rather than switching on iterative calculation. Ask the model to trace the loop. Sometimes it is a genuine mistake and sometimes the model needs to iterate on purpose, and those need opposite responses.

IFERROR deserves its own warning. It is the most-used and most-abused function in finance, because it turns a visible problem into an invisible one. Use it when you know why the error happens and have decided the fallback is correct. Never use it to make a red cell go away before a meeting.

Where the model should not be

Three places, and they are all the same place really.

• Inside a cell as a live call, for the reason at the top of this page. A workbook has to give the same answer twice.
• Anywhere it has invented the inputs. A model asked to “estimate a reasonable growth rate” will give you one, and it will look like an assumption you chose.
• As the only thing that has read the formula. If you are putting a number in front of someone, you are the person who understands it.

For anything heavier than a formula, the honest answer is usually that Excel is no longer the right tool. Python in Excel handles the reshaping and aggregation work that people force into 200-character array formulas, and LAMBDA functions let you name a piece of logic once instead of repeating it across a sheet. A model is good at writing both, and both stay deterministic, which is the property that matters.

If you want the wider view of what to put where, the AI tools for finance map covers the stack, and Claude for Excel covers the one I reach for most on formula work.

Write the formula in two seconds. Spend the ten minutes. The ten minutes is the part they pay you for.