The Voice of Business

AI for Excel and Spreadsheets

What AI can and cannot do in Excel: formulas, data cleaning and analysis, where it quietly gets numbers wrong, and a simple routine for checking its work.

Cover for the article AI for Excel and Spreadsheets. An open laptop on a bright shared office desk shows a spreadsheet grid with one purple highlighted column, ticks in its cells and one cell circled, with a purple sparkle at the corner of the screen, beside a coffee cup, a notepad with three ticked checkboxes and a purple pen, while two colleagues work in the blurred office behind. The headline sits on a white card with the subtitle What AI does well, where it gets numbers wrong, and how to check it, and a line reading 16 November 2026, 14 min read, Voice of Business.

AI is very good at the part of spreadsheet work that is slow and fiddly, and unreliable at the part that has to be right. It will write a formula, tidy a messy column or explain a sheet someone else built in seconds. It will also, without any warning, hand you a total that is wrong and sounds certain.

This article covers what AI can do in Excel and other spreadsheets, where it goes wrong, how to ask for formulas that work, what is safe to upload, and a short routine for checking the result before a number leaves your desk.

Key takeaways

  • AI is strongest at drafting formulas, explaining them, cleaning text and structuring a sheet. It is weakest at being trusted with a final total.
  • Spreadsheets were error-prone before AI. Add a tool that writes confident formulas quickly and you need more checking, not less.
  • An assistant that runs code or a formula on your real cells calculates. One that reads the sheet as text and answers from memory is guessing.
  • Ask for a formula plus a plain-English explanation, and test it on three rows you can work out by hand.
  • Work on a copy, remove personal data you do not need, and keep a person responsible for every number that is used.
  • Start with low-stakes tasks such as cleaning and formatting, and leave financial decisions to a person who owns the numbers.

What can AI do in a Spreadsheet?

AI can do five jobs well enough to be worth using, and they are the ones that eat a person’s afternoon rather than their judgement.

Write formulas. Describe what you want in words, such as “add the sales in column D where the region in column B is North and the month is October”, and get back a formula you can paste in. This is the most common use and the one most people notice first.

Explain formulas. Paste in a long formula that a former colleague built and ask what it does, step by step. For inherited workbooks this is often more valuable than writing new formulas, because it turns a black box into something you can read.

Clean messy data. Split “Smith, Jane” into two columns, standardise dates typed six different ways, remove duplicate rows, fix inconsistent capitalisation in a list of company names. These are repetitive jobs where a pattern is clear and a wrong result is easy to see.

Structure and format. Suggest a layout for a budget or tracker, add headers, build a pivot table, propose a chart. The output is a starting point you adjust.

Summarise and draft. Describe what a table seems to show, draft the commentary for a monthly report, or list questions the numbers raise. The words are the AI’s strength. The figures inside them still need checking.

What links all five is that a person can look at the result and see quickly whether it is right. That is the test for a good first spreadsheet task, and it is the same one used to choose which AI tasks to start with.

Want this for your team?

Pick a time to talk it through with one of our trainers.

Is it Safe to let AI do the maths?

Not without checking, and the reason is how the tool works. A chat model produces text by predicting what should come next. When it answers a sum straight from its own head, it is producing a plausible-looking number, not performing a calculation. That is a form of hallucination, and it is worse in a spreadsheet because the wrong figure arrives looking exactly like a right one.

Some assistants avoid this by writing a formula or running code on your actual cells and returning the result of that calculation. That is far more dependable, because the arithmetic is done by the spreadsheet engine or the code, not guessed. The practical difference is large, so find out which your tool is doing. If it will not or cannot say, treat every total as unchecked.

Even a correct method can hit the wrong cells. A formula that works in general can point at column C when your figures are in column D, include a subtotal row in its own sum, or skip rows with blanks. Nothing flags these mistakes. The workbook simply shows a number.

Microsoft says this directly about its own tool. Its guidance for Copilot in Excel states that Copilot “can sometimes make mistakes, misinterpret information, or produce inaccurate results”, tells users to “review, edit, and verify anything Copilot creates before you rely on it”, and advises avoiding it “for decisions in sensitive areas such as finance, legal, or medical topics”. That is the vendor describing the limit of its own product, and it applies to every tool in this category.

Why do Spreadsheets need extra care even before AI?

Because ordinary spreadsheets already contain mistakes at a surprising rate, and an AI that works quickly can multiply them.

Raymond Panko, a researcher who spent years reviewing the evidence, found in his review of spreadsheet errors that seven field audits of real organisational spreadsheets found errors in 24% of the 367 spreadsheets examined. In laboratory studies of people building spreadsheets, he reports an average cell error rate of 3.9%. His conclusion is that spreadsheet error rates are comparable to those in other human mental activity, and large enough that most large spreadsheets will contain errors.

A second example shows how quiet the problem can be. A 2016 study in Genome Biology scanned supplementary Excel gene lists published alongside scientific papers and found that about one-fifth of the papers with such lists, 19.6% in total, contained gene names that Excel had silently converted into dates or numbers. Nobody typed those errors. The software did them by default, and they survived peer review.

