How often do business spreadsheets contain errors? What the research says

Somewhere in your company there is a spreadsheet everyone depends on. It might hold customer rates, equipment hours, payroll, or the monthly billing for long-term rentals. One person built it, one person really understands it, and everyone trusts it because it has always seemed to work.

Researchers have been auditing spreadsheets like that one for more than thirty years. Their findings are consistent and a little uncomfortable. This article walks through the spreadsheet error rates research in plain terms, the cases that made headlines, and what it means for a company with 5 to 50 people.

The short answer

When trained reviewers carefully inspect real business spreadsheets, they find errors in most of them.

• Most spreadsheets audited closely had at least one error. Raymond Panko of the University of Hawaii pooled 85 intensive inspections of operational spreadsheets, most done by commercial auditing firms, and errors turned up in 94% of them.¹
• The error rate per formula is small but not zero. The best-documented studies find errors in roughly 1% to 2.5% of cells or formulas.²³
• People who build spreadsheets are overconfident. In one experiment, the median developer put the chance that they had made an error at 10%. In fact, 86% had.²

The rest of this article explains where those numbers come from, and where they have limits.

What the field audits found

A field audit means someone takes spreadsheets that a business is actually using and checks them, cell by cell or close to it.

In a paper presented to the European Spreadsheet Risks Interest Group (EuSpRIG) in 2000, Panko collected seven field audits covering 367 spreadsheets. Across all of them, errors were found in 24%. But he pointed out that the older audits used methods unlikely to catch most errors. The audits from 1997 onward, which used better methods, found errors in at least 86% of the spreadsheets they examined.²

In a 2015 update, Panko narrowed the list to intensive inspections that took several days per spreadsheet: 85 spreadsheets across six studies, including commercial audits by firms such as KPMG and Coopers & Lybrand and a review of spreadsheets submitted with UK tax filings. Collectively they found errors in 94%.¹

One caution: many of these audited spreadsheets were large financial models at big organizations. That is a different animal from a 200-row rate sheet. The reason the findings still matter to a small company is the next section.

How often individual cells are wrong

A spreadsheet error rate of 94% doesn’t mean 94% of cells are wrong. It means almost every spreadsheet has at least one. The per-cell rate is much lower.

The classic estimate

Careful audits of three large spreadsheets found that 1.1%, 2.2% and 2.5% of cells contained original errors, according to Panko’s 2000 review. In laboratory experiments, where roughly a thousand participants built spreadsheets from word problems with known answers, 51% of the spreadsheets had errors, even though most were only 25 to 50 cells in size.² His 2015 update reports an average cell error rate of 3.9% across 14 lab studies with 967 participants.¹

A more conservative estimate

Not everyone agrees on the size of the problem. Stephen Powell, Kenneth Baker and Barry Lawson of Dartmouth’s Tuck School of Business pointed out that the widely repeated figure of about 5% of formulas containing errors rested on only five studies and 43 spreadsheets, some of them informal. They built a structured auditing protocol, applied it to 50 operational spreadsheets, and found errors in 0.9% to 1.8% of formula cells, depending on how strictly an error was defined. They also found that the rate varied widely from one spreadsheet to the next.³

That lower range is worth knowing. It is also not much comfort, because of how spreadsheets work.

Why small rates add up

A spelling mistake in a letter usually stays a spelling mistake. In a spreadsheet, a wrong number flows into every cell that depends on it, all the way to the total. Panko put it this way: unless the error rate is close to one error in every ten thousand cells, most large spreadsheets are likely to contain errors.²

Think of a billing sheet where each row pulls a rate, multiplies by hours, applies a discount and adds tax. One wrong reference near the top can quietly affect every invoice built from it.

Cases that made the news

The EuSpRIG keeps a long public list of spreadsheet mistakes reported in the media. A few are worth knowing because they happened to sophisticated organizations with plenty of checks.

A bank’s risk model

In 2012, JPMorgan Chase took large losses on trades by its Chief Investment Office, the so-called London Whale. The bank’s own task force later found an error in a spreadsheet-based risk model: after subtracting an old rate from a new one, the spreadsheet divided by their sum instead of their average, as the modeler had intended. The report said this likely muted volatility by a factor of two and lowered the model’s value-at-risk figure. EuSpRIG lists unreviewed logic and manual copy-and-paste steps among the causes.⁴ The spreadsheet did not cause the losses on its own, but it weakened one of the controls meant to flag them.

An economics paper

