AI for Excel and Google Sheets: What Works and What Breaks

AI spreadsheet assistants are good at exactly one thing, and it is genuinely useful: writing the formula you would otherwise have spent ten minutes looking up. Describe what you want, get a working INDEX/MATCH or a nested conditional back, inspect it, use it. That is a real saving and worth having.

The ceiling arrives faster than most finance teams expect, and it arrives for a structural reason rather than a quality one.

What they are actually scoped to

An AI assistant inside Excel or Sheets reasons about the workbook in front of it. That scope is the whole story:

• It sees the cells that are open, not the system the data came from.
• It inherits every error already in the sheet. A broken lookup two tabs over is invisible to it, and it will reason confidently on top of the wrong number.
• It cannot reconcile across your ERP and CRM, because it cannot see either.
• It has no durable memory of your business rules. The same question next month can produce a different answer.

None of that is a defect. It is the scope the product was built for.

Where the ceiling actually lands

In finance the ceiling arrives the moment a question spans two sources, which is most questions worth asking.

Why did gross margin fall? needs the ledger joined to product and pricing data. Which customers are at risk? needs revenue joined to the CRM and support history. Is this variance structural? needs the same month last year and the contract that changed. None of those live in one workbook, so none of them are answerable by a tool scoped to one workbook.

What you get instead is an answer built on whatever happened to be exported into the sheet, which is a snapshot, taken at a moment nobody recorded, already stale.

The formula trap

There is a distinction worth internalising, because it decides what you can trust.

When the assistant writes a formula, the spreadsheet evaluates it. The arithmetic is real, the logic is inspectable, and you can verify it. Trust this.

When the assistant states a number in prose: "revenue grew 14% year over year" : that number was predicted as text, not computed. It is usually right and occasionally wrong, and nothing in the output tells you which. Verify this.

The two look identical in a chat panel. They are not the same thing at all.

The definition problem nobody mentions

Ask for net revenue retention and you will get a defensible number using a standard formula. Whether it is your number depends on things the assistant cannot know: that your fiscal year starts in April, that customer success sits in COGS, that two accounts are excluded from cohort analysis for a reason agreed three years ago.

It will not flag the gap. Faced with ambiguity it resolves it silently and continues. And because nothing persists between sessions, restating those rules is on you, every single time, which works until the one time somebody forgets.

Use them for this

• Writing and explaining formulas. Best-in-class, and the reason to have one.
• Reshaping data inside a sheet: pivots, splits, cleanups.
• Explaining what a function does, or why one is returning an error.
• Drafting commentary after you have verified the numbers.

Not for this

• Any number going to a board, lender, or auditor without a manual check.
• Questions spanning more than one system.
• Recurring monthly analysis where consistency of definition matters.
• Anything where the spreadsheet itself is the thing you are trying to escape.

The honest comparison

If your analysis genuinely lives in one workbook and the data in it is already correct, an AI spreadsheet assistant is the lowest-friction option available and probably already licensed. Use it.

If the question requires joining data across systems, or the answer has to be traceable to source records, that is outside what a workbook-scoped tool can do, not because it is a weak product, but because it cannot see the data.

A worked example of the ceiling

Take a real question: gross margin fell from 62% to 58% last quarter: why?

In a workbook containing only the P&L, an AI assistant can tell you that COGS grew faster than revenue. That is arithmetic on data it can see, and it will be correct.

What it cannot tell you is which of the four candidate causes it was:

• Mix shift: you sold more of a structurally lower-margin product. Needs revenue joined to the product catalogue.
• Price erosion: you discounted to close the quarter. Needs the CRM's closed-won records at line level.
• Input cost: a supplier raised prices. Needs AP data by vendor.
• Classification: someone moved a cost line between COGS and opex. Needs the ledger's change history.

Each of those four leads to a completely different decision, and the difference between them is invisible inside the workbook. The assistant will not tell you it cannot distinguish them. It will give you the best answer available from the P&L and stop there, which reads exactly like a complete answer.

What "the model wrote the formula" actually guarantees

It guarantees the arithmetic, and nothing about the logic.

A generated SUMIFS will sum precisely the cells its criteria match. Whether those criteria capture what you meant is a separate question, and it is the one that bites. Common failure modes worth checking every time:

• Range drift. A formula written against rows 2–500 silently ignores everything you paste in afterwards.
• Silent type coercion. Dates stored as text, amounts stored with currency symbols, and IDs with leading zeros all fail to match without erroring.
• Hidden and filtered rows. SUM includes them; SUBTOTAL does not. The assistant picks one without telling you which assumption it made.
• Duplicate keys. A lookup returns the first match and discards the rest. On a customer list with two rows per account, this quietly halves a number.

None of these produce an error. They produce a plausible number.

A short verification routine

Before any AI-assisted figure leaves the sheet, four checks catch most of what goes wrong, and they take about two minutes:

1. Tie to a known total. Does the sum of the parts equal a figure you already trust from the ledger?
2. Check the row count. If you expected 1,240 invoices and the formula operated on 1,198, find the 42.
3. Test one row by hand. Pick a single customer and compute their number manually. If it matches, the logic is probably right.
4. Re-run with an obviously wrong input. If the output does not move, the formula is not reading what you think it is.

Where this leaves a finance team

An AI spreadsheet assistant is a genuine productivity gain on formula authorship, and every finance team should have one. Treat it as a very fast colleague who is excellent at Excel syntax, has never seen your business, and will not tell you when a question is outside what they can see.

The work it does not touch is the work that consumes the month: locating the data, reconciling systems that disagree with each other, and shaping it into something analysable. In a typical ad-hoc analysis that assembly is roughly 70% of the elapsed time, and it happens entirely before the workbook the assistant lives in even exists.

Choosing between the options

If you need	Use	Because
A formula written or explained	Copilot or a Sheets assistant	Best-in-class, already licensed, inside the tool
Data reshaped inside one sheet	Same	The workbook is the whole scope of the task
A concept explained, an approach structured	ChatGPT or Claude	Language work, where prediction is the right mechanism
A number spanning ERP and CRM	None of the above	They cannot see the data, and will not say so
A figure that goes to a board or lender	Something that shows its working	Confidence is not verification

The last two rows are not a criticism of spreadsheet assistants. They are the boundary of what a workbook-scoped tool can be asked to do, and knowing exactly where that boundary sits is what makes the tool safe to rely on for everything inside it.

A rule of thumb worth keeping

Let the assistant write anything the spreadsheet will evaluate. Verify anything it says in a sentence. The first is arithmetic you can inspect; the second is a prediction wearing the same clothes.