AI uses for records
RAG spreadsheet evidence retrieval: return the formula and its inputs
By SourceX Editorial · Updated
9 sections
Short answer
RAG spreadsheet evidence retrieval should return the formula behind a cell, every input that formula reads down to typed or imported values, the workbook version and date, and any note explaining an input. The displayed value is the claim to check, not the evidence. Score retrievers on formula chain and input recall, version accuracy and honest abstention, using a task packet like the one on this page.
Retrieval
RAG spreadsheet evidence
Key takeaways
- A displayed spreadsheet value is the claim to check; the formula chain and its leaf inputs are the evidence.
- Capture each cell three ways: displayed text, calculated value and formula, because default exports keep only the first.
- Cached values can be stale or missing, and the calculation chain is not a dependency map, so record calculation state.
- Trace precedents to constants, imports or external links, and record where tracing stops.
- Score formula chain recall, leaf input recall, version accuracy and honest abstention separately from the final answer.
How should retrieval tasks include spreadsheet formulas and inputs?
Treat the displayed number as the answer to be checked, not as the evidence. A spreadsheet retrieval task should require the system to return the cell's formula, the input cells that formula reads (and their own inputs, down to typed or imported values), the sheet and workbook version it read them from, and any assumption cell or note that explains why the inputs are what they are.
That standard follows from how spreadsheets store work. A cell that shows 18.4% is the end of a chain: a formula, a set of referenced ranges, perhaps a lookup into another tab, and somewhere at the bottom a typed number or an import. A retriever that finds the right digits in a chunk of serialized cells has matched a string. It has not shown why the number is what it is, whether it is still current, or which input someone would change to move it.
So the unit of retrieval in these tasks is an evidence set, not a cell. The rest of this page defines what that set contains, how to extract it from Excel and Google Sheets files, a task packet that writes it down, an illustrative case with a counterexample, and the scores that separate a system that found the logic from one that found a matching number.
One cell, three readings: displayed text, calculated value and formula
A single cell can be read three ways, and each answers a different question. The displayed text is what a person saw, including currency symbols, percent signs and rounding. The calculated value is the underlying number. The formula is the instruction that produced it. A retrieval corpus that keeps only one of the three cannot support questions about the other two.
The Google Sheets API makes the split explicit. Its ValueRenderOption returns FORMATTED_VALUE (calculated and formatted by the cell's format, using the spreadsheet's locale), UNFORMATTED_VALUE (calculated but not formatted) or FORMULA (not calculated, so a cell containing =A1 comes back as the text =A1). FORMATTED_VALUE is the default, which means an export written with default settings has already thrown the formulas away.
Microsoft Graph exposes the same distinction for Excel workbooks. Its range resource has values (raw values, with errors returned as the error string), text (the text values of the range), formulas in A1 notation, formulasR1C1, numberFormat and valueTypes. An extraction pipeline that pulls values and text but skips formulas produces a corpus in which every calculated cell looks like a typed constant.
| Reading | What it answers | How to get it | What it cannot show |
|---|---|---|---|
| Displayed text | What a reviewer saw in the sheet or a pasted report | Sheets FORMATTED_VALUE; Graph text | The precision behind the rounding or how the value was produced |
| Calculated value | What number the cell holds as of its last calculation | Sheets UNFORMATTED_VALUE; Graph values; cached v element in the file | Whether it is current, or which inputs produced it |
| Formula | Which cells and functions produce the number | Sheets FORMULA; Graph formulas; f element in the file | The input values themselves, unless they are retrieved too |
| Format and type | Whether 0.184 is a rate, a share or a date serial | Graph numberFormat and valueTypes | Business meaning, which lives in labels and notes |
Why the stored value can be stale, missing or computed somewhere else
A stored value is a record of the last calculation, and nothing more. In the Open XML file format that .xlsx files use, the formula text sits in the cell's f element and the v element stores the cached value from the last time the formula was calculated. Microsoft's documentation notes that the value may be omitted entirely, in which case the reading application is expected to compute it.
Library defaults shape what reaches your corpus. openpyxl, a common Python reader, returns formula text by default; its data_only option returns instead the value stored the last time Excel read the sheet. A file written by a script and never opened in a calculating application can therefore yield empty values for every formula cell, and a file saved while calculation was off can yield values that no longer match their inputs.
Calculation mode is the second trap. Graph reports a workbook's calculationMode as Automatic, AutomaticExceptTables or Manual. In Manual mode a person can change an input and save without the dependent cells recalculating, so the cached value and the formula disagree inside the same file. A task built from such a workbook should record the mode and flag every item whose answer depends on a cell that may not have recalculated.
Do not use the calculation chain as a dependency map. The Open XML calcChain part records the order in which formula cells were last calculated, and Microsoft's documentation says plainly that it does not describe which cells a formula depends on. Dependencies come from parsing formulas or from an API built for the purpose.
Tracing inputs: precedents, direct precedents and where the trace stops
Inputs are found by tracing precedents, and the trace has to go deeper than one level. In Excel's terms, a formula's precedents are the cells it refers to, and its dependents are the formulas that refer to it. A direct precedent is only the first level: the cells the formula points to. The Excel JavaScript API offers getDirectPrecedents for that first level and getPrecedents for the full chain, grouped by worksheet.
The same documentation lists limits that should become fields in your task, because they mark where retrieval cannot be complete. The precedent methods do not cross workbook boundaries, so an input that lives in another file must be resolved by a separate lookup and recorded as an external dependency. When a cell has no precedents the API throws an error rather than returning an empty set, which a pipeline should record as a typed or imported value rather than a failure.
A useful rule for task design: the required input set ends at cells that hold a constant, an import from a named source system, or an external link. Anything above that line is derived and must be explained by a formula in the set. Named ranges, lookup functions and hidden sheets deserve explicit checks, because a formula like a lookup into a rate table reads a different cell for each row and a hidden tab is easy to drop during chunking.
The workbook evidence retrieval task packet, field by field
Each task item is one question about one workbook state, with the full evidence set written down before the item is used. The fields below are an editorial template, version 1.0 dated 2026-10-18. Example values are illustrative and describe no real workbook.
At a glance
The workbook evidence retrieval task packet, field by field
Keep the formula chain and the input values as separate fields. A system that cites the right formula but the wrong version of an input tab fails in a different way from one that never found the formula, and merging the fields hides that difference.
| Field | What it holds | Illustrative value | Check before use |
|---|---|---|---|
| Query and intent | The user's question and the decision it supports | Why did Q3 gross margin in the forecast drop to 18.4%? | The question needs the logic, not only the number |
| Workbook version and as-of date | File identifier, saved version and the date the answer must hold for | fcst-2026.xlsx, version saved 2026-10-02; as of 2026-10-05 | Version is pinned and stored with the corpus snapshot |
| Target cell | Sheet, address, displayed text and calculated value | Summary!F14; shown 18.4%; value 0.18397 | Displayed and calculated readings both captured |
| Formula chain | The target formula and every derived precedent formula | F14 =F12/F9; F12 =F9-F11; F11 =SUM(COGS!D2:D40) | Chain ends only at constants, imports or external links |
| Required inputs | Leaf cells with values and their origin | COGS!D17 freight rate typed 0.062; Volume!C9 imported from ERP | Every leaf has an origin label: typed, imported or external |
| Assumption evidence | Notes, comments or labels that explain an input | Comment on COGS!D17: carrier surcharge from Sept rate card | Comment author and date retained |
| Calculation state | Calculation mode and whether cached values match a recalculation | Mode Automatic; recalculated values match cached | Mismatches flagged and excluded or labeled |
| Hard negatives | Cells or versions that look right but must not be cited | Prior-version COGS tab; a pasted 18.4% in a slide notes sheet | Negatives share the number or the label, not just the topic |
| Permission scope | Which sheets the asking role may read | Analyst may read Summary and COGS; Payroll tab restricted | Scope matches the file's sharing and protection settings |
| Expected behavior and outcome | Answer, partial answer with gaps named, or abstain; plus the later record that confirms it | Answer citing F14 chain and D17 comment; Oct close confirms freight cost | Outcome record postdates the workbook version |
Does the retrieved evidence support this answer for this user and date?
A formula chain only supports an answer for the version, date and reader it was taken from. Workbooks are edited in place, so the same address can hold a different formula next month. Pin the version, record the as-of date in the item, and treat a citation to any other version as a miss even when the digits happen to match.
Permissions apply to inputs, not just to the cell being asked about. If the margin cell is visible but one of its precedents sits on a restricted payroll tab, the correct behavior may be to explain the chain down to the restricted boundary and say that a further input exists that the asker cannot see. Writing that expected behavior into the item tests whether the system respects access while staying honest about completeness.
Abstention is the right answer more often than test sets assume. When the chain runs into an external workbook that is not in the corpus, or the calculation state check shows the cached value is stale, the item should expect the system to say what it found and what it could not confirm. An item that rewards a confident number in those cases teaches the wrong habit.
Illustrative case: a margin cell whose input tab was overwritten
This case is illustrative; the workbook, figures and people are invented to show how one item is built and checked.
Starting state: a finance analyst asks why the Q3 gross margin in the forecast dropped from 21.0% to 18.4% between two weekly versions. The summary cell is a ratio of gross profit to revenue. Revenue did not change between versions. Cost of goods sold rose because one freight rate input on the COGS tab changed from 3.6% to 6.2% of shipment value, with a cell comment citing a new carrier rate card.
Linked evidence: the summary formula, the gross profit formula, the SUM over the COGS range, the changed freight cell with its comment and author, the unchanged revenue cells, and the two pinned workbook versions. Hard negatives include the prior version's COGS tab and a notes sheet where someone pasted 18.4% as a typed number.
Decision and verified outcome: the expected answer names the freight input change as the cause, cites the chain and the comment, and states that revenue was unchanged. The outcome check is the month-end close record showing freight cost at the new rate. A system that cites the pasted 18.4% on the notes sheet has matched the number and fails the item.
Counterexample: in a third version, someone pasted values over the summary row, replacing the formula with a constant 18.4%. The displayed value is identical, but the formula chain no longer exists in that version. The correct behavior is to report that the cell holds a typed value, point to the earlier version where the formula lived, and decline to explain the number from the current file alone.
Scoring a retriever on formula chains instead of matching numbers
Score the evidence set separately from the final answer. Research on spreadsheet encoding shows why: the SpreadsheetLLM work serialized cells with their addresses, values and formats and found that sheets quickly exceed model context limits, which pushed the authors toward compression. Any compression or chunking step can drop the precedents a question needs, so the score should reveal it.
At a glance
Scoring a retriever on formula chains instead of matching numbers
Hybrid table and text benchmarks such as TAT-QA, built from real financial reports, show that many answers need arithmetic over cells plus the paragraphs that explain them. Spreadsheet tasks have the same shape, with formulas and comments playing the part of the explanatory text. The metrics below are an editorial method; thresholds belong to your own baseline, and no results are reported here.
- Formula chain recall: the share of required formulas in the chain that the system retrieved.
- Leaf input recall: the share of required leaf inputs retrieved with the correct version.
- Version accuracy: whether every cited cell comes from the pinned workbook version.
- Hard negative rate: how often a pasted copy, prior version or same-number cell was cited.
- Boundary honesty: whether the answer names restricted, external or stale inputs it could not use.
- Abstention correctness: whether items that expect a partial answer or abstention got one.
- Citation support: whether each material claim in the answer maps to a cell or comment in the evidence set.
Finding workbooks with enough history to build these tasks
Real workbooks with saved versions, comments and a later outcome record make far better tasks than synthetic grids, because the hard negatives and stale values occur naturally. The useful sources are forecast models, pricing calculators, commission and rebate trackers, and close workbooks that a team kept over several periods alongside the system records that confirmed the numbers.
With SourceX, workbook datasets are sourced on request from U.S. businesses rather than held in stock, so a buyer describes the scope, volume, format, timing and licensing terms needed for a workbook evidence set. Before any records are delivered, the supplying company approves that scope and signs the agreement. Formula text, comments and author names can all carry personal or confidential information, so the request should say which of them the evaluation actually needs.
Frequently asked questions
What should the workbook evidence retrieval task packet include?
At minimum: the question, a pinned workbook version and as-of date, the target cell in both displayed and calculated form, the full formula chain, the leaf inputs with their origin, any comments that explain an input, the calculation state, hard negatives, the asker's permission scope and the expected behavior. Add a later outcome record when you want to test whether the answer was actually right.
Is the displayed value ever enough evidence on its own?
Only when the question is literally what the sheet showed, such as reconciling a number quoted in an email. For any question about why a number is what it is, or what would change it, the formula and its inputs are the evidence and the displayed value is the claim being checked.
How do I extract formulas rather than values from Google Sheets or Excel files?
In the Google Sheets API, request the FORMULA value render option, since the default is the formatted value. For Excel files, the Microsoft Graph range resource returns formulas alongside values and text, and Python readers such as openpyxl return formula text unless you ask for cached values. Capture both readings so the item can compare them.
What if the workbook was saved with manual calculation?
Then cached values may not match their formulas. Record the calculation mode, recalculate in a controlled environment, and compare. Items whose target or inputs disagree after recalculation should be excluded or labeled, and the expected behavior should be to report the inconsistency rather than trust either number.
Which outside record confirms that the workbook-backed answer was right?
Something outside the workbook that postdates it: a month-end close entry, an invoice, a posted price list or a signed approval. The workbook shows the reasoning a team used; the outcome record shows whether the business acted on that number, which is what lets you grade an answer as correct rather than merely consistent.
Sources
- The Google Sheets API can return a cell as a formatted value (the default), an unformatted calculated value, or the uncalculated formula text. Source
- The Microsoft Graph workbook range resource exposes raw values, text values, formulas in A1 and R1C1 notation, number formats and value types. Source
- Microsoft Graph reports a workbook's calculation mode as Automatic, AutomaticExceptTables or Manual. Source
- In SpreadsheetML, the f element holds a cell's formula and the v element caches the value from the last calculation; the cached value may be omitted. Source
- The calculation chain part records the order in which formula cells were last calculated and does not describe formula dependencies. Source
- The Excel JavaScript API retrieves all or direct precedents and dependents of a formula, does not cross workbook boundaries, and throws ItemNotFound when none exist. Source
- openpyxl's data_only option controls whether formula cells return the formula (default) or the value stored the last time Excel read the sheet. Source
- SpreadsheetLLM describes spreadsheet grids as hard for language models and proposes compressing a serialization that includes cell addresses, values and formats. Source
- TAT-QA is a question answering benchmark over hybrid tables and text from real financial reports, where answers often require numerical reasoning. Source
Tell us what data you need
Share scope, volume, format, timing and licensing needs. Datasets are sourced on request.