Spreadsheets and AI: let the sheet do the arithmetic

Most small businesses actually live in a spreadsheet. The invoice tracker, the hours, the stock count, the list of clients with their renewal dates. So when a chat window offers to analyse the file, or a cell offers to fill itself, the offer lands somewhere that matters. Two different things are on offer, and from the outside they look the same. One is among the most reliable uses of AI available to you. The other produces numbers that are wrong in ways nothing flags, in a document that people trust precisely because it is a spreadsheet.

The dividing line is not the tool or the brand. It is whether the arithmetic happens inside the model or inside the sheet. Once you can see that line, most of the guidance follows on its own, and you can stop guessing which features are safe with each release. This guide is for people who run a business out of Excel or Google Sheets. If you are building data pipelines, writing production analysis code, or working to an audit standard, the failure modes below are the same but your controls need to be stricter than anything described here.

The safe mode is the model writing instructions your sheet executes

Ask a model for a formula and you are using it for the thing it is built to do, which is produce text. Describe the job in words, get a working SUMIFS or XLOOKUP back with an explanation attached, paste it in, and the sheet does the arithmetic. The numbers come from your actual data through deterministic rules that you can read.

The same shape applies to conditional formatting, pivot table setups, cleaning steps, and untangling an inherited formula that nobody in the business understands any more. Microsoft describes Copilot in Excel as updating your workbook using Excel’s built-in features, so that your content stays editable and you keep control of everything modified [3]. Its listed capabilities are the ordinary furniture of a spreadsheet: editing worksheets, generating formulas, creating charts and PivotTables, applying formatting, sorting and filtering [2]. Anthropic says every edit in Claude for Excel is reviewable, that changes show as highlighted cells, and that nothing gets saved until you say so [6]. For some data-analysis tasks ChatGPT goes one step further and writes and runs Python code in a stateful Jupyter notebook environment rather than predicting the answer text [5].

What these have in common is an artifact you can inspect. A formula, a pivot definition, a block of code. That artifact is the audit trail, and it is the reason this mode is trustworthy: you are not asked to believe the number, you are asked to read the rule that produced it.

The AI cell is a text generator wearing a formula’s clothes

Then there are the functions. Excel has =COPILOT() [1] and Google Sheets has =AI(), which also answers to =Gemini() [4]. They sit in the formula bar next to SUM. They recalculate when your data changes [1]. They look like functions, and that resemblance is doing an enormous amount of unearned work.

Microsoft’s own documentation is unusually direct about it. The COPILOT function is designed for semantic, generative and exploratory tasks, and is best suited for scenarios where deterministic accuracy is not required [1]. The page then lists what to avoid using it for. Numerical calculations are on that list, with the instruction to use native Excel formulas such as SUM, AVERAGE and IF for any task requiring accuracy or reproducibility [1]. So are lookups, which is what XLOOKUP is for, and tasks with legal, regulatory or compliance implications, where the guidance is to avoid AI-generated outputs for financial reporting, legal documents or other high-stakes scenarios [1].

Google’s documentation makes the same point by omission. The AI function generates text, summarises information, categorises information, analyses sentiment, and accesses real-time information from Google Search [4]. Arithmetic is not on the list, and the page states plainly that responses are limited to text [4]. Neither vendor is hiding any of this. The warning is not buried in a terms page. It is in the function’s own reference documentation, under the heading for what to avoid [1].

Non-determinism is the property that actually breaks a spreadsheet

A spreadsheet makes one promise above all others: the same inputs produce the same output on every recalculation, this year and in three years. Auditors rely on it. So does anyone reopening last March’s file to work out what happened.

AI cells break that promise by design. Microsoft states that the COPILOT function currently uses gpt-4.1-mini (2025-04-14), and that the model will evolve and improve in the future so that formula results may change over time, even with the same arguments [1]. The suggested remedy is the blunt one: if you do not want results to recalculate, convert them into values with copy and paste values [1]. Google’s version of the same problem is that you cannot undo or redo an AI function, and can only regenerate the output instead [4].

Take the remedy seriously. Anything a person will act on should be frozen as a value the moment it is right, with a note saying when it was generated. Otherwise you have a workbook whose totals can change on a Tuesday, for reasons nobody recorded, because a model was updated somewhere in Azure.

The features themselves are also young enough to move. Microsoft describes the COPILOT function as currently available in the Frontier program and the Microsoft 365 Insider program, and states that starting 14 September 2026 it will no longer be available, pointing users instead to Copilot in Excel, which it says is generally available and supports many of the same AI-powered tasks [1]. A function can be retired while it is still in preview. That is not an argument against using in-grid AI. It is an argument against building a recurring report on top of one.

Ask to see the work, and you will know which mode you are in

The practical test takes a second. If the tool hands you a formula, a pivot definition, or code you can read, you are in the first mode and the result inherits the spreadsheet’s reliability. If it hands you a number and a sentence, you are in the second mode and the result inherits the chat window’s.

Vendors say this in their own instructions. OpenAI’s data analysis page tells you to review the generated code, outputs and assumptions before relying on the result [5]. Claude for Excel makes every edit reviewable and shows changes as highlighted cells [6]. Microsoft’s Copilot in Excel FAQ addresses whether its insights and formulas can be trusted, and answers that Copilot explains them in fluent, grammatically correct language but the content can be inaccurate or inappropriate, so you should review, edit and verify anything it creates before you rely on it [2].

