The real cost of running a business on spreadsheets

Almost every business we meet runs part of itself in a spreadsheet, and for good reasons. It is the fastest way to answer a new question, and anyone on the team can open one. The trouble starts when the spreadsheet stops being a place to work something out and becomes the place where the business keeps its records.

How it happens

It starts with one sheet for bookings, or stock, or open quotes. It works, so it grows. A second tab appears, then a copy for the other office, then a version someone saved to their desktop before a meeting. The file that matters ends up called something like "Bookings 2026 FINAL (2)", and one person knows which formulas are safe to touch.

Each of those steps made sense on the day it was taken. What nobody notices is that the business now depends on a file with no history, no permissions and no single version everyone agrees on.

Mistakes are the normal case

Spreadsheets go wrong more often than people assume. Raymond Panko at the University of Hawaii spent years collecting audits of real business spreadsheets. In the studies he reviewed, 88 percent of the spreadsheets audited contained errors, and counted cell by cell the error rates ran from under half a percent to almost seven percent. For a large spreadsheet, he concluded, the question is how many errors it has.

A few of these mistakes are well known because of what they touched.

In October 2020, 15,841 positive COVID-19 tests were missing from England's daily figures. Public Health England had been moving the results through Excel templates in the old XLS format, which stops at about 65,000 rows, so each file could hold only around 1,400 cases and the rest were cut off.

In the first half of 2012, JPMorgan Chase lost about 5.8 billion dollars on trades later known as the London Whale. The bank's own task force found that a risk model behind them ran on Excel sheets filled in by copying and pasting from one to the next, and that one formula divided by a sum where it should have divided by an average. According to the report, that probably made the trades look about half as volatile as they were. It was one cause among several.

And in 2013 a graduate student, Thomas Herndon, found that a widely cited paper by the economists Carmen Reinhart and Kenneth Rogoff had averaged the wrong range of cells, which left five countries out of a key calculation. The cell error explained only a small part of the disagreement over their results. It became the famous part because anyone who has used a spreadsheet could see exactly how it happened.

Most businesses will never have a spreadsheet mistake in the news. They get the small ones instead, like a price in a quote that was changed last week in another tab, or a stock count that is right on one laptop and wrong on another.

The cost that does not show up

The bigger cost is time, and it is spread so thinly that nobody adds it up.

The same information gets typed more than once. A customer's details go into the quote, again into the booking sheet and a third time into the invoice. Someone spends Friday afternoon reconciling two files that should have agreed. A simple question about last month, such as how many quotes went out and how many were won, takes an hour of copying and filtering.

Then there is the risk nobody plans for. When the person who built the file is on holiday, or leaves, the business finds out how much of its operation was in one person's head.

Signs you have outgrown it

A spreadsheet is still the right tool while one person owns it and the information only flows one way. It has usually stopped being the right tool when you recognise two or more of these:

• more than one person changes the same records on the same day
• the same information is typed into two or more places
• someone keeps a copy "just in case" because the main file cannot be trusted
• a simple question about last month takes more than a few minutes to answer

What to do instead

Move the records the business runs on into one system, where each thing is entered once and every screen reads from the same place. Customers, bookings, quotes and invoices are the usual candidates. Keep spreadsheets for what they do well: exploring a question, modelling a price change, putting a quick list together for a meeting.

The system does not have to be large to be worth building. Start with the two or three records that cause the most retyping today, and make them the one place the whole team trusts.