How to check if ChatGPT or Copilot got your Excel numbers right

Why does an AI's Excel total look right even when it is wrong?

Because a wrong total looks exactly as clean as a right one. There is no red triangle and no error message, just a normal number with a comma in it. And the AI explains a wrong answer in the same calm, clear way it explains a right one, which Microsoft says openly in its Copilot in Excel FAQ: Copilot explains its insights and formulas in fluent, grammatically correct language, but the content can be inaccurate, so review and verify anything it creates before you rely on it.

It helps to know how these tools actually read your spreadsheet. OpenAI’s help page on data analysis says ChatGPT writes and runs Python code in a Jupyter notebook, which is a scratchpad where code runs one step at a time, and that it shows your data as pandas DataFrames. pandas is the standard Python library for tables, and a DataFrame is just a table held inside that program.

So the tool never sees your sheet the way you see it on your monitor, and that one difference explains most of these mistakes. On your screen a filter hides the rows you do not want, and a Total row is clearly a total because it sits at the bottom in bold. But to a program reading the file, a hidden row is just another row, and the Total row is one more number in the Amount column.

Here is a small example. Take a sheet with six sales, 1,200, 850, 640, 910, 430 and 770, which add up to 4,800, plus a Total row underneath that also says 4,800. You turn on a filter to see only North and East, so on screen you have three rows adding up to 2,270.

Now load that same file with pandas and add up the Amount column, and you get 9,600. It read all seven rows, the three hidden ones and your Total row too, and it did the addition without a single mistake. So one spreadsheet gives you three answers, 2,270 on your monitor, 9,600 from the AI and 4,800, which is the real total, and none of them came from bad maths.

What should you work out yourself before you ask the AI?

One control total. A control total is a number you calculate yourself, by a second route, that the AI’s answer has to match. It is an old accounting habit, and it works because two different routes to the same number rarely go wrong in exactly the same way.

Find the column your question depends on, usually an amount or a quantity, and type these four formulas into empty cells beside your data. The ranges below fit the six row example, so stretch them down to your own last row.

1. 01
   The plain total: =SUM(B2:B7)

   Select only the data rows and leave the Total row out. This is the number you compare with the AI’s answer.
2. 02
   The visible total: =SUBTOTAL(109,B2:B7)

   Microsoft’s SUBTOTAL page documents that SUBTOTAL always ignores rows a filter has hidden, and that function number 109 also ignores rows you hid by hand. If this number is different from the SUM, some of your rows are hidden, and you need to decide whether your question includes them, because a program reading the file will count them.
3. 03
   The cells that are not really numbers: =COUNTA(B2:B7)-COUNT(B2:B7)

   COUNTA counts every cell that is not empty and COUNT counts only real numbers, so the difference is how many cells in the column are text. Anything above zero matters, because Microsoft’s own SUM page says SUM ignores text values and gives you the sum of just the numeric values, so your own total is short as well.
4. 04
   The row count: =ROWS(B2:B7)

   This is how many data rows you have. Later you ask the AI how many rows it read, and the two should match.

Write down the SUM and the count of text cells before you ask the AI anything. If the text count is not zero, fix those cells first, because your own number is wrong too.

What does the gap between your total and the AI's total mean?

Now compare the two. If the AI matches your SUM, good, move on to the three rows. If it does not, do not start arguing with the AI or asking it to try again. Look at how far apart the numbers are first, because each common mistake leaves its own clue.

Numbers stored as text are the tricky one, because they can pull the two totals apart in either direction, or let them agree while both are wrong. Take a column holding 1,200, then 850, 1,250 and $80 all typed in as text, then N/A, then 640. Excel’s SUM skips every text cell and gives you 1,840.

A pandas script that converts the column with errors='coerce', a common setting that turns anything it cannot read into a blank, gives 2,690. The 850 converted cleanly, but 1,250 with its comma and $80 with its dollar sign did not. If all of those were meant as sales the real total is 4,020, and neither of them got there.

Spelling causes the same kind of trouble, and it is the same reason duplicate rows survive when you try to clean a table. North and North with a space at the end are two different values to software, which I went through properly in removing duplicates in Excel without losing data.

Which three rows should you check by hand?

