The Most Costly Spreadsheet Errors in History, and What They Teach

By the XLsheetAI Team · Updated August 7, 2026 · 11 min read

The pattern behind all eight cases

Every failure on this list shares one property: the spreadsheet kept working. No cell turned red, no dialog appeared, no formula returned #REF!. The wrong number was produced silently and travelled straight into a bid, a policy paper, a risk report or a contract. That silence is the whole problem.

Software engineers get compilers, test suites and code review. Spreadsheet authors get none of that by default. A range that stops five rows short is syntactically perfect; a pasted block shifted by one row is valid data. What follows are eight incidents traced to a specific, documented mechanism and verified against primary reports or major news coverage. Where figures are ambiguous, or where the spreadsheet contributed rather than caused, we say so.

1994: Fidelity Magellan and a missing minus sign

In late 1994 Fidelity told investors that its Magellan fund would pay a year-end distribution of roughly $4.32 a share. The distribution was cancelled. A tax accountant transcribing figures into a separate worksheet had omitted the minus sign from a $1.3 billion net capital loss, so the model treated it as a gain and the estimate moved by $2.6 billion.

The mechanism is the least sophisticated on this list and the hardest to defend against: manual re-entry of a figure from one system into another. A sign flip produces a result that is plausible in shape and catastrophically wrong in direction. CIO magazine's roundup of spreadsheet blunders documents the case, citing a letter of explanation from Fidelity president J. Gary Burkhead. One clarification: the $2.6 billion is the size of the estimate error, not a realised loss.

The lesson: any number that a human retypes from one system into another needs a reconciliation check against the source, not a second pair of eyes reading the same typed figure.

2003: TransAlta and a misaligned paste, $24 million

Canadian power generator TransAlta submitted sealed bids for New York transmission hedging contracts. When the bids were opened on 25 April 2003, the company had won contracts it did not want at prices far above the market. Rows in the bidding spreadsheet had been pasted out of alignment, attaching high bids intended for one set of transmission paths to entirely different, lower-demand routes.

The Globe and Mail reported the cost at $24 million (U.S.), roughly a tenth of projected annual profit. Chief executive Steve Snyder was unusually direct: "It was literally a cut-and-paste error in an Excel spreadsheet that we did not detect when we did our final sorting and ranking of bids prior to submission." Note where it survived: the sorting step, where an imperfect selection moves some columns and not others, and the result still looks like tidy data.

The lesson: never sort a raw range. Convert the block to a proper Table first so rows move as units, and reconcile a handful of paired values (route and price, ID and amount) after every sort or paste.

2008: Barclays, Lehman and 179 hidden rows

Three days after Lehman Brothers filed for bankruptcy, Barclays agreed to buy part of its business. A spreadsheet listing Lehman's trading contracts contained rows marked for exclusion from the deal, and those rows were hidden. Late on 18 September 2008 an associate at law firm Cleary Gottlieb Steen & Hamilton reformatted the sheet for conversion to PDF and resized the rows globally. The hidden rows reappeared and went into the filed agreement.

The ABA Journal reported that the spreadsheet held nearly 1,000 rows and more than 24,000 cells, and that 179 contracts Barclays had not intended to buy were added to the purchase. The error was found on 1 October and the firm filed a motion seeking relief from the sale order. Hidden rows are still present, still exported, still counted by most functions, and any formatting change can reveal them. They are a display setting masquerading as a filter.

The lesson: exclusion should be data, not formatting. Add an explicit "include" column, filter or query on it, and generate the outbound file from the filtered result rather than hiding what you do not want people to see.

2010: MI5, a formatting fault and 134 wrong phone numbers

The 2010 annual report of the UK Interception of Communications Commissioner, laid before Parliament in June 2011, records at paragraph 7.33 that the Security Service reported 1,061 errors that year. The first category is described plainly: "subscriber data was acquired in relation to 134 incorrect telephone numbers. These errors were caused by a formatting fault on an electronic spreadsheet which altered the last three digits of each of the telephone numbers to '000'."

This is a number-formatting failure, the same family of bug that turns gene names into dates and strips leading zeros from postcodes. A phone number is a string of digits, not a quantity, and the moment a spreadsheet treats it as numeric it becomes eligible for rounding, truncation and scientific notation. The Commissioner noted the data had no relevance to any investigation and that the material was destroyed. There is no financial figure here. The cost was 134 innocent people having their subscriber details pulled.

The lesson: identifiers are text. Force the column to Text before import, and add a length check so a 11-digit number that arrives as 8 digits fails visibly instead of quietly.

