Excel Shared Strings Table: The Hidden Vocabulary Inside xl/sharedStrings.xml That Preserves Deleted Cell Contents and Reveals Editing History

Why Excel Has a Shared String Table at All

A workbook with ten thousand rows of customer data typically reuses the same country names, product codes, status labels, and category strings over and over. If every worksheet cell stored its own copy of "United Kingdom", the XLSX would balloon. The Office Open XML specification solves this with a single shared string table inside the package: every distinct text value appears once at xl/sharedStrings.xml, and worksheet cells reference it by zero-based index.

The t="s" attribute marks the cell as a shared-string reference. The value 42 is not a number to display; it is the zero-based offset into the table. The cell’s actual text lives at /sst/si[43] inside sharedStrings.xml. This indirection is the heart of the format and the reason the table behaves the way it does.

The count attribute is the total number of shared-string references made by the worksheets. uniqueCount is the number of <si> entries in the table. The two numbers tell a reader at a glance how heavily the workbook reuses text: a workbook with count=17284 and uniqueCount=612 is dominated by a small vocabulary repeated many times. A workbook with count=900 and uniqueCount=900 is one where every text value is unique — a strong signal that the workbook contains free-text data such as comments, descriptions, or names rather than categorical data.

The Index Order Is the Chronological Insertion Order

Excel does not sort the SST. New entries are appended at the end as they appear during workbook construction. This is the first forensic property of the table: the index sequence preserves the order in which distinct text values were first typed into the workbook.

A workbook whose first hundred SST entries are sheet headers (“Date”, “Account”, “Amount”, “Memo”), followed by entries that look like draft column titles (“TEMP_lookup”, “FIXME_check_rates”, “Old_GL_code”), followed by the production data, tells a reader the author scaffolded headers first, prototyped lookups next, and only then pasted real values. A workbook whose first entries are city names, then a long block of personal names, then a sparse tail of one-off comments, suggests the workbook started as a directory and accumulated annotations over time.

Deleted Cells Leave Their Strings Behind: The Orphan Problem

When a user deletes a cell or clears its content, Excel removes the cell’s reference from sheet1.xml. It does not remove the corresponding entry from sharedStrings.xml. The string remains in the table, no longer referenced by any worksheet, and survives every subsequent save until the workbook is rewritten with a different tool or saved through Excel’s “Save As” in some configurations.

These leftover entries are called orphan strings. They are trivially detected: enumerate every shared-string reference in every worksheet, build the set of used indexes, and subtract from the full range [0, uniqueCount). Whatever remains was once on a worksheet and is no longer.

The output is a list of strings that were once on a worksheet and were subsequently deleted. The index also tells you roughly when in the workbook’s history the string was first introduced relative to its still-living neighbours.

Rich Text Runs: The Formatting History of a Cell

Cells that combine multiple formatting runs — bold for one word, italic for another, a different colour or font for a third — are stored as a sequence of <r> elements inside an <si> entry, each with its own <rPr> (run properties) block. The runs preserve the exact formatting state of every fragment of text the author ever applied.

A reader sees not only the text but its visual emphasis: the word URGENT in red, the rest in plain Calibri. Strip the styling out of the cell and the runs still describe the original formatting verbatim. For investigators working with redacted workbooks, the rich-text runs frequently reveal what was once highlighted, underlined, or coloured for attention — a forensic signal independent of the visible content.

Phonetic Guides and Other Hidden Annotations

The <si> element can carry far more than text and runs. Three less-obvious children are worth knowing.

The Alternative: Inline Strings

Not every workbook uses the SST. The OOXML spec defines a second mode in which a cell stores its text directly inline:

Excel itself almost never writes inline strings. They are common in workbooks generated by third-party libraries (Apache POI, EPPlus, openpyxl in some configurations, every JavaScript XLSX writer in streaming mode) because writing inline strings does not require a second pass to build the table. The forensic implication is the opposite of the SST: inline strings cannot leak orphans, but they reveal the writing tool by their mere presence. A workbook that mixes t="s" and t="inlineStr" cells was almost certainly produced by a pipeline of at least two different tools — for example, an export from a Python service that wrote inline strings, opened and saved once by Excel which migrated some cells into the SST and left the rest inline.

Detecting When count and uniqueCount Lie

Both attributes on the <sst> root are optional and not authoritatively validated by Excel. Some tools write them. Some omit them. Some write them stale, with the result that the declared counts no longer match the actual contents of the table.

A mismatched uniqueCount is a near-certain indicator that the workbook was edited by something other than Excel after the last clean save. The most common cause is a third-party library that appended a string to the table without updating the header. The same workbook opened in Excel and re-saved would have a corrected count. Spotting the mismatch is one of the cheapest authenticity checks available:

What an Investigator Reconstructs from the SST

An analyst with five minutes and a ZIP utility can extract a remarkably complete picture of the workbook’s editing history from sharedStrings.xml alone.

Document Inspector Does Not Touch the SST

File > Info > Check for Issues > Inspect Document has no item that addresses shared strings. The Document Inspector’s “Hidden Worksheets” option deletes hidden sheets but leaves their strings in the SST as orphans. The “Custom XML Data” option does not consider sharedStrings.xml at all. The “Document Properties and Personal Information” option clears docProps/core.xml but never reaches into the SST. Running every inspector option and seeing “No items found” tells you nothing about the orphan strings still present in the table.

Saving the workbook through “Save As > Excel Workbook (*.xlsx)” from a freshly opened Excel session occasionally rewrites the SST and drops orphans, but the behaviour is not documented and not reliable across Office builds. Treat it as a side effect, not a defence.

How to Actually Rewrite the SST

Cleaning the shared string table is a structural operation: every worksheet cell that references the table by index must be rewritten with the new index, and the table must be rebuilt to contain only currently-referenced strings. Four approaches, in increasing order of robustness.

Pre-Share Checklist

Run this checklist against any workbook leaving your organisation, especially after a long editing history of deletions, renames, and copy-pastes.

Conclusion

The shared string table at xl/sharedStrings.xml is one of the oldest pieces of the OOXML format and one of the most underappreciated metadata layers in any workbook. Its design as a deduplicated, append-only vocabulary makes it efficient for storage and convenient for analysis, and it makes it a persistent record of every distinct text value the workbook has ever held. Deleting a cell never deletes a string. Orphan entries accumulate over the life of the file. Rich-text runs preserve formatting history. Phonetic guides and whitespace escapes leak provenance. Document Inspector does nothing about any of it.

For workbooks staying inside an organisation, the SST is exactly what it is supposed to be: a compact lookup table that keeps file sizes small and editing fast. For workbooks crossing the perimeter, it is the closest thing an XLSX has to a typing-history log — a record of values that were once on a sheet, in roughly the order they were first introduced, with the formatting they were once given. The only durable defence is to rewrite the table as part of a coordinated pre-share pipeline that touches every metadata layer in one pass, leaving recipients with the values the author intended to disclose and none of the vocabulary they thought they had deleted.