Spreadsheet errors that cost millions

Spreadsheets run more of the world’s money than most people are comfortable admitting. They also fail quietly. A wrong range or a pasted cell does not throw an error. It returns a number that looks fine, flows into a decision, and only surfaces when someone reconciles it against reality. Here are five cases where that gap cost real money, and what would have caught each one.

JPMorgan and the London Whale

In 2012, JPMorgan lost more than six billion dollars on a trading position whose risk had been understated by a flawed model. The bank’s own post-mortem found the value-at-risk model ran across several spreadsheets with data copied and pasted between them by hand. One step divided by a sum where it should have divided by an average, which halved the reported volatility. The risk looked half as large as it was.

The lesson is not that Excel is dangerous. It is that a model passed between people, with manual copy-paste and a hidden arithmetic slip, needs every formula checked, not just the headline output. A precedent trace on the final risk number would have walked straight into the bad division.

Reinhart and Rogoff

A 2010 paper by two Harvard economists became one of the most cited arguments for austerity. It claimed that economic growth fell sharply once public debt passed ninety percent of GDP. In 2013 a graduate student tried to reproduce the result and could not. When he got the original spreadsheet, the AVERAGE formula at the centre of the analysis covered only fifteen of the twenty countries. Five rows had been left out of the range. The headline finding weakened once the missing rows were included.

This is a range error, the most ordinary mistake in any model. The formula was valid. It just pointed at the wrong cells. A check that flags a SUM or AVERAGE whose range stops short of the data block catches this in seconds.

Public Health England and the lost cases

During the 2020 pandemic, England briefly lost track of nearly sixteen thousand positive COVID cases. The cause was a spreadsheet. Case data was being collected into an old .xls file, a format that stops at 65,536 rows. When the data ran past that limit, the extra rows were silently dropped. Nobody saw an error, because there was none to see. The rows simply did not exist in the file.

The fix here is structural rather than a single formula, but the theme is the same: the failure was invisible inside the tool. Anyone reconciling the row count against the source feed would have spotted the shortfall immediately.

TransAlta and the cut-and-paste bid

In 2003 the Canadian power company TransAlta lost about twenty-four million dollars on a bid for transmission contracts. A copy-and-paste error misaligned the rows in the bidding spreadsheet, so the company locked in more contracts at worse prices than it intended. The chief executive called it a simple mistake that came down to cut and paste in a spreadsheet.

A paste that shifts rows out of alignment breaks the pattern of the formulas around it. A consistency scan that compares each formula against its neighbours flags the cells that no longer fit, which is precisely the signature of a misaligned paste.

Fidelity’s missing minus sign

In 1994 the Fidelity Magellan fund told shareholders to expect a large year-end distribution. The estimate came from a spreadsheet where someone transcribing a net capital loss of about 1.3 billion dollars left off the minus sign. A loss was recorded as a gain. The error flipped the projected distribution, and the fund had to withdraw the estimate publicly.

A sign error on a hand-keyed number is a hardcode problem. The value was typed in rather than carried by a formula, so nothing downstream could check it. Flagging hand-entered numbers that sit where a formula should be is the habit that catches this class of mistake.

The common thread

None of these were exotic. A wrong range, a manual paste, a typed-in number, a file limit. They are the everyday mechanics of spreadsheet work, and they stayed hidden because Excel shows results, not reasoning. The defence is the same in every case: read the formulas, not just the numbers.

You can start with the free browser tools. The circular reference and hardcode finder surfaces typed-in numbers and broken links, the inconsistent formula scanner catches misaligned pastes, and the named range checker finds references that point at deleted cells. For the full method, the financial model audit checklist walks through it step by step, and Formula Audit XL runs the whole set across every sheet at once.