The finding nobody likes to quote
For three decades, researchers have been quietly auditing real spreadsheets — not toy models, but the operational files companies actually run on — and finding errors in most of them. The most frequently cited summary comes from Ray Panko, whose review of the field-audit literature is usually quoted at around 88% of spreadsheets containing at least one error. Individual studies land anywhere from roughly 86% to 94%, depending on how the audit was conducted and how strictly an "error" is defined.
That number deserves a caveat, and honest practitioners give it one: some of the underlying studies have small samples, the definition of error varies between them, and the figure has been repeated so often that it has taken on more authority than the evidence strictly supports. Treat it as directional rather than precise.
But the direction is the point. Even the conservative reading says that errors in working models are normal rather than exceptional. That reframes the problem entirely. If errors were rare, the sensible response would be care — check your work, be diligent, hire good analysts. Since errors are common, and since they are common among careful, diligent, good analysts, the sensible response is structural: build models where the most likely mistakes are impossible, or at least visible.
The uncomfortable implication: if your process assumes a model is correct because nobody has found a problem with it, you are not relying on the model's quality. You are relying on the fact that nobody has looked hard enough yet.
Four failures that reached the front page
Most model errors are caught internally, cost a few hours, and are never spoken of again. A few are not caught, and the public record of those is instructive — not because the people involved were careless, but because they largely were not.
JPMorgan's Chief Investment Office, 2012
The trading losses widely known as the "London Whale" episode cost the bank roughly $6.2 billion. JPMorgan's own internal Task Force report, published in January 2013, is unusually candid about the model machinery involved: a revised Value-at-Risk model operated through a series of Excel spreadsheets that had to be completed by manually copying and pasting data from one sheet to another. The report identified a specific formula problem in which a figure was divided by the sum of two rates rather than their average — an error that understated volatility.
The spreadsheets did not cause the trading losses. But the risk model that was supposed to flag the size of the position failed to do so, and the manual copy-paste architecture underneath it was part of why.
"Growth in a Time of Debt," 2010–2013
Carmen Reinhart and Kenneth Rogoff's widely cited paper on public debt and economic growth was, for several years, one of the most influential pieces of economics in policy circles. In 2013, researchers at the University of Massachusetts Amherst — Thomas Herndon, Michael Ash, and Robert Pollin — obtained the original spreadsheet and found that an averaging formula had been applied to a range that omitted five countries from the calculation.
There is legitimate debate about how much that particular error changed the paper's conclusions, and the authors acknowledged the coding mistake while disputing that it undermined their central finding. What is not in dispute is the mechanism: a formula whose range was off by a handful of rows, in a spreadsheet that shaped an international policy argument, sat undetected for three years because nobody outside had the file.
TransAlta, 2003
The Canadian power producer disclosed a loss of roughly C$24 million after submitting bids for transmission hedging contracts at incorrect prices. The company's chief executive described the cause publicly and plainly as a simple cut-and-paste error — rows misaligned in a spreadsheet, so bids were attached to the wrong contracts.
Public Health England, 2020
During COVID-19 case reporting, roughly 15,800 positive test results went unreported for several days. The cause was a legacy file format: results were being collated in an older Excel format with a hard limit of 65,536 rows, and once a file hit that ceiling, additional cases were silently dropped rather than raising an error.
This one is worth sitting with, because it is the purest example of the category. Nobody made a mistake in the ordinary sense. A tool hit a structural limit, failed quietly, and the failure mode was invisible until the consequences were downstream.
The pattern across all four: none was caused by an analyst who did not understand finance. Each was a structural weakness — manual transfer between files, a range that did not match its intent, misaligned rows, a silent limit — that ordinary care was never going to catch.
The three classic error types
Spreadsheet error research generally sorts mistakes into three buckets. The taxonomy is useful because each type needs a different defense.
Mechanical errors
Slips of the hand and eye: a mistyped number, a formula dragged one row short of the range, a reference pointing at the cell above the one you meant. These are the most common type by a wide margin, and mercifully the easiest to catch — they usually produce a result that is visibly wrong, and consistency checks across a row will surface them.
Logic errors
Reasoning mistakes rendered flawlessly. The formula does exactly what it says; what it says is wrong. Summing a series of margin percentages instead of averaging them. Double-counting a cash flow that appears in two schedules. Applying a growth rate to a figure that already includes growth. Logic errors are rarer but far more dangerous, because the model looks completely normal — the numbers are plausible, the formatting is clean, and nothing raises a flag.
Omission errors
Something that should be in the model simply is not: a cost line nobody thought of, a working-capital effect, a tax that applies in one jurisdiction. Omission errors are the hardest of all to detect, because there is nothing to find. No cell is wrong. Reviewing the model more carefully will not reveal them; only comparing the model against reality — or against someone else's independent build — will.
| Error type | Frequency | Detectability | What actually catches it |
|---|---|---|---|
| Mechanical | High | Good | Formula consistency checks, range auditing, extreme-value tests |
| Logic | Medium | Poor | Independent review by someone who did not build it |
| Omission | Medium | Very poor | Checklists, benchmarking against actuals or a second build |
| Version | High in scenario work | Very poor | Structural — eliminate duplicate copies entirely |
The fourth type: version error
The classic taxonomy was developed for single spreadsheets, and it misses the failure mode that dominates modern scenario-heavy work. Call it version error: the model is right, the arithmetic is right, and the answer is still wrong because you were looking at the wrong copy.
It arrives through a completely reasonable sequence. You build a base case. You need a downside, so you duplicate the sheet and adjust three assumptions. You need a management case, so you duplicate again. Two weeks later you find a mistake in the revenue build and fix it — in the base case, because that is the file you had open. The other two copies still carry the bug.
Now the three cases differ in two ways: the three assumptions you changed on purpose, and one revenue-build correction you applied by accident to only one of them. Every individual file is internally consistent. The comparison between them — which is the entire reason the scenarios exist — is quietly meaningless. This is version drift, and it is the most common serious error in scenario modeling precisely because no single file is broken.
It is also the error type that ordinary defenses handle worst. Cell protection does not help; the cells are fine. A model audit does not help unless the auditor happens to diff all three files against each other line by line, which nobody does. Peer review does not help, because the reviewer looks at one model at a time.
Why the standard defenses only half work
The conventional model-integrity toolkit is genuinely useful, and every serious modeler should use it. It is worth being precise about what each technique does and does not cover.
| Defense | Catches well | Misses |
|---|---|---|
| Input/calc/output separation | Hardcodes hiding in formulas; unclear assumption ownership | Logic and omission errors entirely |
| Color conventions (blue inputs, black formulas) | Someone typing over a formula | Anything the convention was not applied to |
| Cell protection | Accidental overwrites | Wrong-but-permitted inputs; version drift |
| Check rows (balance ticks, sum-to-zero) | Mechanical errors that break an identity | Errors that keep the identity intact |
| Peer review | Logic errors — the single best defense here | Omissions both people share; cross-file drift |
| Formal model audit | Most single-file errors, thoroughly | Expensive; a point-in-time snapshot that ages immediately |
Read down the "misses" column and a pattern appears. Every conventional defense operates within one file at one moment. None of them addresses the two failure modes that matter most in scenario work: drift between copies, and the absence of any record of what changed when.
Building a model that resists error
Structural habits beat vigilance, because vigilance degrades under deadline and structure does not. The following are worth treating as non-negotiable.
- One assumption, one cell. Every input exists in exactly one place and is referenced everywhere else. If a number appears twice, it will eventually appear as two different numbers.
- No hardcodes inside formulas. A literal number buried in a formula is an assumption nobody can find, review, or sensitize. If it can change, it belongs in the input block.
- Consistent formulas across a range. Every cell in a row should be the same formula. An inconsistent cell in a uniform range is one of the most reliable error signatures there is, and it is easy to scan for.
- Test the extremes. Set a key driver to zero, then to something absurd. If the outputs do not move the way basic arithmetic says they should, the calculation chain is not what you think it is.
- Have someone else walk the logic. The builder's eye reliably skips its own assumptions. Independent review is the only real defense against logic errors, and it is worth the hour.
- Keep an audit trail. "What changed since the version we showed the board?" is a question that arrives eventually. A model that cannot answer it forces you to reconstruct history from saved files and memory.
- Stop duplicating. Every copy of a model is a future divergence. This is the one that most workflows get wrong, and it is the one with a real structural fix.
The structural fix for version error
Points one through six are craft, and good modelers already practice most of them. Point seven is different, because in a conventional spreadsheet it is close to unachievable — the tool gives you no way to have several scenarios without several copies.
That is the specific gap branching closes. Instead of duplicating a sheet per scenario, each scenario becomes a branch of one live model: it inherits every cell from its parent and overrides only the handful that genuinely differ. Fix the revenue build once, on the trunk, and every branch that has not deliberately overridden it receives the fix immediately. The drift described earlier cannot occur, because the shared logic is not duplicated — it exists once.
The second half of the fix is being able to see difference directly. When any two scenarios can be compared cell by cell, with every differing output traced back to the assumption driving it, the question "why do these two cases disagree?" stops being an afternoon of detective work and becomes a click. That is also the fastest error-detection tool most modelers have never had: an unexpected difference in a diff is an error announcing itself.
Combine that with a complete change history — every edit recorded, any past state recoverable — and the two failure modes that conventional defenses miss are both structurally closed rather than merely watched for.
Version drift, structurally eliminated
TreBranch is Git for spreadsheets. Every scenario is a branch that inherits from its parent and overrides only what genuinely differs, so a fix on the trunk reaches every case automatically. Compare any two branches to see every difference traced to its root assumption, and scrub back through a complete history of every change ever made. The errors that come from maintaining five copies of one model cannot happen when there is only one model.
Try Free — Microsoft Store →7-day free trial. Buy once, own it forever. 100% offline — your models never leave your computer.
Frequently Asked Questions
What percentage of spreadsheets contain errors?
Field audits of real, in-use spreadsheets have repeatedly found errors in the large majority of them. The most frequently cited summary comes from Ray Panko's research review, often quoted as roughly 88%, with individual studies landing anywhere from about 86% to 94% depending on the audit method and how strictly an error is defined. The precise number is debated and the sample sizes in some underlying studies are small, so it is better treated as a directional finding than a hard statistic. The practical takeaway is not the exact figure but the shape of it: errors in operational spreadsheets are normal, not exceptional, and any process that assumes a model is correct because nobody has found a problem yet is relying on luck.
What are the most common types of financial model errors?
Spreadsheet error research generally splits mistakes into three categories. Mechanical errors are slips: a mistyped number, a formula dragged one row short, a wrong cell reference. Logic errors are reasoning mistakes rendered correctly, such as using a sum where an average belongs, or double-counting a cash flow. Omission errors leave something out entirely, like a cost line that was never modeled. Mechanical errors are the most common but also the easiest to catch; logic and omission errors are rarer but far more dangerous because the model still looks and behaves normally. In scenario-heavy modeling there is a fourth practical category, version error, where a correct model is used in the wrong or outdated copy.
What was the JPMorgan London Whale spreadsheet error?
In 2012 JPMorgan Chase disclosed trading losses of roughly $6.2 billion in its Chief Investment Office. The bank's own internal Task Force report, published in January 2013, described a revised Value-at-Risk model that operated through a series of Excel spreadsheets requiring data to be copied and pasted manually from one to another. The report identified a specific formula problem in which a figure was divided by the sum of two rates rather than their average, which understated volatility. The spreadsheet issues did not cause the trading losses on their own, but they contributed to a risk model that failed to signal how much exposure the desk had accumulated.
How do you audit a financial model for errors?
A practical model audit works in layers. Start with structure: confirm that inputs, calculations, and outputs are separated, and that every assumption lives in exactly one cell. Then trace precedents and dependents on the headline outputs to make sure the calculation chain is what you think it is. Check for hardcoded numbers buried inside formulas, since those are assumptions hiding where nobody will find them. Verify that formulas are consistent across each row or column, as an inconsistent cell in an otherwise uniform range is a classic error signature. Run sanity checks at the extremes by setting key drivers to zero or to implausibly large values and confirming the outputs move the way basic arithmetic says they should. Finally, have someone who did not build the model walk the logic, because the builder's eye reliably skips over their own assumptions.
What is version drift in financial models?
Version drift is what happens when a model is duplicated into multiple scenario copies and those copies gradually stop matching. A fix or a corrected assumption gets applied to one copy but not the others, so over weeks the base case, the upside case, and the downside case silently develop different underlying logic. The result is that comparing them no longer isolates the strategic difference you meant to test, because it also includes accumulated maintenance differences nobody tracked. Version drift is particularly dangerous because each individual file can be internally correct while the comparison between them is meaningless.
Do spreadsheet errors actually cost companies money?
Yes, and several cases are publicly documented. In 2003 the Canadian power company TransAlta disclosed a loss of roughly 24 million Canadian dollars after a cut-and-paste error caused it to submit bids at the wrong prices, which the chief executive publicly described as a simple cut-and-paste mistake. In 1994 Fidelity's Magellan fund had to withdraw a projected year-end distribution after a missing minus sign turned a $1.3 billion net capital loss into a gain, a swing of $2.6 billion in the estimate. During the 2008 Lehman Brothers acquisition, a reformatted spreadsheet caused hidden rows to become visible, and Barclays ended up including roughly 179 contracts in its bid that it had not intended to purchase.
How can you prevent copy-paste errors in financial models?
The reliable prevention is structural rather than behavioral: reduce the number of places where the same information has to exist. Every assumption should live in exactly one cell and be referenced everywhere else rather than retyped. Manual copy-paste steps between sheets or workbooks should be replaced with direct references or an automated process, since any recurring manual transfer is a scheduled opportunity for error. For scenario work specifically, avoid duplicating whole sheets per case. A branching model where each scenario inherits from a shared parent and overrides only the cells that genuinely differ eliminates the duplication that copy-paste errors depend on, because there is only one copy of any shared logic.