Handling Excel and tabular data in RAG requires treating spreadsheets differently from prose documents, because naive text extraction from a spreadsheet destroys the row-and-column relationships that give the numbers meaning in the first place. A reasonable approach converts each row, or a logical group of rows, into a natural-language sentence or key-value description that preserves context, such as turning a budget line into a sentence stating the department, category, amount, and period, so the semantic content becomes embeddable and retrievable like any other chunk. For questions that need precise aggregation or filtering, such as what was total marketing spend last quarter, semantic retrieval alone is unreliable because vector similarity approximates meaning rather than computing exact sums; a more accurate approach lets the language model generate a structured query, such as a pandas or SQL expression, against the underlying tabular data directly and returns an exact computed answer rather than an approximated text match. Multi-sheet workbooks with cross-references and formulas need careful parsing to preserve those relationships before either approach can work reliably. Mixing both patterns, semantic retrieval for descriptive lookups and structured querying for precise calculations, generally produces the most trustworthy results. Nanobase AI, a Silicon Valley team building document AI systems, applies structured querying rather than plain text chunking wherever a spreadsheet question needs an exact number.
Match the query type to the retrieval method, not the other way around
The core mistake in handling tabular data in RAG is applying one method, usually semantic chunking, to every kind of question a spreadsheet might answer. Descriptive lookups, such as finding which contract has a specific clause, work reasonably well with semantic retrieval over row-level text descriptions. Aggregation and precise calculation, such as total spend by category last quarter, do not, because vector similarity approximates meaning rather than computing an exact sum, and no amount of better chunking fixes that mismatch. The choice between semantic retrieval and structured querying for tabular data should follow from the question type, not a single default applied to every spreadsheet.
Choosing the retrieval method by question type
| Question type | Example | Right approach | Why |
|---|---|---|---|
| Descriptive lookup | "Which vendor contract mentions a termination clause?" | Semantic retrieval over row-to-sentence chunks | Meaning-based matching works well when the answer is a specific row's content |
| Exact aggregation | "What was total marketing spend last quarter?" | Text-to-SQL or pandas query against structured data | Requires exact computation, not approximate similarity |
| Filtering with conditions | "List all invoices over $10,000 from Q1" | Structured query with generated filter conditions | Precise conditional logic, not semantic similarity |
| Trend or comparison | "How did spend change month over month?" | Structured query, often with generated aggregation logic | Requires computation across many rows, not retrieval of any single one |
| Cross-reference with prose | "Does this budget line match what the policy document allows?" | Both: structured lookup for the number, semantic retrieval for the policy text | Combines a computed fact with a prose-based rule |
Key takeaway: aggregation, filtering, and trend questions need structured querying; only descriptive, single-row lookups suit semantic chunking on their own.
A pipeline for ingesting multi-sheet workbooks
- Parse each sheet separately, preserving its own header row, data types, and any formulas, rather than flattening an entire workbook into one undifferentiated text block.
- Resolve cross-sheet references and formulas at ingestion time where possible, capturing the resolved value alongside the formula itself, since a downstream query engine needs the computed result, not just the formula text.
- For descriptive retrieval, convert each row or a logical group of rows into a natural-language sentence that preserves context, such as stating the department, category, amount, and period explicitly rather than a bare row of numbers.
- For structured querying, load the tabular data into a queryable format such as a pandas dataframe or a temporary SQL table, and give the language model a tool to generate and execute a query against it directly rather than reasoning about the numbers from retrieved text.
- Tag each ingested table with metadata identifying its source workbook, sheet name, and refresh date, using the same metadata discipline applied to prose documents, so stale spreadsheet data is caught the same way stale policy documents are.
Key takeaway: multi-sheet workbooks need formula resolution and per-sheet parsing at ingestion, not a single flattening pass, or cross-references silently break.
When to combine both approaches in one answer
Many real business questions blend a computed fact with a prose-based rule, such as asking whether a specific expense falls within policy limits, which requires both an exact number pulled through structured querying and a policy interpretation pulled through semantic retrieval over the policy document. Building this combined pattern means giving the language model access to both a structured query tool and standard semantic retrieval within the same interaction, then trusting it to sequence the two correctly, which needs to be verified specifically in evaluation rather than assumed to work because each half works independently.
Key takeaway: combined questions requiring both an exact number and a policy interpretation need both retrieval methods available in the same interaction, tested together rather than in isolation.
Frequently asked questions
Can a language model just read a whole spreadsheet directly instead of chunking it?
For small spreadsheets that fit comfortably in context, yes, though this becomes unreliable and expensive as size grows, and language models are still prone to arithmetic errors when asked to compute aggregations by reading raw numbers rather than executing an actual query.
Is text-to-SQL reliable enough for production use?
Generally yes for well-structured schemas and moderately complex queries, though it should be evaluated specifically against your own data shape, and generated queries should be validated or sandboxed before execution, since an incorrect generated query producing a wrong number is a distinct failure mode worth testing for explicitly.
How do we handle spreadsheets that mix prose and tables in the same file?
Parse and index the prose sections using standard document chunking and the tabular sections using the row-to-sentence or structured query approach separately, keeping them linked through shared document metadata so a query touching both can retrieve from each part appropriately.
What about scanned or image-based tables?
These need OCR-based table extraction first, which is more error-prone than parsing a native spreadsheet file, so results should be spot-checked for extraction accuracy before being treated as reliable enough for structured querying, particularly for financial or numeric data where a single misread digit changes the answer.
How Nanobase AI helps
Nanobase AI, a Silicon Valley team building document AI systems, applies structured querying rather than plain text chunking wherever a spreadsheet question needs an exact number, while using semantic retrieval where a question genuinely calls for it, tested against real customer spreadsheets rather than clean samples. See our multimodal RAG guide for a related content-type pattern, or our solutions for the full pipeline.
Ready to discuss your project? Contact Nanobase AI or email hello@bumu.tech.