Excel to RAG: formulas, cached values and merged cells need explicit rules

Excel ingestion for RAG must distinguish formulas, cached values and workbook structure before turning cells into prose. A convenient row export can hide stale calculations, merged headers and hidden context, producing a fluent answer that misrepresents what the workbook actually establishes.

Keep formula text and cached values separate

The openpyxl tutorial explains workbook loading and data_only. Cached results are not freshly computed values. The formula documentation states that openpyxl does not evaluate formulas. If the workbook has not been recalculated by an appropriate spreadsheet engine, a cached value can be absent or stale. Preserve the formula and its available cached result as different fields, with a clear indication of their provenance.

Preserve headers and visibility context

The worksheet API exposes worksheet structure, including merged cells and access to rows and columns. A merged title can govern several data columns even though only its anchor cell stores the value. Hidden rows or sheets may contain assumptions rather than irrelevant clutter. Define whether they are eligible evidence instead of silently dropping them or exposing everything as equally applicable public-facing content.

Test a budget workbook deliberately

Use a workbook with a computed total, a merged scenario heading and a hidden assumptions sheet. Ask which scenario the total describes and what inputs determine it. A correct ingestion should recover the relationship while distinguishing the displayed cached value from the unevaluated formula. This fixture tests semantic structure, not spreadsheet execution or a guarantee that the budget is accurate.

Workbook item	Required evidence
Formula cell	Formula and cached-result status
Merged header	Scope over subordinate cells
Hidden assumptions	Explicit inclusion policy and visibility
Named sheet	Stable source location and revision

Serialize rows with their governing context

A retrieved row needs sheet identity, row labels, applicable headers, units and any relevant assumptions. Do not flatten an entire workbook into one anonymous text stream. Preserve cell addresses so a reviewer can inspect the original. When a formula references another sheet, keep the reference visible rather than presenting its cached result as an independently verified measurement.

Validate answerability without recalculation claims

Check whether questions about scenario, unit and assumptions resolve to the correct cells. Flag missing or stale cached values for review; do not invent a computed replacement in the text pipeline. The output should state what was read and what remains uncomputed. This keeps search evidence useful while preventing a document-ingestion library from being mistaken for a calculation engine or an audit of the workbook’s financial correctness.