Excel Files in Enterprise AI: Why Spreadsheets Break RAG and What to Fix First

The Monday question that exposes everything

A regional controller types one question into the internal assistant: what is our committed Q4 logistics spend in EMEA, excluding one-off capex? Four seconds later she has a number, a fluent sentence and three citations. Two of those citations are worthless. One points at a pivot table whose cache was last refreshed in August; the other points at a budget workbook where the word committed exists only as a background colour that a planner applied by hand. The number is plausible, so it gets forwarded, and the argument starts two days later in a planning meeting.

Nothing in that story is a model failure. It is a file-format failure. Excel is the most widely used analytical tool in the enterprise and the least cooperative input for enterprise AI, because a workbook is four things stacked on top of each other: a calculation engine, a flat database, a presentation layer and an informal change log. An assistant that reads a workbook as if it were a document will extract the right cells and still produce the wrong answer, with full confidence and a citation attached.

Five structural problems that live inside almost every workbook

Most spreadsheet pain in AI projects comes from a small set of patterns that repeat across finance, operations, HR and sales. None of them are exotic, and none of them are visible to a parser designed for PDFs or prose. The result is that the same workbook can look perfectly clean to the person who built it and be genuinely ambiguous to any system that did not sit next to them while they built it.

The five problem groups below cover the vast majority of failed retrievals we see when teams move from a spreadsheet demo to a production assistant that other people depend on. Each one has a fix, but the fix is never just a better prompt. It is a structured extraction step that happens before the language model is allowed to answer.

• Layout used as logic — merged header cells, two-row titles, spacer columns and a totals row in the middle of the table. A human reads the intent; a parser reads unnamed columns and stops trusting the file.
• Formulas without cached values — workbooks saved without recalculated results, links to files on a colleague's laptop, or INDEX and MATCH chains that break when the file moves. The assistant sees either a formula string or a zero, never the number the business actually uses.
• Hidden layers — hidden rows, filtered-out regions, white-on-white text and secondary sheets where the real adjustments live. The visible answer and the true answer differ, and nothing inside the file says which one is authoritative.
• Many tables on one sheet — a summary block, a detail table, a pivot cache and a manual footnote on the same tab, with no schema declaring which one wins when they disagree.
• Semantics stored in formatting — colour-coded status, comments and cell notes, custom number formats, and units typed into the cell text (thousands, euros, per month) instead of being held in their own field.

Why converting everything to CSV makes it worse

The reflex fix is to export the workbook to CSV and load it into the ingestion pipeline. It feels clean because the output is tidy, and it is often the worst decision available. Flattening removes precisely the layer that answers the controller's question, then hands the remaining numbers to a model that has no way to know what was deleted.

CSV also strips provenance. Two files named budget_final.csv from two departments become indistinguishable once loaded, so the assistant cannot say which one it used, whether it was the approved version, or whether a later revision exists. Auditability, which is the whole point of putting AI in front of financial data, disappears at the first transformation step.

• Formulas disappear — the assistant sees 48,200 but not that it equals revenue minus credit notes minus a manual override typed in March.
• Header hierarchy collapses — a two-level header becomes a column literally named Unnamed: 7.
• Empty and zero merge into one value — a blank cell usually means not yet entered, which is a different fact from nil.
• Cross-sheet joins vanish — the mapping between cost centre codes and business units lived on sheet three, and nobody exported sheet three.
• Comments, notes and colour rules are lost, so every caveat the planner attached to the number is gone.

What retrieval has to do with a spreadsheet

Retrieval for prose is a solved-enough problem: split by paragraph, embed, match meaning, pass context to the model. Workbooks break the assumptions behind that chain. A single row means nothing without its column headers, its sheet name, its table boundaries and the version of the file it came from. Chunk by row and you strip exactly the context the model needs; chunk by sheet and the chunk is too large and too noisy to retrieve precisely.

Numbers also behave badly in embedding space. Two rows with 48,000 and 51,000 produce nearly identical vectors while supporting completely different decisions, so similarity search is the wrong tool for a numeric question. A question like what did we spend in total is an aggregation, not a lookup, and the honest way to answer it is to compute over a typed table rather than to retrieve a paragraph that happens to contain a number.

That leads to the design most teams arrive at after their first failed pilot: table-aware extraction, plus a query router. Extract each sheet into a typed structure with headers, units, data types and a stable key, keep the formula next to its computed value, and store the file version and cell range as metadata. Then classify the incoming question before retrieving — lookup, aggregation, comparison, or explanation — and route it to the right mechanism.

• Lookup — find the value at a specific intersection and return the cell reference with it.
• Aggregation — run a real computation over the extracted table, then show the filter and the rows counted.
• Comparison — align two versions or two entities on the same key before comparing, and say which key was used.
• Explanation — retrieve the formula, the comment and the neighbouring context, not just the number.

