Hoverview

data quality · M&E · provenance · spreadsheets · systems

Spreadsheets are not the problem. Copies are

Why the standard advice to move off spreadsheets misdiagnoses the failure, and what the actual failure costs.

Every M&E system procurement starts from the same premise: the organisation is running on spreadsheets, spreadsheets are error-prone, therefore the organisation needs a system. The premise is half right, and the half that is wrong is the half that determines whether the new system helps.

Spreadsheets really are error-prone, and the research is not close. Raymond Panko's review of thirteen field audits conducted between 1995 and 2004 found that 94 per cent of the 88 spreadsheets studied contained at least one error, with an average cell error rate of 5.2 per cent across the 43 that were measured at cell level. A later study by Powell examined fifty operational spreadsheets from a bank, a college and two consultancies, and found a much lower rate: 1.79 per cent across 270,722 formulas, dropping to 0.87 per cent when only outright wrong results were counted. Panko's summary of the disagreement is the sentence to keep: the issue is not whether there is an error but how many there are and how serious they are.

So the error rate is real, it is contested at the margin, and it is not the thing that destroys programme data. What destroys programme data is that the file gets copied, the copy gets edited, and eight months later nobody can say which of the four versions on the shared drive is the one the annual report was built from.

The copy has no identity

A spreadsheet is a file. Files are copied, and a copy is indistinguishable from its original except by filename and timestamp, both of which are unreliable and both of which a person can change.

This matters because the copy immediately begins to diverge. Someone adds a column. Someone else, working from the version they downloaded on Tuesday, corrects a figure. A third person emails their version to the cluster coordinator. Each of those actions is reasonable and each produces a file that is now the only place a particular correction exists.

There is a published humanitarian dataset that shows this cleanly. The OCHA inter-cluster 3W for Burkina Faso covering July and August 2024 contains a sheet called MATRICE with 7,029 rows, and a second sheet called MATRICE2 with the same twenty-eight columns and 12,626 rows. Same structure, same file, nearly twice the data in the second. The first carries the HXL tag row that machine readers use to identify humanitarian columns, so any automated pipeline reads the smaller sheet and never sees the larger one. Which of the two is current is not recorded anywhere in the file.

That is not a spreadsheet error in Panko's sense. Every formula in both sheets may be correct. It is a failure of identity: two things that claim to be the same object, with no statement of which one is.

The consequence lands downstream. A figure taken from MATRICE and a figure taken from MATRICE2 will differ, both will be defensible, and reconciling them requires a conversation with whoever built the file. If that person has moved on, the reconciliation is guesswork.

What Panko's own data says about review

There is a finding buried in the spreadsheet error literature that argues against the standard remedy, and it is worth pulling out.

Panko and Halverson's 1997 experiment gave the same modelling task to individuals and to groups. Individuals working alone produced spreadsheets with errors 79 per cent of the time. Groups of two, 78 per cent. Groups of four, 64 per cent. The cell error rate fell from 5.6 per cent to 3.8 to 1.9 across the three conditions.

More eyes on the same object catches more errors. That is unsurprising and it is exactly what copies prevent. Four people looking at four copies is not four people looking at one spreadsheet, it is four separate one-person reviews of four different objects, which by Panko's own numbers is the worst configuration available.

The organisation that responds to its spreadsheet problem by giving everyone a copy so they can work without stepping on each other has made the error rate worse while feeling like it improved coordination.

The copy that became the record

The most expensive published instance of this is the Public Health England case in October 2020, and it is usually told as a story about Excel's row limit. That is the surface.

Testing laboratories sent daily results to PHE as CSV files, which have no size limit. Those files were then loaded into Excel and appended to a main database held in the old .xls format, which stops at 65,536 rows. Once the limit was reached the additional rows were silently dropped. 15,841 positive tests were left off the official figures between 25 September and 2 October, and roughly 48,000 contacts were never traced during the window when isolation would still have helped.

The row limit is the mechanism. The failure is that a lossy copy became the record. The laboratory's CSV was complete and correct throughout. The Excel file derived from it was incomplete, and because the Excel file was what the reporting pipeline read, nobody compared the two. There was no step in the process that asked whether the copy still matched the source, because in a copy-based workflow that question has no natural owner.