A widely cited 2010 paper by Harvard economists Carmen Reinhart and Kenneth Rogoff reported that countries with public debt above 90% of GDP averaged negative growth. In 2013, University of Massachusetts Amherst researchers Thomas Herndon, Michael Ash and Robert Pollin found a spreadsheet error that left five countries, including Canada, out of a key average, along with two other methodological problems. With all three corrected, they reported average growth of 2.2% for high-debt countries, not the published negative 0.1%.⁵

A Calgary power company

In 2003, TransAlta took a US$24 million charge after bidding on more transmission hedging contracts than it intended, at higher prices. The CEO called it “literally a cut-and-paste error in an Excel spreadsheet that we did not detect” before submitting the bids.⁶

Scientific data

Spreadsheets also change data on their own. Excel’s default settings convert some gene names into dates (SEPT2 becomes 2-Sep, for example). A 2016 study in Genome Biology screened supplementary files from 18 journals and found gene name errors in 704 of 3,597 papers that included gene lists in Excel files, or 19.6%.⁷ For a service company, the same automatic formatting can turn a part number, a unit number or a work order code into something else without anyone noticing.

What this means for a 5 to 50 person company

Your spreadsheets are smaller and simpler than a bank’s risk model. That helps. But several things work against you:

• Nobody audits them. The studies above involved trained reviewers spending days per spreadsheet. A small company’s rate sheet usually gets reviewed when a customer complains.
• One person built it. When that person leaves or goes on holiday, nobody else knows which cells are formulas and which were typed over.
• They touch money directly. Rates, hours and invoice totals go straight to customers. An error doesn’t stay in a model; it goes out on an invoice.
• Confidence is high. Panko’s work found overconfidence in every study that looked for it. “It has always worked” is exactly what the developers in those experiments believed.¹

If you are staying on spreadsheets for now

Plenty of companies will, and that is a reasonable choice for a while. A few habits reduce the risk:

1. Keep inputs (rates, hours) separate from formulas, and lock the formula cells.
2. Keep one master rate sheet rather than copies on different laptops.
3. Have a second person check the logic, not just the totals. Panko’s review found cell-by-cell inspection was the one method with solid evidence of catching errors.²
4. Format code columns, such as unit and work order numbers, as text so they aren’t converted.

When the spreadsheet becomes the thing standing between a finished job and an invoice, it is usually time to move off spreadsheets. Our comparison of Dispatch as an alternative to Excel covers what that switch involves.

Where Dispatch fits

Dispatch replaces the rate sheet and the billing spreadsheet with records that don’t depend on formulas someone has to maintain. Each client’s rates live in one place and are applied automatically when a quote or invoice is built, GST and PST are calculated by province, and invoices come straight from completed job tickets. Dispatch keeps an audit log of key actions, so changes can be traced. It is not accounting software, and your books stay where they are.

If you’d like to see how your current spreadsheet would map into it, book a demo and bring the file. We will go through it with you.

Footnotes

1. Raymond R. Panko, “What We Don’t Know About Spreadsheet Errors Today: The Facts, Why We Don’t Believe Them, and What We Need to Do,” Proceedings of the EuSpRIG 2015 Conference, 2015. https://arxiv.org/abs/1602.02601 ↩ ↩² ↩³ ↩⁴

2. Raymond R. Panko, “Spreadsheet Errors: What We Know. What We Think We Can Do,” Proceedings of the EuSpRIG 2000 Symposium, 2000. https://arxiv.org/abs/0802.3457 ↩ ↩² ↩³ ↩⁴ ↩⁵ ↩⁶

3. Stephen G. Powell, Kenneth R. Baker and Barry Lawson, “Errors in Operational Spreadsheets,” Journal of Organizational and End User Computing 21(3), 2009. https://mba.tuck.dartmouth.edu/spreadsheet/product_pubs_files/Errors.pdf ↩ ↩²

4. European Spreadsheet Risks Interest Group, “Horror Stories” (entry POB1301, quoting the JPMorgan Chase Management Task Force report of January 2013), accessed 2026. https://eusprig.org/research-info/horror-stories/ ↩

5. Thomas Herndon, Michael Ash and Robert Pollin, “Does High Public Debt Consistently Stifle Economic Growth? A Critique of Reinhart and Rogoff,” Political Economy Research Institute Working Paper, April 2013. https://peri.umass.edu/publication/item/526-does-high-public-debt-consistently-stifle-economic-growth-a-critique-of-reinhart-and-rogoff ↩

6. Drew Cullen, “Excel snafu costs firm $24m,” The Register, June 19, 2003. https://www.theregister.com/2003/06/19/excel_snafu_costs_firm_24m/ ↩

7. Mark Ziemann, Yotam Eren and Assam El-Osta, “Gene name errors are widespread in the scientific literature,” Genome Biology 17:177, 2016. https://doi.org/10.1186/s13059-016-1044-7 ↩