Why your spreadsheet keeps breaking — and what to use instead

Almost every small business runs on at least one spreadsheet that nobody fully trusts. It works, mostly. One person understands it. Everybody else changes numbers in the yellow cells and hopes. If that describes a file in your business, the reassuring news is that this is not a personal failing — it is the single most studied reliability problem in business computing, and the findings are consistent across three decades.

The error rate is not a rumour

Ray Panko of the University of Hawaii has spent years collecting field audits of real, in-use business spreadsheets. His summary of that work reports that nine such audits examined 163 operational spreadsheets and found errors in 84% of them on a weighted average. Narrowing to the five studies with the strongest detection methods, which looked only for serious errors across 55 spreadsheets, the figure rises to 91%.

Panko's paper for the European Spreadsheet Risks Interest Group, “What We Don't Know About Spreadsheet Errors Today,” draws three conclusions that are worth stating plainly. Errors are rare on a per-cell basis, but in a large workbook at least one wrong bottom-line figure is very likely to be present. Errors are extremely hard to detect and correct. And developers and organisations are consistently overconfident about the accuracy of their own spreadsheets.

Independent work points the same way. Stephen Powell, Kenneth Baker and Barry Lawson of the Tuck School of Business at Dartmouth audited operational spreadsheets in use at real organisations and found errors in more than half of those they examined, with financial consequences ranging from nothing at all to very large sums indeed. The European Spreadsheet Risks Interest Group maintains a long-running public list of spreadsheet failures that reached the news — the kind of incident where a single cell moved a public budget or a takeover price.

Four ways a working spreadsheet quietly rots

1. References that break when the shape changes

A formula points at a range. Somebody inserts a row, sorts a block, deletes an obsolete column, or copies the sheet into a new workbook. The formula still returns a number — it is just now summing the wrong cells, or averaging a range that stops five rows short of the data. Nothing highlights, nothing warns, and the output still looks like an output. Thomas Herndon, Michael Ash and Robert Pollin's 2014 critique in the Cambridge Journal of Economics of a widely cited study on public debt and growth is the famous example: among other problems, a formula that did not extend across all the intended rows helped produce a headline result that did not survive correction.

2. Copy-paste, done by a tired human

Manual transfer between sheets is the most common way a correct model produces a wrong number. JPMorgan Chase's own management task force report into its 2012 Chief Investment Office losses described a risk model that operated through spreadsheets requiring manual entry and copying of data between files, and identified errors in that process among the contributing failures. If a bank with a risk department can lose control of a copy-paste chain, a small business copying last month's tab and editing the dates is not doing anything unusual by getting it wrong.

3. No validation, so the file silently changes your data

Spreadsheets coerce. Text that looks like a date becomes a date; a long identifier loses its leading zero; a pasted number arrives as text and drops out of the total. The best-documented case is in genomics: Mark Ziemann, Yotam Eren and Assam El-Osta reported in Genome Biology in 2016 that gene names such as SEPT2 were being auto-converted to dates, and that around one in five papers with supplementary Excel gene lists in the journals they scanned contained such conversions. If a field with thousands of trained researchers could not stop this happening, a quoting sheet will not stop it either.

4. Version sprawl and structural limits

The moment a workbook is emailed, there are two of them. Then there is a copy on a shared drive, one in somebody's downloads folder, and one attached to a proposal from March. There is no way to tell from inside any of them which is authoritative. Older formats add hard limits on top: the European Spreadsheet Risks Interest Group's list records the case of Public Health England, where COVID-19 test results were being collated in the legacy .xls format, which caps a worksheet at 65,536 rows — and when the limit was reached, roughly sixteen thousand positive results were simply dropped rather than flagged.

Why “we check it carefully” does not solve it

The instinctive fix is more review. The evidence says review helps but does not get you close to zero: Panko's summary of inspection research reports that an individual inspecting a spreadsheet catches roughly half the errors present, which is why software engineering long ago moved to group inspection for anything important. Most small businesses do not have a second person who understands the model well enough to inspect it, and the person who built it is the least able to see their own assumptions.

