When a spreadsheet changes its meaning

Organisations publish the same spreadsheet every month or quarter, and now and then its shape changes. We collected twenty real ones, three editions each, and labelled exactly what changed from one edition to the next. In a third of cases, anything reading them automatically should stop.

The question

Tools that read spreadsheets are usually tested on tidy, one-off tables. Real recurring workbooks are nothing like that. Headers span three rows, totals sit in the middle of the data, a formula becomes a typed-in number, a column is renamed, a sheet is reshaped. Beyond reading the table, a tool has to decide whether this month’s file is still the same kind of file as last month’s, and whether it’s safe to carry on. We wanted a benchmark that asks exactly that, on real files.

The corpus

There are 20 recurring families and 60 editions. Each family is one publication that recurs, with three consecutive editions kept exactly as published. They come from ONS (business survey tables), the NHS (cancer waiting times, staffing, beds, waiting lists), Ofgem (price-cap and network allowance models), Ofwat (a price-control financial model), and London traffic-appeal statistics.

We inventoried 1,075 sheets, annotated 227 critical regions and 203 critical datasets, and checked 618 values literally against the file.

Every hard case turns up at least once: several tables on one sheet (19 families), multi-row headers (all 20), merged cells that carry structure (16), totals inside the data (17), formulas mixed with constants (14), cross-sheet references (14), hidden sheets, rows or columns (12), links to other workbooks (9), mapping sheets (7), macros (2) and a data connection (1).

The originals are untouched. No workbook was opened in a spreadsheet program or re-saved, and no macro was ever run. Everything was read from the file package directly, and every original is kept with its hash.

The drift

Each of the 40 edition-to-edition pairs has a decision: would a careful automated reader carry on, carry on with a warning, or stop?

• 13 of 40: edition changes where a reader should stop
• 18 of 61: individual changes that alter what the numbers mean

Visible layout drift appeared in 19 of the 20 families. Most changes are harmless, such as a renamed sheet or a reordered column. But 18 of the 61 changes found were material: they change what the numbers mean, or whether they can be trusted. On the corpus’s 0 to 4 drift scale (read from the planted examples below: 1 for renames and reordering, up to 4 when the shape of the data changes), the changes split 20, 17, 8, 12 and 4. The outcomes split 23 carry on, 4 warn and 13 stop.

Ten planted changes

Real drift is uneven, so the corpus also has ten controlled mutations. Each takes one real workbook and makes exactly one change, recorded with the expected rating and outcome. Seven are held blind.

Two are nasty in a specific way. Replacing a formula with its current value changes nothing you can see today, and breaks the file the next time its inputs change. And a stored result that disagrees with its own formula fools any reader that trusts the cached number.

Keeping it blind

Three families (and three mutations) are for development. The other seventeen are blind: settings deny the development tools any access to their folders, so nothing can be tuned against them. A validation script checks every annotation, drift decision, licence record and hash, and passing it is part of the acceptance rules.

Limits

Nothing has been scored against it yet, so there are no results.

Public files are cleaner than private ones. Published workbooks carry less of the informal commentary and local habits that internal spreadsheets have. Only one family includes a live data connection, so that case is thin.

Three families can’t be shared. Seventeen are redistributed under the Open Government Licence. For the other three, permission wasn’t established, so only their annotations and hashes are kept, and the originals have to be downloaded again from their sources.