Matching totals are a good sign, but they are not proof. Two separate mistakes can cancel each other out, and a correct grand total tells you nothing about whether the North number is right. So check three rows by hand, and pick them on purpose.

The first is an easy one. Pick a small group you can add up in your head or with a quick filter, like one product or one week, and see if the AI’s answer for that group matches yours. The second comes from somewhere in the middle of the sheet, anywhere except the top.

The third one matters most, and it comes from the very end of your data. When code loads a spreadsheet it usually looks at a short preview first, and in pandas the preview command, head, returns the first five rows by default. The AI works out what each column means from the headers and those top rows.

But the real mess in a spreadsheet lives at the bottom. That is where you find the Total row, a note someone typed under the data, a second small table pasted underneath, or rows from a later export where the date format changed, and none of it shows up in a five row preview.

OpenAI’s own help page says a file can upload fine and still be too large, complex or poorly structured for complete analysis, and it suggests asking ChatGPT to inspect specific sheets, rows, columns or sections. So do exactly that for your third check. Pick one of the last real rows in your file and ask the AI what it has for that row.

Check one easy row, one middle row and one row from the very bottom. If the last row is wrong or missing, the AI did not read your file the way you think it did.

Should you fix the data or the formula first?

Fix the data first, every time. When a sheet gives strange numbers the natural move is to ask the AI for a better formula, and it will happily write one, but that new formula points at the same messy column. So the answer is still wrong and now it looks more professional. Half the formulas I have been asked to fix were correct all along and were reading a column that was pretending to be numeric.

So clean before anything else. Turn text numbers into real numbers, trim the stray spaces, move Total and subtotal rows out of the data, keep one header row and unmerge the cells, which is the routine in the cleaning order I use for messy Excel data. Once the data is clean, ask for the formula, and read its ranges against the last row of real data, which is one of the checks for an AI written Excel formula.

If you use Copilot directly in Excel, keep two points from Microsoft’s FAQ in mind. Copilot can only edit your sheet when calculation is set to Automatic, under Formulas then Calculation Options, and that matters for your own checks too, because in Manual mode the numbers on screen can be old. Copilot also makes real changes to your file, so if something looks wrong you can undo the changes or open a previous version of the workbook. The same caution applies to Anthropic’s rival add-in, whose own documentation says it is not for audit-critical calculations without verification, which is one of the limits of Claude in Excel.

What should you ask ChatGPT to show you before you trust the answer?

OpenAI’s help page is clear on this: when ChatGPT uses Python for analysis, review the generated code, outputs and assumptions before relying on the result. You do not need to know Python to do that. You need four questions and a quick look for a few words in the code.

1. 01
   How many rows did you read, and what is in the last one?

   Compare the answer with your own row count and your real last row. If those two do not match, nothing else it tells you can be trusted yet.
2. 02
   Which sheet and which column did you add up, by name?

   A wrong column, like quantity instead of amount, is the usual reason a total comes back a completely different size from yours.
3. 03
   Did you include any Total, Subtotal or blank rows?

   If it says yes, ask it to leave them out and add up the column again.
4. 04
   How many values could you not turn into numbers? List them.

   This is the question that catches the 1,250 and $80 cases. A list is easy to check against your sheet, and if it says zero while your own text count was above zero, one of you is wrong.

Then open the analysis to see the code it ran, and look for these words. You do not have to read the rest of it.

One more thing to keep in mind. ChatGPT’s Python environment cannot make web requests or reach your database, which I covered in the AI tools I actually use as a data analyst, so everything it knows about your numbers comes from the file you uploaded. If the file is messy, the answer is built on the mess.

When is a five minute check not enough?

When the number goes to a client, a board or anywhere money is involved, and when the same report goes out every week. Microsoft’s FAQ says to avoid using Copilot for decisions in sensitive areas such as finance, legal or medical topics. That does not mean you stop using it, it means you put a real check between the tool and the person who reads the number.

For a one off question, a control total and three rows are enough. For a report that repeats, the check should live inside the spreadsheet, as a cell that compares the report total with the source total and turns red when they differ. That is the trace, reconcile and sample pass from the spreadsheet audit I run before a report ships, and once it is built into an automated Excel report nobody has to remember to do it.