Governance: which number is the number?

Workbooks multiply. Q4_forecast_final_v7_reviewed_AK_comments.xlsx is a real pattern, not a joke, and every copy carries equal weight once it lands in a shared drive. An assistant that indexes all of them without a versioning rule will eventually cite the superseded one, and the first time that happens in front of an auditor the entire programme loses credibility. Versioning, owner metadata and a staleness signal are not nice-to-haves; they are the difference between an answer and a liability.

Permissions matter just as much. A workbook containing salary bands or customer-specific pricing must not be readable by every employee who can ask the assistant a question. Permission inheritance has to be enforced at retrieval time, not at the interface, and the answer must show the source cell so a reviewer can check it. For many organisations the data also cannot leave the network at all, which pushes the conversation toward on-premise AI and file-level residency controls rather than a public API.

A remediation checklist for the next quarter

You do not need to rebuild every workbook to make enterprise RAG work. You need to stop pretending all workbooks are documents and start treating the important ones as small databases with a schema, an owner and a version. The checklist below is deliberately ordered: each step makes the next one cheaper, and doing them out of order usually produces a demo that nobody trusts.

The first pass should cover a narrow set of high-value files — the monthly close pack, the pricing model, the headcount tracker — rather than the entire shared drive. Prove that answers are correct and traceable on twenty files before you scale to two thousand. Meanwhile, wherever a live system already holds the underlying data, connect to it instead of re-parsing an export; Pilardin supports that path through live system connectors described at /pipeline, which removes whole classes of staleness problems before they start.

• Inventory the top 20 workbooks by decision impact, and name an owner for each one.
• Extract tables into typed schemas with headers, units, data types and a row key; keep the formula beside the computed value.
• Reject or flag files with broken external links, uncached formulas or missing headers instead of silently indexing them.
• Record file version, sheet name and cell range as metadata on every chunk so answers can cite a location, not just a filename.
• Add a query router so aggregations are computed rather than retrieved.
• Enforce source-system permissions at retrieval time and log every answer with the cells it used.
• Set a staleness rule: flag any workbook untouched for a defined period before it can be cited as evidence.

What changes when spreadsheets stop being the bottleneck

The point of fixing Excel handling is not to make an assistant that can open xlsx files. It is to make the numbers in those files usable in the same conversation as the contract, the policy and the ticket, without a human re-typing anything. When extraction, versioning, permissions and a query router are in place, a controller can ask a question across a budget workbook and an ERP extract and get one answer with two citations, each pointing at a specific cell and a specific version.

Until then, the honest framing is that Excel is where enterprise AI projects go to die quietly: the demo works, the pilot stalls, and nobody can explain why the totals are off by four percent. Getting the workbook layer right is unglamorous engineering, and it is the reason some deployments earn trust while others stay in the sandbox. If you want to see how that layer fits with document chat and specialised modules in one workspace, the Matrix overview at /matrix is the shortest route in.

FAQ

Can an AI assistant read Excel files directly without converting them?

Modern tools can open xlsx files, but opening is not the same as understanding. A production-grade system should parse the workbook into typed tables, resolve the formula layer, capture merged headers and hidden regions, and attach version and cell-range metadata. Without that extraction step, the model is guessing at structure even when it reads the correct cell.

Is CSV export good enough for enterprise RAG over spreadsheets?

Rarely. CSV is fine for a single flat, clean table with one header row. The moment a workbook contains formulas, multi-level headers, cross-sheet mappings or manual annotations, the export deletes the evidence needed to verify an answer. If you must use CSV, generate it from a controlled extraction step and keep the source workbook reference alongside it.

Why does the assistant give a wrong total when the cells are correct?

Usually because the question required computation and the system performed retrieval. Similarity search over numbers cannot add them up, and a pivot cache or a stale total row can look authoritative. The fix is a query router that classifies aggregation, comparison, lookup and explanation questions and sends each to the appropriate mechanism, with the filter and row count shown in the answer.

How do we handle workbooks containing salary or pricing data?

Treat permissions as part of retrieval, not part of the chat interface. The assistant should only surface content the asking user is already entitled to see in the source system, and every answer should log which file, sheet and cells were used. Where regulations or internal policy forbid data leaving the network, run the retrieval and inference layer on infrastructure the organisation controls.

Where should we start if we have thousands of spreadsheets?

Start with the twenty files that decisions actually depend on, and make answers on those files correct and traceable before widening the scope. In parallel, check whether the same figures already exist in a live system; connecting to that source removes staleness and version sprawl that no amount of parsing can fix.