Methodology
Formula integrity in real-world spreadsheets
Plug prevalence, broken references, and formula verification across two public corpora — 4,207 formula-bearing workbooks, identical code.
How often do real spreadsheets contain hardcoded constants where formulas should be? We measure plug prevalence, broken references, and external links across 4,207 formula-bearing workbooks from the Enron corpus [2] and FUSE [1] — two public datasets with nothing in common but the file format. The rate of any high-precision finding rises monotonically with model complexity on both corpora, reaching 60.3% (Enron) and 41.5% (FUSE) on workbooks with ≥1,000 formulas. Private working models consistently show ~1.4× more findings than publicly shared spreadsheets — confirming a hypothesis the FUSE authors stated but could not test [1]. An independent verification against Excel’s own cached values shows 97.31% of workbooks recompute cell-for-cell with zero divergence across 4,033,846 formulas.
1. Introduction
Spreadsheet errors are widely cited as pervasive — field audits consistently find errors in the majority of reviewed models [3] — but corpus-scale prevalence measurements are rare. The studies that exist rely on manual inspection of small samples, typically fewer than 100 files. Automated detection at scale requires a formula engine that can recompute every cell and compare the result to what Excel cached, which no public tool has done on real-world data at this volume.
We report the first automated measurement of three structural findings — hardcoded plugs, broken references, and external links — across two independent corpora. Both are public and unencumbered, the sample seed is fixed, and the commands to reproduce every corpus-level count are given in §8.
2. Data
Two corpora, chosen because they share nothing but the file format. One is a single company’s private working files, the other is whatever the public web was hosting. A finding that holds on both is unlikely to be an artifact of either.
Table 1. Corpus summary. “Formula-bearing” excludes files with zero formula cells — exports and reports that cannot contain a plug by construction.
| Corpus | Source | Total files | Measured | Formula-bearing |
|---|---|---|---|---|
| Enron | figshare 1221767 [2] | 15,929 | 1,000 (sampled, seed 1337) | 567 |
| FUSE | Zenodo 581678 [1] | 249,376 | 10,703 (.xlsx/.xltx, full population) | 3,640 |
Enron was sampled: 1,000 files, uniform random, seed 1337, drawn before any file was read. 999 produced an audit. FUSE was not sampled — every one of the 10,703 .xlsx/.xltx files was processed, so its figures are population values rather than estimates.
We reproduced the FUSE authors’ headline counts exactly before using the corpus: 249,376 unique spreadsheets, 28,616 unreadable by their tooling, 205,978 with zero formula cells [1]. If those had not matched, nothing downstream would be trustworthy.
3. Method
Each workbook goes through the same intake pipeline a user’s upload does. The engine — its own decompressor, zip reader, XML tokenizer, formula compiler and evaluation engine, zero third-party libraries — parses every cell, recomputes every formula from source, and compares each result against Excel’s cached value. Median intake time is 2 ms per workbook; p90 is 19 ms. The full FUSE corpus audits in under a minute.
Figure 1. Independent parser agreement on FUSE (.xlsx subset, n=10,703). Apache POI [1] and Basis agree exactly on both the parseable set and the formula-bearing subset. Two independently written engines landing on identical counts is a stronger correctness signal than either number alone.
4. Detectors
Only high-precision detectors are included in published figures:
- Hardcoded plug — a constant sitting in an otherwise-uniform formula fill-down, identified by bytecode program identity. An overtyped cell is a structural discontinuity, not a heuristic match.
- Broken reference — a
#REF!error in a formula that Excel could not resolve. - External workbook link — a formula referencing another
.xlsx, which breaks on any machine where the linked file does not exist at the expected path.
A fourth detector, formula_deviation, is excluded from every figure on this page. It flags a cell whose formula differs from its neighbours, but over-fired on columns that were internally consistent but simply different from the column beside them — 6,571 flags on a single 110,997-formula workbook. It remains excluded from published statistics until precision improves.
5. Results
The rate of any high-precision finding rises monotonically with model size on both corpora (Figure 2). At the substantial tier (≥1,000 formulas), 60.3% of Enron workbooks and 41.5% of FUSE workbooks contain at least one finding.
Figure 2. Prevalence of any high-precision finding by model complexity. The rate rises monotonically on both corpora. Private working models (Enron) consistently show higher rates than publicly shared spreadsheets (FUSE).
Table 2. Full results matrix. Enron / FUSE in each cell.
| Population | n | Plug | Broken ref | External | Any |
|---|---|---|---|---|---|
| All formula-bearing | 567 / 3,640 | 18.3% / 13.6% | 10.2% / 4.0% | 21.0% / 5.9% | 33.9% / 20.5% |
| ≥ 100 formulas | 326 / 1,928 | 29.8% / 21.9% | 16.3% / 6.5% | 32.8% / 9.6% | 52.1% / 32.5% |
| ≥ 1,000 formulas | 146 / 733 | 43.2% / 29.9% | 22.6% / 10.8% | 41.1% / 9.8% | 60.3% / 41.5% |
Substantial models only (≥1,000 formulas)
Figure 3. Per-detector breakdown at the substantial tier. Hardcoded plugs and external links dominate. The Enron–FUSE gap is widest on external links (41.1% vs 9.8%), consistent with private models referencing neighbouring files that published spreadsheets cannot.
FUSE runs consistently ~1.4× lower than Enron across all tiers and detectors. The gap is not noise — the FUSE authors predicted it and could not test it:
“FUSE can only offer spreadsheets that are intentionally (or inadvertently) made publicly accessible, and as a result, may contain fewer errors than spreadsheets not for public dissemination.” [1]
Measured: 29.9% vs 43.2% plug prevalence on substantial models — a 1.44× gap, on identical code. Spreadsheets people publish are cleaner than the ones they keep.
6. Verification against Excel
Every .xlsx stores the values Excel computed the last time it was saved, making each file its own test case. The engine recomputes every formula from source and compares cell-for-cell.
3,254 of 3,344 FUSE workbooks · 4,033,846 formulas compared · 4,391 mismatches across the 90 files that have any
Figure 4. Formula verification against Excel’s cached values (FUSE only). 97.31% of workbooks with comparable cached values recompute with zero divergence. The remaining 90 files concentrate 97% of all mismatches in 10 files — the signature of a few missing functions rather than broad drift.
This figure uses FUSE only, and that restriction is the point. FUSE files were written by real Excel installations, so their cached values are Excel’s. Enron predates .xlsx — the company collapsed in 2001, the format arrived in 2007 — so its cached values were written by whatever converter the dataset publishers used. Enron cannot support a claim about matching Excel, and we do not use it for one [2].
7. Limitations
- FUSE excludes large files. Common Crawl caps stored binaries at 1 MB, so the corpus structurally under-represents the big models where the plug rate is highest. Its ≥1,000-formula tier is 733 files, but they are small ones.
- Enron is one company, in 2001. Its conventions are not the world’s, and the sample is 1,000 files rather than all 15,929.
- FUSE is 95.7% legacy
.xls, which Basis does not read. The 10,703 files measured are the.xlsxminority of a 2014 crawl [1]. - “Formula-bearing” is doing real work as a denominator. 43% of the Enron sample and 66% of FUSE contain no formulas — they are exports and reports, not models. A file with no formulas cannot have a plug, and including them would halve every rate here.
8. Reproducibility
Both corpora are public and unencumbered — anyone can download them and check the file counts, format breakdown, and formula-bearing share independently. The engine that produced the finding rates is not yet published, so those specific figures are ours to stand behind; the audit the app runs on your own workbook is the same code.
# FUSE — 9.4 GB, split 7z inside a zip curl -L -o fuse.zip https://zenodo.org/api/records/581678/files/fuse.zip/content bsdtar -xf fuse.zip && cat FUSE.7z.* > FUSE.7z bsdtar -xf FUSE.7z fuse-cc-binaries.tar.gz && tar -xzf fuse-cc-binaries.tar.gz # Enron — 993 MB curl -L -o spreadsheets.7z https://ndownloader.figshare.com/files/3242531 bsdtar -xf spreadsheets.7z -C enron/ # audit every workbook; audit.json carries plugs.by_kind and the # verification certificate for each file cd core && cargo build --release --bin intake ./target/release/intake <workbook.xlsx> <outdir>
Per-kind plug counts are emitted as plugs.by_kind, counted over every plug rather than the capped sample, so the split between detectors is a number you can read from the output rather than one you have to take from us.
References
- Barik T, Lubick K, Smith J, Slankas J, Murphy-Hill E. FUSE: A Reproducible, Extendable, Internet-scale Corpus of Spreadsheets. Proceedings of the 12th Working Conference on Mining Software Repositories (MSR), 2015. Data: Zenodo 581678.
- Hermans F, Murphy-Hill E. Enron Spreadsheets and Emails. figshare, 2015. doi:10.6084/m9.figshare.1221767.
- Panko RR. What We Know About Spreadsheet Errors. Journal of End User Computing 10(2), 1998. Updated 2008.
Run the same audit on your own workbook. The engine that produced these figures is the one the app runs on any file you open.