You paste a quarterly sales table into a chatbot, ask for the total, and get a number that is confidently wrong. The model is not bad at math. AI misreads tables because a spreadsheet is a two-dimensional grid, and the model reads it as a one-dimensional stream of text, so the alignment between a value and its row and column quietly falls apart before any calculation begins.
The core problem: a 2D grid gets flattened into a 1D stream
When a table reaches a language model, it is serialized into a linear sequence of tokens. A spreadsheet cell that sits neatly under a header on screen becomes just another word somewhere in a long string. Once flattened, cells in the same column are separated by every other value in each intervening row, so the model has to attend across long distances to reconnect a number with the header that gives it meaning. Researchers describe this as structural collapse: flattening hierarchical headers and merged cells into a linear string strips away the row-and-column dependencies that made the table readable in the first place.
This is not a minor rough edge. A Microsoft Research study that probed whether large language models can understand structured tables found they have only basic structural comprehension, and are far from perfect even on trivial tasks like counting how many rows and columns a table has. If a model cannot reliably detect the size of a table, expecting it to eyeball a correct sum across forty rows is optimistic.
The model is not looking at a grid. It is reading a paragraph that used to be a grid, and inferring the shape back from punctuation.
Format matters more than it should
Because the model reconstructs structure from the text you give it, the exact format of that text changes the answer. The same table encoded as HTML, JSON, markdown, or comma-separated values produces different accuracy. The Microsoft study found HTML outperformed plain text with separators, likely because models saw enormous amounts of HTML tables during training. Change the wrapper, change the answer. A separate benchmark, ToRR, built specifically to test how stable models are under formatting changes, showed that models which do well on one table format often stumble when the identical data is presented in another, meaning their reasoning leans on surface formatting rather than genuine understanding of the table.
Here is what most guides get wrong: they tell you to write a better prompt. The bigger lever is the shape of the data you hand over. A model that reasons well on clean CSV can fail on the same numbers wrapped in merged cells, and no amount of please-be-careful phrasing repairs that.
Where AI misreads spreadsheets, one failure at a time
Merged cells and multi-row headers
Real spreadsheets rarely have one clean header row. They have a title merged across the top, a second row of subheaders, and cells that span two rows to group categories. When that is flattened, the link between a value and its multi-level header is broken. Research on structure-aware table reading calls this spatial-logical discontinuity: once cells are merged and offset, physical closeness in the text no longer means the values belong together, so straightforward row-and-column scanning stops working.
Scanned tables inherit OCR errors
If your table came from a photographed invoice or a scanned PDF, the model never sees the table at all: it sees whatever optical character recognition produced. A misread 8 as 3, a column that OCR split into two, or a decimal point lost in a smudge all become the model's input. The model then reasons faithfully over corrupted numbers and hands you a tidy, wrong total with no warning that the source was degraded. Worse, OCR often loses the grid geometry entirely, so a two-column layout collapses into one run-on line, and the model has to guess where one cell ends and the next begins. A faint gridline that the scanner dropped can silently merge two separate figures into a single garbled number.
Number formatting and locale collide
One string, two meanings. A value written as 1,000 means one thousand in the United States and India, but 1.000 means the same thing in Germany, while 1,000 in Germany can mean one point zero. Thousands separators, decimal commas, currency symbols, percentage signs, and Indian lakh-crore grouping (1,00,000) all have to be interpreted correctly before arithmetic. When the model guesses the locale wrong, it can be off by a factor of a thousand and still sound certain.
Large sheets get truncated
Context windows are finite, so a big sheet gets cut off, and accuracy falls as tables grow. On TQA-Bench, a multi-table question-answering benchmark tested across context lengths from 8K to 64K tokens, a mid-size open model dropped from about 50 percent accuracy at 8K tokens to roughly 33 percent at 64K, a 16-point decline driven mostly by aggregation tasks. The same benchmark found models do markedly better when several tables are merged into one, up to a 20 percent accuracy gain, which tells you that spreading data across sheets and tabs actively hurts.
The model can invent a cell
When alignment is lost and the sheet is truncated, a model will sometimes fill the gap with a plausible-looking value rather than admit it cannot see the cell. That hallucinated number then flows into a sum or an average. This is the most dangerous failure of all, because the output looks complete and gives you nothing to catch: the total that is quietly missing a row still adds up to a clean, believable figure.
Formulas do not run themselves
A spreadsheet formula like =SUM(B2:B40) is live: it recalculates from the cells it points to. Paste that sheet as text and the formula is just a string of characters. Unless a code tool actually executes it, the model reads the formula, does not run it, and has to approximate the result by mental arithmetic, which is exactly the operation it is least reliable at.
This is a different failure from the well-worn trick of asking a model how many R's are in strawberry. That one is about how words get chopped into tokens. Table errors are about geometry: the model can see every digit, but it has lost the map that says which digit belongs to which row and column. Fixing the tokenizer would not help here, because the numbers are all present. What is missing is the structure that told you which of them to add together.
The real fix: make the model run code, not read numbers
The reliable answer to tabular reasoning is not a smarter reader, it is an executor. Modern data-analysis tools do not have the model estimate a sum from text: they have it write real code that runs over the file and returns the exact result. ChatGPT's Advanced Data Analysis, formerly called Code Interpreter, lets you upload a spreadsheet, then writes and runs Python in a sandbox to parse and compute over it, and can correct its own code when it hits an error.
Anthropic took the same route. When Claude's analysis tool launched in October 2024, the pitch was explicit: it lets Claude write and run JavaScript code for precise data analysis with mathematically accurate results, processing a data file step by step until it reaches the correct answer. The tool runs the code in a locked-down worker and uses the Papa Parse library to read CSV data properly. Anthropic's own guidance told the model to reach for the tool on math that cannot be done by mental arithmetic, noting four-digit multiplication is within reach, five is borderline, and six-digit multiplication should go to the code tool.
Reading a table asks the model to be a calculator. Running code asks it to write a calculator and press equals. Only one of those is reliable.
| Dimension | Reading the table as text | Running a code or data tool |
|---|---|---|
| How the answer is produced | Model infers structure from tokens, then estimates the result | Model writes code that parses the file and computes the exact result |
| Accuracy on sums and aggregates | Degrades as tables grow and formats vary | Deterministic, same as running the code yourself |
| Handles formulas | No, formulas are read as inert text | Recomputes from the underlying values |
| Large sheets | Truncated when they exceed the context window | Reads the whole file from disk, memory permitting |
| Best used for | Quick reading, explaining a column, spotting a trend | Any real calculation, filtering, joins, or reporting |
The tradeoff is not free. Code tools carry file-size ceilings, and in ChatGPT a spreadsheet upload is capped at roughly 50MB depending on row size, so a truly massive workbook still needs to be split or sampled. But for the everyday case of getting a correct total, average, or filtered count, executing beats reading every time.
How to structure a table so the model reads it right
You can prevent most of these failures at the source by shaping the data before it reaches the model. The goal is to make the grid survive flattening.
- Export to clean CSV rather than pasting a styled spreadsheet, so there is no formatting for the model to misread.
- Use a single header row. Un-merge cells and flatten multi-row headers into one clear label per column, such as combining a Region group and a Q1 subheader into Region_Q1.
- Send one table per prompt. Splitting data across tabs and sheets measurably lowers accuracy, so merge related tables into one before asking.
- State units and locale explicitly: say the amounts are in USD, that commas are thousands separators, and that percentages are already expressed as whole numbers.
- For any real calculation, ask the model to use its code or data-analysis tool instead of answering from the text, and confirm it actually ran code.
Give every column a unique, unambiguous header. Two columns both labelled Total force the model to guess which one you mean, and it often guesses wrong.
When your numbers live across dozens of files
Cleaning one sheet is doable. The harder problem is when the figure you need is scattered across many bank statements, expense reports, invoices, and screenshots, each with its own layout, and you cannot remember which file holds it. Re-reading every sheet to find one number is exactly the manual work that tabular tools do not solve. This is the gap MemX is built for: you save your statements, reports, and screenshots, then ask in plain language, and it searches across all of them by meaning to surface the figure and the file it came from, so you are not reopening ten spreadsheets to answer one question. MemX is private by architecture, with per-user isolation and encryption at rest, so those financial documents stay yours.
01Why does ChatGPT get spreadsheet totals wrong?
Because it reads the table as flattened text and estimates the total by mental arithmetic rather than running a real calculation. The fix is to have it use its code or data-analysis tool, which writes and executes code over the file for an exact result.
02Does AI actually run my Excel formulas?
Not when it reads the sheet as text: a formula becomes an inert string it cannot evaluate. Only a code or data-analysis tool recomputes values from the underlying cells. Without one, the model approximates the formula's output.
03What is the best file format to give an AI a table?
Clean CSV with a single header row and one table per prompt is the safest general choice. Research shows accuracy varies by format, and messy styled spreadsheets with merged cells are the hardest for models to read correctly.
04Why does AI struggle with large spreadsheets?
Context windows are finite, so big sheets get truncated, and benchmarks show accuracy falls as tables grow, especially on sums and aggregates. A code tool that reads the whole file from disk avoids the truncation problem.
05Can AI misread numbers because of commas and decimals?
Yes. Formats like 1,000 versus 1.000 mean different things by locale, and Indian lakh grouping (1,00,000) adds more ambiguity. If the model guesses the locale wrong, it can be off by a factor of a thousand while sounding certain.
The takeaway is simple: an AI reading a table is guessing at a grid it can no longer see, and the more your data looks like a real spreadsheet, with merged cells, multiple tabs, and mixed number formats, the more it guesses. Hand it clean CSV, one table at a time, with units spelled out, and for anything you actually need to be correct, make it run code instead of read. Treat the model as a writer of calculators, not as a calculator, and the wrong totals mostly disappear.
