Microsoft Excel Spreadsheet (Open XML)

10,000 Cells of "London". Barely Any Extra Bytes. How?

Open an XLSX file containing 10,000 rows of "London" and the file is barely larger than one containing the word once. The Shared Strings Table is what makes this work.

Every unique text value in a workbook is stored exactly once in xl/sharedStrings.xml and referenced by a zero-based index. A cell containing text uses <c r="A1" t="s"><v>0</v></c> — the t="s" attribute says "type is string, value is SST index 0." Numbers live differently: <c r="B1"><v>42</v></c> has no type attribute, and the value is stored inline as a literal number.

This duality is the difference between XLSX and CSV. CSV has no types — every cell is plain text, and spreadsheet applications have to guess whether 00123 is a leading-zero string or the number 123, whether 1/5 is a date or a fraction. XLSX removes the guessing. A cell's type is declared, its value is either stored inline or referenced by index, and the Shared Strings Table deduplicates text on the way in. The file-size payoff is incidental. The design payoff is that the format knows what each cell is before anyone reads it.

That is how XLSX keeps text and numbers apart. Dates are where the system gives up — they live as numbers, and they carry a bug older than XLSX itself.

Excel Remembers February 29, 1900. That Day Never Existed. Why?

February 29, 1900 does not exist. Under the Gregorian calendar, century years are leap years only when divisible by 400 — 1900 was not. But Excel stores date serial number 60 as February 29, 1900, and every date from March 1, 1900 onward is off by one from the true count.

This was not a bug that slipped through. Lotus 1-2-3 had it in 1983. When Microsoft built Excel to read Lotus files, the leap-year error was deliberately preserved for compatibility, and every Excel version since has kept it. A spreadsheet saved in Lotus on an IBM PC in 1984 and opened in Excel today produces the same date arithmetic — because Excel never fixed the ghost leap day.

Dates are numbers with a costume. Serial 1 is January 1, 1900. Serial 44297 is April 10, 2021. The number is stored in the cell; the formatting tells the renderer to display it as a date. Remove the format and the number reappears. Change a text cell to a date format and it becomes February 29, 1900 — the ghost leap day, waiting. An optional 1904 date system, originally shipped on Mac, starts from January 1, 1904 and skips the bug. It is declared in workbook.xml via <workbookPr date1904="1"/>.

Dates inherited one constraint from 1983. The grid around them carries another — a hard wall you probably have never reached.

The Grid Ends at Row 1,048,576. Why That Number?

Your spreadsheet stops at row 1,048,576. Column XFD is the wall. Beyond that, nothing exists — the format does not address cells outside the grid.

The limits are 2²⁰ rows × 2¹⁴ columns, introduced in Excel 2007 when XLSX replaced the binary XLS format. XLS topped out at 65,536 rows and 256 columns (column IV). The jump was 16× rows and 64× columns. These limits are not defined in ECMA-376 — they are an Excel implementation constraint that became the de facto standard. The format itself is unbounded.

Formulas live inside <f> elements, stored as plain text: <c r="D2"><f>SUM(B2:B100)</f><v>1500</v></c>. The <v> element caches the last calculated value. When an application opens the workbook, it renders the cached value immediately without recalculating, then recalculates only on edit or refresh. This split matters: a read-only tool does not need a calculation engine. LibreOffice Calc, openpyxl, and a JavaScript SheetJS parser can all display the results of a million-row sheet without evaluating a single formula.

Shared formulas compress repetition. When a column has the same formula with shifting references — =A2*B2 in row 2, =A3*B3 in row 3, and so on — Excel stores one copy with a ref attribute indicating the range it covers. The format knows copy-drag columns are common and optimizes for them at the XML level.

A million rows and fourteen thousand columns is room for almost any dataset on a single worksheet. But what happens to the data on the way in is where most spreadsheet stories actually break.

Excel Knows Text From Numbers. So Why Did It Rename Human Genes?

In August 2020, the HUGO Gene Nomenclature Committee renamed dozens of human gene symbols. MARCH1 became MARCHF1. SEPT1 became SEPTIN1. DEC1 became DELEC1. HGNC's fix was to rename the genes. Microsoft did not patch Excel. Open genenames.org and search for MARCHF1 — the record still lists MARCH1 as a previous symbol.

The problem had existed for two decades. Gene symbols like MARCH1 and SEPT1 were being auto-converted to dates ("1-Mar", "1-Sep") every time a researcher imported a CSV of gene expression data into Excel. Excel's auto-date detection runs before a human sees the cell, and once the conversion happens the original symbol is gone — there is no undo for a file that was saved. The errors were pervasive enough across published genomics papers that HGNC updated its nomenclature rules to make date collisions structurally impossible.

The root cause connects directly to the Shared Strings Table. XLSX protects types once data is inside the file — a cell marked t="s" stays text forever. But CSV has no types. When Excel imports a CSV, it guesses. The guessing is what destroys the data. The same pattern damages leading zeros in phone numbers and ZIP codes (00210 becomes 210), long account identifiers get shifted to scientific notation, and anything that looks numeric but is meant as a string can disappear.

The single defense is to format the column as text before the CSV is opened, or to build the XLSX directly and never let Excel guess.