Not sure whether your formula does what you think it does? Paste it into XLsheetAI and get a plain-English explanation of exactly what it references and returns, before it reaches a report someone acts on.

2012: JPMorgan's London Whale and a division by the wrong denominator

JPMorgan's Chief Investment Office lost approximately $5.8 billion in the first half of 2012. The bank's own Management Task Force report, published on 16 January 2013, describes a new Value at Risk model that ran on spreadsheets and contained an arithmetic fault which understated how volatile the portfolio actually was.

The report's language is worth quoting because secondhand accounts garble it. On the model's construction: "the model operated through a series of Excel spreadsheets, which had to be completed manually, by a process of copying and pasting data from one spreadsheet to another." On the fault: "the spreadsheet divided by their sum instead of their average, as the modeler had intended. This error likely had the effect of muting volatility by a factor of two and of lowering the VaR."

Two details are usually reported wrongly. First, the Task Force says it is "unclear by exactly what amount" the VaR was lowered, and unclear when the error was introduced. Second, the loss figure is not a spreadsheet loss: the Task Force attributes the losses to the trading strategy, and the spreadsheet fault removed a warning light rather than causing the crash. The report also records that the model review group had called the VaR computation "error prone" and "not easily scalable," then approved it on a promise of automation that never happened.

The lesson: the danger was not the formula, it was the architecture. A model rebuilt daily by copying and pasting between sheets has no version history, no test, and no way to prove today's file matches yesterday's logic.

2013: Reinhart and Rogoff, and the range that stopped five rows short

Carmen Reinhart and Kenneth Rogoff's 2010 paper "Growth in a Time of Debt" reported that countries with public debt above 90% of GDP averaged −0.1% real growth. The figure was cited heavily in austerity debates. In April 2013, Thomas Herndon, Michael Ash and Robert Pollin of the University of Massachusetts Amherst obtained the working spreadsheet and found an averaging formula covering a range five rows short of the data.

As Matthew Yglesias wrote in Slate on 16 April 2013, summarising Mike Konczal's write-up, the summary cell read =AVERAGE(L30:L44) where it should have read =AVERAGE(L30:L49). Five countries, Australia, Austria, Belgium, Canada and Denmark, dropped out entirely.

Here is where most retellings overreach. Herndon, Ash and Pollin are explicit that the spreadsheet error alone accounts for a −0.3 percentage point distortion. The full gap between the published −0.1% and their corrected +2.2% comes from three problems: the range error, the selective exclusion of early years, and a weighting scheme that gave New Zealand's single worst year one-seventh of the entire average. Reinhart and Rogoff accepted the coding error while disputing the rest. The spreadsheet bug is the famous part, not the biggest part.

The lesson: a range that stops short never announces itself. Put a COUNT() next to every aggregate and check it against the number of records you expect, or reference a Table column so the range grows with the data.

2020: Public Health England and the 65,536-row ceiling

Between 25 September and 2 October 2020, 15,841 positive COVID-19 test results in England were recorded but never passed to contact tracers. Public Health England pulled results from commercial laboratories as CSV files and collated them into templates saved in the legacy .xls format, which stops at 65,536 rows. Rows beyond that ceiling were dropped silently.

The Register and BBC News both reported the mechanism: because each test result occupied several rows, a template filled up after roughly 1,400 cases rather than 65,000. Once full, new results were not flagged as rejected, they simply did not appear. The .xlsx format, available since 2007, holds over a million rows. The consequence is why this case matters more than any dollar figure: roughly 48,000 close contacts went un-notified during a period of exponential transmission.

The lesson: legacy formats carry legacy limits, and truncation is the most dangerous failure mode in data handling because the output still looks complete. Any automated pipeline needs a row-count assertion: records in must equal records out, checked every run.

2023: Norway's sovereign wealth fund and a benchmark built on wrong data

Norges Bank Investment Management, which runs Norway's roughly $1.5 trillion Government Pension Fund Global, disclosed an error in its 2023 annual report. In the fund's own words, it concerned "our calculation of the currency allocation in the government bond portion of the Ministry of Finance's benchmark index for the bond portfolio as a result of the wrong data being used."

NBIM reported 173 unwanted incidents in 2023 with a combined estimated impact of NOK 1.2 billion, and states that this single incident accounted for most of that total. Financial Times Alphaville covered it under the headline "The Norwegian sovereign wealth fund's $92mn Excel error." Here the sources diverge: the FT framed this as a spreadsheet error, while NBIM's own disclosure describes wrong data in a benchmark calculation and does not name the tool. The class of failure is not in dispute. A single incorrect input propagated through a calculation nothing downstream could challenge.

