What a spreadsheet rewrite actually costs

Ask what it costs to turn a spreadsheet into a web application and the conversation goes to the formulas: how many there are, how gnarly they look, whether anyone still understands the nested one in column H. That is the wrong thing to count, and this note is the measurement that says so.

108 spreadsheets published by UK government, holding 464,872 formula cells between them. Of those cells, 99.1% use functions with a direct equivalent in any web stack you would pick — sums, lookups, dates, text handling, conditionals. Translating them is typing. What costs money is the 0.5% with no faithful translation at all, and the 58.7% of filled columns where the same calculation is done more than one way down the column.

The corpus is 123 spreadsheet URLs published on GOV.UK's asset host, collected one at a time with a named user agent, after reading that host's robots.txt — and after being refused the data.gov.uk catalogue API, which disallows the path its own search redirects into. Deduplicated by content hash that is 108 distinct workbooks plus five duplicate copies, which are reported rather than dropped, because a file published twice under two names is a fact about how these sheets circulate. Another ten files could not be read at all.

The instrument reads formulas. It does not evaluate them: no cell was computed, so there is no claim anywhere below about a spreadsheet getting a wrong answer. Every figure here is about the shape of the logic, which is the thing a rewrite has to reproduce.

The 31 workbooks that do hold logic hold 464,872 formula cells — and 10,287 distinct rules. A formula filled down a column is one rule the moment you normalise its relative references: every cell in it says "the cell three to my left times the cell above me", and a rewrite implements that once. Collapsing identical shapes that way, 464,872 cells come to 10,287 rules, a ratio of 45.2 to one.

The average hides the interesting part. The median workbook is only 11.2 cells per rule; the most repetitive is 1,028.4. The largest sheet in the corpus, Materials_Facility_Combined_Waste_Returns_Spreadsheet.xlsx, holds 96,668 formula cells and 94 rules. A quote priced on its cell count would be wrong by three orders of magnitude.

Normalising a reference is where the bugs live, and both of the ones here pushed the answer the same way — towards making spreadsheets look harder than they are.

The first: a reference carrying a sheet name was not normalised. A formula reading Other!B2 alongside a local B2 had one half turned into an offset and the other left as a literal address, so the same formula filled down a thousand rows produced a thousand different shapes — a thousand rules where there is one. The second: a workbook can store a formula once and have the rest of the column point at it by index, carrying no text of their own. Those followers were being skipped, so a column filled down thousands of rows counted as a single cell. One error inflated the rule count and the other deflated the cell count, and together they made the cells-to-rules ratio meaningless.

Both are fixed, both figures above are post-fix, and the general lesson is the one worth taking: a reference normaliser has to handle the qualified form and the shared form, or it is measuring its own regex.

Of 464,872 cells, 460,904 are mechanical. 1,611 — 0.3% — use a function the instrument does not recognise, which means somebody has to read them rather than that they are hard. The 2,357 that are genuinely unrepresentable — 0.5% — break down into four kinds, and each one is a conversation with the client rather than a line of code:

No formula in the corpus reads a cell in another workbook. At the file level 21 of them carry a link to one anyway — a connection saved into the file, pointing somewhere on a drive that may not exist now.

Across the 22 workbooks with a filled column at all, there are 972 such columns and 1,261 runs of a repeated rule inside them. 571 of those columns — 58.7% — compute their values more than one way, with 1,578 extra shapes between them, across 18 workbooks.

That is not by itself a defect. A total row at the bottom of a filled column is a second shape and a correct one; two stacked tables sharing a column are two rules, not a rule with exceptions. But every one of them is a decision somebody has to make during a rewrite, and the person who can make it is the person who knows what the sheet meant — not the developer reading it. That is the line item a formula count cannot see, and it is why the estimate that matters is hours of somebody's attention, not lines of logic.

Looked at by eye the variants are exactly what you would guess. In one workbook a column of lookups wraps each in IFERROR, except for the cell that doesn't; two cells further down gained a wrapper the run above them lacks. Harmless, until the rewrite has to pick one behaviour for the column and somebody notices the blanks moved.

The figure above went through three definitions before it was honest, and the first two both overstated the case badly enough to have been worth publishing as a warning.

Version one took the most common shape in a column as the rule and called every other cell a mistake against it. On that definition most columns in the corpus carried disagreement — but a summary column holding a stack of unrelated one-off formulas has no rule at all, and whichever two of them happened to match became the rule while the rest became defects. Version two required the dominant shape to hold a real majority of a real number of cells. Better, and still mostly wrong, for the same reason: a column with one table's subtractions above another table's lookups is two rules, not one rule with twenty exceptions.

The definition that survived is about runs rather than totals. A rule is a run of consecutive cells with the same shape, long enough not to be a corner of a table. An exception is a short run sitting between two long runs of the same shape — so the rule demonstrably resumes after it, and the interruption cannot be explained as the column simply moving on to something else. By that definition there is one exception in 972 columns: 0.1%, in one workbook, HARC_DRC_programme_Improving_food_security_nutrition_protection_Budget_template_Annex_3.xlsx.

The same pass counts the things that do not appear in any brief:

All ten unreadable files are legacy binary workbooks, the pre-2007 format, which this instrument declines to parse rather than guess at. The part worth knowing is that two of them are published with a modern .xlsx name: Lighting_Tool_190314.xlsx and Model_Tester_130314.xlsx, both beginning d0cf11e0 — an OLE compound file, not a zip.

Anything in a migration that routes on the extension will take those two down the wrong path, and the error will look like a corrupt file rather than a mislabelled one. Read the first eight bytes; the name is a claim, not a measurement. The container of every file in this corpus is recorded that way, which is why the figure above can be checked without the files.

Four limits, and the third is the one that would change the headline:

In the order the answers change the price:

The instrument behind this note reads formulas and reports shapes; the figures above are re-derived from its output on every build of this site, so a number in the prose that drifts from the measurement fails the build rather than ageing quietly. That is the same discipline we bring to replacing a spreadsheet with something auditable and to a pipeline that gates its own claims.