Every M&E pipeline that exports to Excel for reporting has the same shape. The export is a copy. If the copy is edited after export, which it always is, the two objects diverge and the divergence is invisible unless somebody deliberately looks.

Why "move off spreadsheets" is bad advice

The standard remedy fails for a reason worth stating plainly: spreadsheets are the only tool in most organisations where the person who understands the data can change it without filing a ticket.

That property is enormously valuable and every replacement system erodes it. A programme officer who spots that a district name is misspelled can fix it in a spreadsheet in four seconds. In a system with a data governance workflow, the same correction is a request, a queue, and a fortnight. Faced with that, the officer exports to Excel, fixes it there, and now the authoritative figure lives in a file the system does not know about. The system has not eliminated the copy. It has guaranteed one.

The other reason is that a spreadsheet is legible. When a figure looks wrong you can open the file and follow the formula. When a figure in a database looks wrong you file a support request, and the answer arrives from someone who can read the query log. Both systems have errors; only one of them lets the person who noticed do anything about it.

So the advice to move off spreadsheets is usually a recommendation to trade a visible failure mode for an invisible one, made by someone who will not be the person reconciling figures in eighteen months.

What actually helps

The problem to solve is not the file format. It is that copies are indistinguishable from originals and carry no statement of their own currency.

Three things address that, in rough order of how much they cost.

Name the authoritative copy and write down where it lives. Not a convention held in one person's head. A line in the project documentation saying that the figures of record for this programme are in this file at this path, and that anything else is a working copy. This is nearly free and it resolves the Burkina Faso case entirely: a note saying which sheet is current would have cost thirty seconds.

Make exports carry their own provenance. An exported file should say what it was exported from, when, and against which framework version. A reader who opens a file dated eight months ago and sees that stamped at the top knows to check. A reader who opens the same file with no stamp assumes it is current, because there is nothing telling them otherwise.

Stop treating the export as a one-way door. If the workflow is export, edit in Excel, and email the result, then the edited file is where the truth now lives and the system has become a historical archive. A re-ingest path that matches rows by identifier and shows what changed is the difference between a spreadsheet being a working surface and a spreadsheet being a fork.

None of that requires abandoning spreadsheets, which is fortunate, because the organisations most affected by this are the ones least able to fund a migration.

Where this stops

The honest limit is that identity discipline does not survive contact with a deadline.

Everything above assumes somebody has the time and the standing to say which file is authoritative and to hold the line when a colleague emails a modified version at eleven at night before a submission. That person exists in some organisations and not in most, and where they do not exist, no amount of provenance stamping helps, because a provenance stamp is only useful to a reader who has been given permission to act on it.

There is also a version of this argument that goes too far. A single authoritative file with no copies is a single point of failure, and the reason people make copies is not always carelessness. Sometimes it is because the shared drive is unreliable, or the connection drops, or the person is going to a field site with no network and needs the data offline. Those are real constraints and the answer to them is genuinely a copy.

What nobody has measured, as far as the published literature goes, is how much of the reconciliation effort in a typical reporting cycle is spent resolving copy divergence rather than correcting actual errors. Panko's field audits counted errors inside spreadsheets. The equivalent audit, counting the hours spent working out which of several files is current, has not been run, and until it is, the argument that copies cost more than cell errors rests on the shape of the problem rather than on a number.

Sources

Spreadsheet error rates

  • Error Estimation in Large Spreadsheets using Bayesian Statistics (2009). Source for Panko's review of thirteen field audits, the 94 per cent of 88 spreadsheets containing at least one error, the 5.2 per cent average cell error rate, Powell's lower figures of 1.79 and 0.87 per cent over 270,722 formulas, and Panko's summary that the question is how many errors and how serious rather than whether any exist
  • What We Know About Spreadsheet Errors, Raymond Panko, University of Hawaii. Source for the Panko and Halverson 1997 group comparison: 79 per cent of individuals, 78 per cent of pairs and 64 per cent of groups of four produced spreadsheets containing errors, with cell error rates of 5.6, 3.8 and 1.9 per cent respectively

The Public Health England case

Operational data

  • OCHA inter-cluster 3W, Burkina Faso, July to August 2024. Published humanitarian coordination dataset. The workbook contains two sheets holding the same twenty-eight-column matrix at different row counts, 7,029 and 12,626, with the HXL tag row present only on the smaller. Nothing in the file records which is current