The lesson: inputs deserve as much scrutiny as formulas. A hard-coded date or rate buried in a calculation is invisible to formula auditing, because the formula is correct. Only the number is wrong.

All eight cases at a glance

Read down the middle column and the same handful of mechanisms recur across three decades, four countries and completely unrelated industries. The technology changed; the failure modes did not.

Research on spreadsheet error rates

Academic work on spreadsheet errors is small but consistent. The headline result, from Raymond Panko's 2015 paper for the European Spreadsheet Risks Interest Group, is that pooling six intensive inspection studies covering 85 operational spreadsheets found errors in 94% of them. Individual studies reported between 86% and 100%.

Be precise about what that measures. Most of those 85 spreadsheets were inspected by commercial auditing firms, so the sample is not random: organisations that commission an audit have reason to worry. Read it as an upper-bound signal from high-stakes financial models, not a population statistic for every workbook on a shared drive. Panko's own framing is that errors are "rare on a per-cell basis, but in large programs, at least one incorrect bottom-line value is very likely to be present." Studies measuring cell error rates reported 1.2% to 2.5%, in line with base error rates of 1% to 5% for comparable tasks such as writing program statements.

A larger, less self-selected sample comes from Felienne Hermans (Delft) and Emerson Murphy-Hill (NC State), who in 2015 analysed the spreadsheets recovered from Enron's email archive during legal discovery: over 15,000 files, 9,120 of them containing formulas. They found that 24% of the spreadsheets with at least one formula contained an Excel error, and that 76% of the corpus used only the same 15 functions.

The practical takeaway is the arithmetic of cascades. If a single formula has even a 1% chance of being wrong and a bottom-line figure depends on a hundred distinct formulas, the odds that at least one is wrong exceed 60%. Scale, not carelessness, is what makes large spreadsheets unreliable.

Six error classes, and how to catch each one

Grouping the incidents by mechanism rather than industry produces a short, useful list. Six classes cover all eight cases and, in our experience, most everyday spreadsheet failures. What unites them is that each leaves the file in a valid, calculable state.

How not to become a case study

None of these organisations lacked expertise or money. What they lacked was a check that would fail loudly. The practical defence against silent errors is a small set of habits that make a wrong number visibly wrong, applied consistently rather than heroically.

• Separate inputs, calculations and outputs. Three areas or three sheets, inputs shaded a distinct colour. If a number is typed, it belongs in the input area; nothing else should hold a literal value.
• Use Tables, not raw ranges. Ctrl+T makes ranges self-expanding, keeps rows intact through sorts, and gives readable structured references instead of A2:A5000.
• Name the ranges that matter. =Revenue-Costs is checkable at a glance; =D42-H17 is not, and nobody reviews what they cannot read. Our Excel formulas cheat sheet covers the patterns worth standardising on.
• Cross-foot every total. Sum by row and by column, confirm they agree, and reconcile the headline number against an independent source. This one habit catches misalignment, short ranges and truncation.
• Be disciplined with IFERROR. Wrapping a formula to make #N/A disappear also hides #REF! and #VALUE!, which are genuine structural failures. Prefer targeted handling such as XLOOKUP's if_not_found. See XLOOKUP vs VLOOKUP for why lookup defaults matter here.
• Version the file properly. Dated, immutable copies at minimum; a repository or workbook-diff tool for anything informing a real decision. JPMorgan's model could not prove what changed between days.
• Review the logic, not the layout. Ask a colleague to trace one number from output back to source. Reviewers who scan the printed page catch formatting, never a range that stops five rows short.
• Retire legacy .xls. The row ceiling is not theoretical, and .xlsx has been the default since 2007. Anything with hard limits belongs in a database.
• Validate at the door. Validation rules, text-typed identifier columns and a row-count assertion on every import stop bad values before they reach a formula.

Bottom line

Spreadsheets fail silently, which is why competent people at well-run institutions keep appearing in lists like this one. The specific bugs vary, but the shape is constant: a valid file, a plausible number, and no mechanism anywhere in the chain designed to say "this cannot be right." Adding that mechanism is cheap. Not having it has repeatedly cost tens of millions.

Build the formula correctly the first time. Describe what you need in plain English and XLsheetAI writes it with the right ranges and error handling, or explains an inherited formula so you can spot the range that stops short before it costs you.