So the starting point is not that Excel is accurate and AI is risky. It is that spreadsheets are fragile and AI adds speed and confidence. A fluent, authoritative-sounding explanation next to a wrong number makes a mistake harder to spot, not easier. That is why the routine later in this article matters more than the choice of tool.

How do you ask AI for a formula that works?

Write the request the way you would brief a new colleague who has never seen the sheet. Vague requests produce formulas that fit an imaginary spreadsheet instead of yours, which is the same principle as when you brief it like a new hire.

Give it five things.

  1. The layout. Say which columns hold what, which row the headers are on, and where the data starts and ends. Pasting the header row and two sample rows is often enough.
  2. The goal in plain words. “Total October sales for the North region, excluding refunds” is better than “sum this”.
  3. Edge cases. Mention blanks, duplicates, text in number columns, and rows you want ignored.
  4. The tool and version. A formula for one spreadsheet application may not exist in another, and older versions lack newer functions. Say which you use.
  5. A request to explain. Ask for the formula and a line-by-line explanation. If the explanation does not match what you meant, the formula is wrong or your request was unclear.

Then test it. Pick three rows where you can work out the answer by hand, including one awkward row such as a blank or a refund, and compare. If all three match, it is probably sound. If one fails, tell the AI exactly which row and what you expected, and it will usually fix it. The test takes two minutes and removes most silent errors.

How do you clean messy Data with AI?

Cleaning is the best first use because mistakes show up quickly and the original is still there to compare against.

Start by giving the assistant a few examples of what comes in and what should come out. “Jane Smith, jsmith@example.com” becomes two columns and a lower-case email. Examples are more reliable than a description, because the tool can see the pattern you mean.

Ask for the method, not just the result. A formula or a short set of steps you can run again is better than a column of cleaned values pasted over the top, because you can inspect the method and reuse it next month. If you have to paste the cleaned values, put them in a new column beside the old one.

Watch for three things afterwards. Count rows before and after, because a clean that silently drops rows is the most common fault. Scan for values that changed meaning, such as a product code beginning with zeros turning into a number, or an ambiguous date read as day-month in one place and month-day in another. And spot-check a random handful against the original.

If the data is large, remember the limits of the tool. An assistant that reads the file as text can only use what fits in its context window, so a long sheet may be cut off or sampled without you being told. Ask it how many rows it actually used.

Where does AI get Spreadsheets wrong?

Six patterns cover most of the failures, and knowing them tells you where to look.

Confident totals from memory. The model gives a sum it has not calculated. The cure is to insist on a formula or code you can see, and the wider ways to reduce AI hallucinations apply here too.

Right formula, wrong range. The logic is sound and the cells are not. This is the most common, and the hardest to see by eye.

Silent changes. An assistant that edits your workbook directly may alter more than you asked, or overwrite a cell that other formulas depend on. Work on a copy and compare.

Invented context. Asked what a trend “means”, the tool will supply a business reason it cannot know. The numbers may be right and the story made up, so keep the explanation separate from the evidence.

Inconsistent answers. Ask the same question twice and the wording, and occasionally the figure, can differ, for the reasons covered in why AI gives different answers to the same question. A calculation done by a formula gives the same result every time. A figure quoted from a chat does not.

Missing the unusual row. One-off oddities, such as a negative quantity, a duplicate invoice or a total pasted into the middle of a column, are exactly what a pattern-matching tool smooths over and an attentive person notices.

Which AI Tool should you use for Excel?

There are two broad types, and each has a place.

An assistant built into the spreadsheet works inside your workbook, can see it directly and can edit it in place. That convenience comes with the silent-change risk above, and with the fact that anything it saves to a shared file is visible to everyone with access. Microsoft’s guidance for its own assistant says exactly that about saved changes. If you are weighing the Microsoft options, Copilot Chat and Microsoft 365 Copilot are not the same product, and the difference affects what you can do with a workbook.

A general chat assistant is flexible, strong at explaining and drafting, and often better at awkward one-off questions. The cost is moving data in and out, which raises the data-protection question below. ChatGPT and Claude compared is a reasonable starting point for that choice, and ChatGPT and Copilot compared covers the other common pairing.

Do not choose from a feature list. Pick one real task where you already know the right answer, run it in two tools, and compare the all-in time, including checking and fixing, not only how fast the answer appeared. The 30-day AI pilot is a structured way to do exactly this, and the habit of asking whether the tool is actually saving time keeps the comparison honest.

Is it Safe to upload company Data to an AI Tool?

It can be, but spreadsheets are the place people most often paste more than they meant to. A sheet you think of as a sales report may carry customer names, email addresses, notes typed by staff and hidden columns you forgot existed.

Before you upload anything, do four things.

  • Remove what you do not need. Delete personal columns, hidden sheets and comments. The AI usually does not need names to total a column.
  • Check the plan. Read what your plan does with uploaded files, and whether they are used to improve the model. Business and personal plans can differ.
  • Use a copy. Anonymise or sample a copy, and keep the master where it is.
  • Follow your rules. If you have an AI use policy, it should say what may be uploaded. If you do not, writing one is a better first step than another tool. Without clear rules people use their own accounts, which is how shadow AI starts.