The Institute of Chartered Accountants in England and Wales publishes a set of twenty principles for good spreadsheet practice, updated in 2024, grouped around planning, building, and control. They are genuinely useful — separate inputs from calculations, document assumptions, avoid embedding constants inside formulas, control versions — and they are also a decent argument against spreadsheets for certain jobs. If following twenty structured principles is the price of a reliable workbook, it is fair to ask whether a workbook is the right container at all.

When a spreadsheet is genuinely the right tool

• One-off analysis you will throw away — a quick comparison, a scenario sketch, a sanity check before a meeting.
• Exploration, where you do not yet know the shape of the calculation and the flexibility is the whole point.
• Small, flat data with no derived logic — a contact list, a stock count, a simple log.
• Anything where you personally own the file, use it once a month, and can see all of it on one screen.

When it is time to stop

• More than one person edits it, or it is emailed between people as an attachment.
• A wrong answer costs real money — quotes, invoices, payroll, tax, safety margins, stock reorder points.
• The same calculation is repeated for many jobs, customers or months.
• You cannot explain the logic without opening the file and scrolling.
• It has grown a set of instructions, a cheat sheet, or a person whose job is partly “knowing how the sheet works.”
• Somebody has ever said “don't touch that tab.”

A decision framework you can apply in five minutes

1. Write down the cost of the worst plausible wrong answer this file could produce. If it is more than a day of your revenue, it needs structure.
2. Count the people who edit it and the copies that exist. More than one of either means you have a version problem already.
3. Ask how you would notice an error. If the honest answer is “the customer would tell me”, the file has no defences.
4. Estimate how often the calculation is reused. Reuse is what justifies building something proper — a one-off never does.
5. If three or more of these point the wrong way, move that calculation into a purpose-built tool and keep the spreadsheet for exploration only.

What “purpose-built” actually buys you

The advantage of a tool over a workbook is not that the arithmetic is cleverer. It is that the arithmetic is fixed. The formula cannot be dragged over, the inputs are validated so a percentage cannot arrive as text, the assumptions are stated on the page instead of living in a cell somebody hid in column BQ, and everyone using it gets the same answer from the same inputs. You lose flexibility, which is exactly the point: for a repeated calculation with money attached, flexibility is the failure mode.

That is also the honest trade-off. A purpose-built tool is worse than a spreadsheet at anything it was not built for. Keep both, and be deliberate about which job goes where.

References

• Panko, R. "Audits of Operational Spreadsheets." University of Hawaii. https://panko.com/ssr/Audits.html
• Panko, R. (2015). "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 16th EuSpRIG Conference. https://arxiv.org/abs/1602.02601
• Powell, S. G., Baker, K. R. and Lawson, B. (2009). "Errors in Operational Spreadsheets." Tuck School of Business, Dartmouth College. https://mba.tuck.dartmouth.edu/spreadsheet/product_pubs_files/Errors.pdf
• Herndon, T., Ash, M. and Pollin, R. (2014). "Does high public debt consistently stifle economic growth? A critique of Reinhart and Rogoff." Cambridge Journal of Economics, 38(2), 257-279. https://academic.oup.com/cje/article-abstract/38/2/257/1714018
• JPMorgan Chase & Co. (2013). "Report of JPMorgan Chase & Co. Management Task Force Regarding 2012 CIO Losses." https://ypfsresourcelibrary.blob.core.windows.net/fcic/YPFS/JPMorgan%20Management%20Task%20Force%20Regarding%202012%20CIO%20Losses%201-16-13.pdf
• Ziemann, M., Eren, Y. and El-Osta, A. (2016). "Gene name errors are widespread in the scientific literature." Genome Biology, 17:177. https://pmc.ncbi.nlm.nih.gov/articles/PMC4994289/
• European Spreadsheet Risks Interest Group. "Horror Stories." https://eusprig.org/research-info/horror-stories/
• ICAEW (2024). "Twenty principles for good spreadsheet practice, 2024 edition." https://www.icaew.com/technical/technology/excel-community/20-principles-for-good-spreadsheet-practice-2024-edition