A spreadsheet copilot can explain a number and still get the story behind it wrong. Imagine a real estate feasibility workbook showing a lower development profit than expected. The number is visible, and a language model can describe it in seconds. But explaining why it changed requires more than reading a cell. The answer may depend on construction costs, revenue assumptions, formulas across multiple worksheets, the selected scenario, or the date of the underlying data.
This is where spreadsheet AI becomes an engineering problem rather than simply a language-generation problem. To explain a workbook reliably, a system must understand how its contents relate to one another, distinguish calculated outputs from assumptions, and establish whether the numbers it sees reflect the current calculation state.
A spreadsheet is more than its visible values
A straightforward implementation might extract cell values, convert them into text, and send that text to a language model. For small, simple tables, this can be sufficient. The approach becomes less reliable when a workbook contains multiple worksheets, named ranges, nested formulas, imported data, scenario models, or links between calculations.
Consider a simplified workbook in which Inputs!B2 contains construction cost per square metre, Model!D12 calculates total construction cost, and Summary!C8 reports development profit.
If a user asks why profit has fallen, the system needs to trace the relevant calculation chain. It must determine which formulas depend on the construction-cost assumption, whether that assumption changed, and whether other inputs changed at the same time. Even then, a dependency is not automatically a complete explanation. The fact that a cost input contributes to profit does not establish that it caused the entire decline. The first requirement is a structured representation of workbook context, not a longer prompt.
Build a model of the workbook before generating an answer
The system needs to preserve more than cell addresses and displayed values. It should capture worksheet structure, formulas, number formats, named ranges, relevant metadata, and the relationships between calculations.
For each cell, a useful representation includes its worksheet and address, stored value, formula where applicable, data type, number format, workbook version, and available source information.
These fields help prevent subtle interpretation errors. A value displayed as 12.5% has a different meaning from a currency amount or a quantity, while a hard-coded assumption should not be confused with a formula-generated result. Formatting can help identify the intended presentation, but it does not establish the business meaning of a number on its own.
Dependencies are equally important. A cell record tells the system what is stored in a location; a dependency record tells it which other cells contribute to that calculation.
For example, a simplified relationship might be:
Inputs!B2 → Model!D12 → Summary!C8
This relationship can be represented as a directed graph, allowing the system to trace upstream inputs from a requested output. Real workbooks are more complicated: formulas can branch, reference other worksheets, use named ranges, or contain constructs the extraction system cannot resolve.
The system should preserve those limitations. An unresolved reference must remain unresolved rather than being silently replaced with an assumed relationship.
Keep extraction, calculation and explanation separate
One architectural mistake is to ask the language model to perform every step: interpret the workbook, calculate the result, diagnose the change, and explain it. A more dependable design separates these responsibilities.
The extraction layer reads workbook structure and records the available evidence. The calculation layer establishes numerical results using an appropriate spreadsheet engine or a separately verified deterministic implementation. The explanation layer uses those results to answer the user's question.
This separation matters because reading a formula is not the same as evaluating it. For example, the openpyxl documentation explicitly states that openpyxl does not evaluate formulas. A system using it can inspect formula expressions, but it cannot assume that the cached result represents a freshly recalculated workbook.
The calculation process should therefore expose its status to the explanation layer. The system needs to distinguish an extracted value from a recalculated result, and a recalculated result from one that has passed the relevant validation checks. These distinctions should be represented as data, not left for the language model to infer.
Give the language model structured evidence
Once the workbook has been parsed and the relevant calculations established, the system can assemble a context package for the requested answer. Suppose a user asks why development profit changed. The package might identify the target cell, its formula, the relevant upstream dependencies, the selected scenario, the workbook version, and the status of the calculation. It should also identify missing evidence, such as an input whose source date is unavailable.
A simplified representation could look like this:
{
"workbook_version": "v17",
"target_cell": "Summary!C8",
"scenario": "Base case",
"formula": "=Revenue-Costs",
"dependencies": [
"Model!D12",
"Model!D18"
],
"calculation_status": "verified",
"evidence_status": "partial",
"limitations": [
"Source provenance for one input is unavailable"
]
}
This is an illustrative example, not output from an actual workbook. Its purpose is to show how a system can communicate what it knows and what remains uncertain. A schema can enforce required fields, permitted status values, and the structure of cell references. The system can then reject malformed responses or flag references that do not exist in the relevant workbook version.
However, valid JSON does not prove that an explanation is correct. Structural validation can establish that an answer follows a contract; it cannot establish that the narrative accurately describes the business logic. That requires separate evidence and validation.
Retrieve context according to the question
Sending an entire workbook to a language model is often inefficient. Large files may contain historical worksheets, unrelated calculations, repeated formatting, and data that has no bearing on the question. Instead, context retrieval should follow the task.
When a user asks what drives a particular output, the system should retrieve the target formula, its relevant dependencies, units, scenario metadata, and available source records. When the question concerns changes between workbook versions, it should compare the relevant formulas, values, and metadata across those versions.
A question about whether an assumption is reliable requires a different set of evidence. The system may need the source, publication date, geography, definition, and available quality checks. The numerical value alone cannot answer that question.
This is where spreadsheet context differs from ordinary text retrieval. The system is not merely searching for related passages. It must identify the correct calculation relationships and select evidence that supports the specific question. Retrieval also needs boundaries. Dependency expansion should be limited to relevant parts of the workbook, and incomplete formula chains should be reported explicitly. A system that cannot resolve every relationship should not imply that it has examined the complete model.
Treat uncertainty as part of the data model
Consider a construction-cost assumption with no recorded source date or geographic reference. The copilot may still be able to explain how the assumption affects development profit. It cannot establish from that workbook alone whether the assumption is current or appropriate for the project. Three questions must remain distinct: what the calculation produces, whether the underlying evidence is reliable, and whether the resulting project outcome is acceptable.
The first can often be answered through model logic. The second requires appropriate supporting evidence. The third involves professional judgment and decision-making beyond the calculation itself. Combining all three into a single confidence score would conceal important differences. Instead, the system should report missing provenance, unresolved references, conflicting inputs, and uncertain calculation freshness in explicit terms. If the workbook does not establish something, the explanation should not invent a plausible answer to fill the gap.
Test the failure cases, not just the successful examples
A spreadsheet copilot may perform well on straightforward questions while failing on the cases that matter most. Testing should therefore examine whether it handles cross-sheet dependencies correctly, distinguishes hard-coded values from formulas, detects stale results, respects scenario selection, and reports missing source information.
It should also be tested against renamed worksheets, broken references, formula errors, ambiguous labels, and mismatches between the workbook version and the evidence supplied to the model.
These tests measure different properties. Retrieval accuracy determines whether the system found the right cells. Calculation correctness determines whether the numerical result is valid under the specified calculation process. Explanation fidelity determines whether the narrative matches the available evidence. Uncertainty handling determines whether the system acknowledges what it cannot establish. A single overall accuracy score can hide failures in any one of these areas.
The implementation should begin with a limited set of workbook structures and question types. Each expansion should follow testing of the new structures and their failure cases, rather than an assumption that support for one workbook implies support for spreadsheets generally.
A practical engineering sequence
A first implementation should start by extracting and normalizing workbook contents. It can then resolve supported formula references, build dependency relationships, establish calculation status, and retrieve the context needed for a particular question.
Only after those steps should the language model generate an explanation. The resulting response should be checked against the supplied evidence, its schema, the relevant workbook version, and the calculation status. Material explanations should remain subject to human review before being used in consequential decisions.
This sequence does not eliminate every error. It makes errors easier to detect, isolate, and investigate because extraction, calculation, retrieval, and explanation have distinct responsibilities.
It also makes limitations visible. If a formula cannot be resolved, a calculation cannot be verified, or source information is missing, the system can report that specific limitation instead of producing a confident but unsupported answer.
Conclusion
The hard part of an AI spreadsheet copilot is not describing a number. It is establishing where the number came from, which assumptions affect it, whether the calculation is current, and what the available evidence can actually support.
That requires structured workbook context, explicit dependency relationships, a calculation process separate from language generation, and validation that goes beyond checking whether a response is well formed.
For financial modelling and real estate feasibility analysis, these distinctions are particularly important. A fluent explanation may sound convincing, but a traceable explanation gives an analyst something they can inspect, challenge, and verify. The objective should not be to make every answer sound certain. It should be to make every answer accountable to the evidence.
Top comments (0)