Two details worth knowing before you start. When you save Copilot changes to your workbook, anyone with access to the file can see those changes, including people in a coauthoring session, and your way back is to undo the changes or view prior versions [2]. And OpenAI notes that ChatGPT may not reliably extract exact values from image-based tables, scanned files, or files with complex visual layouts [5], which makes a screenshot of a table the single worst way to hand over data. Upload the file.

Run it once on a sheet whose answers you already know

Before you use any of this on numbers that matter, spend ten minutes on a closed period. Take last quarter, where the totals are final and you know them, ask the questions you would actually ask, and compare. This tells you more about your specific tool on your specific data than any review will, and it calibrates your trust against reality rather than against a demo.

Do it again after anything changes. Copilot in Excel supports Claude models from Anthropic and GPT models from OpenAI for editing your workbooks, lets you switch models or choose Auto to let Copilot pick one for you, and reverts to the default model when you close Excel [2]. So the thing that categorised your support tickets last week may not be the thing categorising them today.

The reason fluency is a trap here is that it is uniform. A confident, well-punctuated explanation reads identically whether the figure underneath it is right or off by a row. Microsoft’s limitations tell you to avoid using Copilot for decisions in sensitive areas such as finance, legal or medical topics [2], and Google’s help page tells users not to rely on Gemini features as medical, legal, financial or other professional advice [4]. Both are describing the same gap between how right an answer sounds and how right it is.

The real constraints are licences, rate limits, and confidentiality labels

In-grid AI is a paid feature everywhere. The COPILOT function required a Microsoft Copilot add-on licence for work or school accounts, and was limited to Microsoft 365 Premium subscribers for individuals [1]. Copilot in Excel requires one of a Microsoft 365 Personal or Family subscription with an AI credits plan, a Microsoft 365 Premium subscription, a commercial Copilot subscription, or a Copilot Chat-eligible Microsoft 365 or Office 365 business or enterprise subscription [2]. Microsoft lists Microsoft 365 Copilot Business at $18.00 per user per month paid yearly, a promotional price against a list price of $21.00, or $25.20 per user per month billed monthly [7]. Google’s AI function requires an eligible Google Workspace or Google AI plan [4]. Claude for Excel is generally available on all paid Claude plans [6].

Rate limits shape how you use them. Microsoft allows up to 100 COPILOT calculations every 10 minutes, and going past that returns a Short-Term Limit Exceeded error [1]. The function only has access to the data you hand it through its context arguments [1], which means a single formula pointed at a whole column is one calculation, while the same formula copied down 400 rows is 400 of them. Google generates only the first 350 selected cells with AI functions in one go, and applies short-term and long-term generation limits, where reaching a long-term limit means waiting 24 hours before trying again [4]. Neither is a reason to avoid the feature. Both are a reason to design your sheet around one call over a range rather than a thousand cells each calling out on its own.

Then the data question, which for most small businesses is the one that actually decides things. Microsoft states that your prompts and data supplied as context to the COPILOT function will not be used to train AI models [1], and that the function cannot calculate at all in workbooks labelled Confidential or Highly Confidential [1]. Google warns that feedback you submit about an AI function may be human readable, so you should not submit data containing personal, confidential or sensitive information [4]. A sheet of customer names and balances deserves a decision made on purpose rather than by habit.

What still goes wrong

The confident wrong answer is still the main hazard, and none of this removes it. A row can be skipped, a duplicate counted twice, a currency column read as text, and the summary that comes back will be as fluent as the correct one. Microsoft’s own answer on trusting Copilot’s output is that it explains insights and formulas in fluent, grammatically correct language and that the content can still be inaccurate or inappropriate [2]. There is no signal in the writing. There is only your own sense of the business, which is why the owner who thinks “that margin cannot be right, March was terrible” catches what no tool catches.

The ground moves. The COPILOT function stops being available on 14 September 2026 while it is still in the Frontier and Microsoft 365 Insider programs [1], the underlying model can change so that the same formula returns something different [1], and the model behind Copilot in Excel can be switched by you or picked automatically and resets when you close the app [2]. Anything you built as a repeatable process on top of these features needs re-testing more often than you would expect from ordinary spreadsheet functions.

There are also plainer limits worth knowing before you are surprised by them. Microsoft notes Copilot was trained on sources predominantly in English and may not perform as well when prompts or data are in other languages [2]. Google’s AI function does not have access to your entire spreadsheet or other files in your Google Drive, so context has to be handed to it explicitly [4], and the same is true of the COPILOT function, which has no access to other data from the workbook beyond the context arguments you pass it [1]. Neither tool knows about the tab next door unless you point at it. That is a feature, and it is also a very common source of an answer that is confidently about the wrong data.

Prompts from this guide

formula-not-answer

I need a spreadsheet formula, not an answer.

Tool: {excel_or_google_sheets}
Sheet layout: {columns_and_what_they_contain}
What I want to compute: {the_calculation_in_plain_words}
Edge cases that exist in my data: {blanks_text_in_number_columns_dates_etc}

Give me:
1. The formula, ready to paste, using the column letters above
2. One sentence saying what it does
3. What it returns when a value is missing or the wrong type

Do not compute the answer yourself and do not show example totals.

explain-this-formula

Explain the formula below, which I inherited and do not understand.

Break it into its nested parts, innermost first. For each part say what it
returns and why it is there. Then give me one sentence describing the whole
formula in business terms.

Finally, list anything in it that would break or silently return the wrong
result if the sheet changed: inserted columns, blank cells, text where a
number is expected, a range that does not extend to new rows.

Formula:
{formula}

What the sheet contains:
{sheet_description}