When your spreadsheet becomes a risk: lessons from real errors

Spreadsheets are brilliant. They are flexible and cheap, and nearly everyone can use one. That is exactly why they end up running things they were never designed to run. Most of the time nothing goes wrong, until the day a single forgotten limit or mistyped cell becomes very expensive.

The short version

• In 2020, an old Excel file format contributed to nearly 16,000 coronavirus cases going unreported in England.¹
• The European Spreadsheet Risk Interest Group has collected a long list of public spreadsheet errors.²
• Spreadsheets are fine for many jobs, and even acceptable for some legal records.³
• The warning signs are about how much depends on one file, not how big it is.

What happened at Public Health England

The best-known UK example came in October 2020. The BBC reported that badly thought-out use of Excel was the reason nearly 16,000 coronavirus cases went unreported in England.¹

Public Health England had set up an automatic process to pull test data into Excel templates, so that it could be uploaded to a central system. Its developers picked an old file format, XLS. As a result, each template could handle only about 65,000 rows, rather than the million-plus that Excel is actually capable of. Because each test result created several rows, each template was limited to about 1,400 cases, and further cases were simply left off.¹

Nothing crashed, and no alarm went off. The extra data simply was not there. That is the frightening part of spreadsheet failures: they are often silent.

It is not a one-off

The European Spreadsheet Risk Interest Group, known as EuSpRIG, collects public reports of spreadsheet errors, noting that they appear in the media at a fairly consistent rate. Its collection covers the “uncontrolled use of spreadsheets”, and it gives its view on how each problem might have been avoided.²

Its examples range from a $92 million error at the Norwegian sovereign wealth fund to a UK Office for Budget Responsibility correction.² Another entry concerns the Police Service of Northern Ireland, where it reports that consequential costs could run to £50 million, with the Information Commissioner issuing a fine of £750,000.² These are large organisations with far more checking than a small business has.

Why this happens

These are common causes, and any of them could affect a small business spreadsheet:

• Mixing data and formulas. One accidental overwrite replaces a calculation with a number, and nothing tells you.
• Copies everywhere. “Rota_final_v3_NEW” and “Rota_final_v3_NEW(2)” both exist, and nobody knows which is right.
• No history. When a figure changes, you cannot see who changed it or why.
• Hidden assumptions. A rate was typed into a cell three years ago, by someone who has since left.
• No checks on what is typed in. “14/13/2026” or “two” go straight in.
• Sharing too widely. Customer data can end up attached to emails, in downloads folders and on phones.

When a spreadsheet is fine

We build software for a living, but we are not going to tell you to replace every spreadsheet. A spreadsheet is a good choice when:

• one or two people use it
• a mistake would be easy to spot and cheap to fix
• it is for thinking something through, not for running the business
• it does not hold sensitive information

Even for legal records, it can be acceptable. Acas gives a spreadsheet as an example of a reasonable way to keep holiday records.³ HMRC’s guidance also says some software can connect to existing spreadsheet records for Making Tax Digital.⁴

Warning signs it has outgrown itself

If you recognise three or more of these, it is probably time to look again:

1. Only one person fully understands it, and you would be in trouble if they left.
2. A mistake could mean wrong pay, a wrong quote or an upset customer.
3. Several people edit it, and overwrite each other’s work.
4. Someone retypes the same information from it into another system.
5. It takes noticeably long to open, or it has crashed.
6. It holds personal or financial data, and many people can see it.
7. You have ever found an error by accident, and wondered how long it had been there.

Making the one you have safer

Before replacing anything, these simple steps reduce the risk. They are our suggestions:

• Lock your formula cells, so only the input cells can be changed.
• Keep inputs separate, with rates and assumptions in one labelled place.
• Add check totals which should always agree, with a visible warning if they do not.
• Add data validation, so dates must be dates and only allowed values can be chosen.
• Use one shared file with version history, instead of emailing copies.
• Limit who can edit it, and restrict access to anything sensitive.

Moving on, when you are ready

You do not have to replace it all at once. Find the part that causes the most trouble, and start there. If you are deciding between a product and custom work, see build or buy.

Our data and automation service cleans up messy data and connects the systems you already use. Usually, the first step is simply looking at a sample of the real file and telling you what is worth fixing.