Chunking a spreadsheet

A table inside a document has prose around it telling you what it is. A spreadsheet has none. It is sheets of cells, some of which are headers, some of which are notes in row 1, and some of which are the result of a formula you cannot see.

Splitting a spreadsheet is a different problem from splitting a document that happens to contain a table. The techniques for tables in prose assume there is a document around the table; here the table is the document.

What makes a spreadsheet harder than a table

No introducing text. Nothing says what the sheet is about. The filename, the sheet name and the header row are the entire available context, and one of them is often Sheet1.

Headers are not one row. Real spreadsheets have a title in row 1, a blank row, merged category headers spanning several columns in row 3, and the actual column names in row 4. A parser looking for “the header row” finds the title.

Rows are wide. Sixty columns is normal. A single row serialised with all its column names can exceed any sensible chunk size on its own, and most of those columns are irrelevant to any given question.

Several sheets, several schemas. One file contains a data sheet, a lookup sheet, a pivot, a notes sheet and three sheets nobody has opened since 2023. They are unrelated to each other.

Values are computed. What you extract may be a formula string or a cached value, and which one you get depends on the extractor and the file. Neither is self-explanatory.

Meaning lives in formatting. A red cell means overdue. A strikethrough row means cancelled. Bold means total. Extract to text and all of that is gone, silently, and the remaining numbers look like data rather than like data with a caveat.

The split, shown

A sheet cut by size, then serialised per row with its real header.

=== SIZE-DRIVEN CUT OF FLATTENED CELLS ===
--- chunk 1 ---
Regional Inventory — updated 2026-06-30

Warehouse,SKU,Description,On hand,Reserved,Reorder point
LON-1,8842-XL,Insulated jacket XL,142,18,60
LON-1,8842-L,Insulated jacket L,7,4,60
MAD-2,8842-XL,Insulated jacket XL,0,0,40
--- chunk 2 ---
MAD-2,9931,Wool liner,88,12,25
BER-1,8842-L,Insulated jacket L,31,0,60
BER-1,9931,Wool liner,4,4,25

Chunk 2 is six numbers per line with no column names, no sheet title and no date. A query asking which items are below their reorder point has to match against a chunk that contains neither “reorder” nor any word describing what these numbers are.

=== ROW SERIALISED, HEADER AND SHEET CONTEXT CARRIED ===
--- chunk (one per row) ---
[Regional Inventory — sheet: Stock — as at 2026-06-30]

Warehouse BER-1 holds 4 units of SKU 9931 (Wool liner) on
hand, with 4 reserved and a reorder point of 25. On-hand
quantity is below the reorder point.

Columns: Warehouse=BER-1, SKU=9931, Description=Wool liner,
On hand=4, Reserved=4, Reorder point=25

The row is now a sentence containing the words a person would search for, plus the structured values for filtering, plus the sheet’s identity and date. The derived clause about the reorder point is the kind of thing worth computing at ingest, because nobody’s query will phrase itself as an inequality between two columns.

The strategy

1. Split by sheet first, and treat each sheet as its own document. Different schemas, different subjects. Carry the sheet name and the workbook name on every chunk; they are frequently the only description that exists.

2. Detect the real header row rather than assuming row 1. Look for the first row where most cells are non-empty short strings and the row below it is typed differently. Handle merged category headers by flattening them into compound column names — Q2 / Actual, Q2 / Forecast.

3. Serialise each row as a sentence, one chunk per row, for record-shaped sheets. This is the highest value transformation available. Embeddings handle a sentence far better than a comma-delimited line because a sentence is the kind of text the model was trained on, and each row becomes a precise, self-describing retrieval target.

4. Select the columns worth serialising. Sixty columns produce an unreadable sentence. Choose the identifying columns and the ones queries actually mention; keep the rest as structured fields for filtering rather than as embedded text.

5. Group rows where a row is not a record. A time series has one row per date and no row means anything alone. Group by the entity — one chunk per region containing all its months — so the chunk covers a subject rather than an instant.

6. Keep the notes sheet, and index it as prose. Assumption sheets, changelogs and README tabs are the only real documentation the workbook has, and they route to your ordinary prose handler rather than to any of this.

7. Compute the derived statements a query would ask for. Below reorder point, overdue, variance against forecast, top by value. These are cheap at ingest and they put searchable language onto rows that otherwise contain only numbers.

8. Preserve the workbook’s own dates. A spreadsheet is a snapshot, usually undated inside the cells and dated in the filename or the modified time. Without a date on the chunk you will answer this quarter’s question from last year’s export.

Where a retriever is the wrong tool

Worth saying plainly, because a lot of effort gets spent avoiding it.

Aggregation questions are not retrievable. “Total stock across all warehouses,” “how many SKUs are below reorder point,” “which region grew fastest.” These require computing over all rows, and retrieval returns some rows. No chunking strategy makes a sum emerge from a nearest-neighbour lookup, and a system that answers such a question from three retrieved rows will answer confidently and wrongly.

Filters and comparisons want a query language. If the sheet is tabular data and the questions are tabular questions, load it into a database and query it. Retrieval is for finding the passage that discusses something, not for computing over records.

The honest architecture for a workbook-heavy corpus is usually both: the numbers in a database, the notes and documentation in the index, and the row serialisation only for the sheets where people genuinely ask “tell me about this item” rather than “add these up.” Deciding which sheets are which is a routing decision and it is worth making explicitly.

What still breaks

Pivot tables and cross-tabs, whose meaning is two-dimensional and which serialise into nonsense. Cells whose value is an error. Hidden rows and columns, which extract as content nobody intended to publish. Charts embedded in a sheet, which are figures with no caption. Cross-sheet formulas, where a cell’s value depends on a sheet you routed elsewhere. And the formatting semantics — the red cells — which are simply lost.

How to tell if it worked

Take ten rows you know are important and check that each serialised chunk names its sheet, its date and its identifying columns. Then read a serialised row aloud. If it does not parse as an English sentence, the template is wrong and the vector will be poor.

Then test the failure that matters. Ask your system three aggregation questions whose correct answers you know from the spreadsheet itself. If it answers them at all, it is answering from a handful of retrieved rows, and you have learned that this corpus needs a database rather than a better splitter — which is a more useful thing to learn early than late.