The wider principles are covered in what is safe to paste, and your obligations under data protection law do not pause because the file is “only a spreadsheet”.

How do you check AI work in a Spreadsheet?

Use the same short routine every time, and keep a person responsible. This is what keeping a human in the loop looks like in practice for spreadsheet work, and it is a narrower version of the habit in how to check the output before it reaches a customer.

  1. Save a copy first. Never let the assistant touch the only version.
  2. Ask for the method. A visible formula, or steps you can rerun, not a pasted answer.
  3. Test on three rows by hand. Include an awkward one.
  4. Reconcile the totals. Does the new total equal the old total, or the figure from another source? Do row counts match before and after?
  5. Look for the odd rows. Sort by the largest, smallest and blank values and read the extremes.
  6. Name the owner. One person signs off every number that leaves the team, and writes down what was checked.

Here is how it looks on a small, made-up example. Suppose a shop owner asks an assistant for the total of October sales in column D for the North region, and gets back a figure of 18,450. Before using it, they save a copy, ask for the formula and read its explanation, which says it adds column D where column B is “North” and the month is October. They pick three rows they can work out by hand, one of them a refund shown as a negative number, and the formula gets two right and the refund wrong, because it counted the refund as a sale. They tell the assistant which row and what they expected, the formula is corrected, and all three rows now match. Then they sort the column by largest value and find a total pasted into the middle of the data, which the formula had also been adding in. Removing it changes the answer. None of those problems would have shown up in the first reply, and the whole check took about ten minutes.

The routine takes minutes on a small task. If checking regularly takes longer than doing the job by hand, the task is not yet suited to AI, and that is a useful result.

Which Spreadsheet tasks should you start with?

Start where a wrong answer is cheap and easy to see.

Good first tasks: explaining an inherited formula, cleaning a contact list, standardising dates or product names, drafting headers and a layout for a new tracker, writing the commentary for a report whose figures you have already checked.

Second stage: building pivot tables and charts, combining sheets with lookups, writing longer formulas, recurring monthly reports. Do these once the checking routine is a habit.

Leave to a person for now: anything that sets a price, a payment, a payroll figure, a tax position or a forecast the business will act on. AI can draft the structure of these, but the numbers need an owner who can explain every cell.

If your team would benefit from learning this properly, with real workbooks and a checking habit built in, the training course on Advanced AI in Excel and databases covers it. If you would rather have someone look at how your spreadsheets and AI tools fit together, an AI audit is where that starts.

Sources

FAQ

Questions we get asked

Can ChatGPT read an Excel file?

Usually yes, if your plan lets you upload files, but how well it works depends on whether the assistant only reads the text of the file or can also run code on it. One that runs code can count, filter and total the real cells. One that only reads the text is guessing at sums. Ask the tool which it is doing, and check any total against a formula in the sheet itself.

Is AI Accurate with Excel formulas?

It is good at writing formulas and unreliable at checking that they fit your sheet. A formula can be correct in general and wrong for your layout, for example pointing at the wrong column or ignoring blank rows. Microsoft's own guidance for Copilot in Excel says to review, edit and verify anything it creates before you rely on it. Test every formula on three rows you can calculate by hand.

Will AI replace Excel?

Not in the way the question suggests. AI is becoming a way of operating Excel, by describing what you want in words, but the workbook is still where the numbers live and where they can be inspected. A business that cannot read its own spreadsheet cannot check what AI did to it, so the skill that matters is reading a sheet, not typing a formula from memory.

Is it Safe to upload a Spreadsheet with customer details?

Only if you have decided where that information goes first. A spreadsheet often holds more personal data than the one column you are asking about. Remove names, emails and any column you do not need before uploading, check your plan's data terms, and follow your own AI use policy. If you are unsure, test on a copy with made-up rows.

Can AI build a financial Model?

It can draft the structure, the labels and many of the formulas, which saves the slow part. It should not be the final author of a model that drives a decision. Microsoft advises against using Copilot for decisions in sensitive areas such as finance, legal or medical topics. Have the person who owns the numbers walk through every assumption and every total.

Do I need to learn formulas if AI can write them?

You need to understand what a formula does, even if you never type one from memory. Without that you cannot tell when the answer is wrong. A short habit works: ask the AI to explain the formula in plain English, then check the explanation against what you wanted. If the two do not match, the formula is wrong or your request was unclear.

What is the Best AI for Excel?

It depends on where you work. An assistant built into Excel sees your workbook directly and can edit it in place. A general chat assistant is flexible and good at explaining and drafting, but you move data in and out. Choose by testing both on one real task with a known answer, and compare the all-in time including checking, not only how quickly the answer appears.

How do I stop AI changing my original Data?

Work on a copy. Save the file under a new name before any AI touches it, keep the original untouched, and ask the assistant to add new columns or a new sheet instead of overwriting existing cells. Where edits are saved to a shared file, Microsoft notes that anyone with access can see the changes, so a copy also protects your colleagues.

Ready to Put This to Work?

Tell us where your team is with AI and we will tell you honestly what would make the biggest difference.

More from the Blog

All blog posts