Every failure in this article was built by someone competent. A quantitative modeler at JPMorgan. Government developers at Public Health England. Two Harvard economists whose paper shaped austerity policy across three continents. None of them lacked the skill to use a spreadsheet correctly, and all of them shipped a number that was wrong by millions or billions. The pattern underneath is worth more than the anecdotes, so this guide covers what actually broke in each case and which control would have caught it.

Key Takeaways

• The failure mode is silence, not difficulty. A broken spreadsheet does not crash or throw an error. It returns a confident, plausible number, which is why these errors survive review and reach publication.

• Scale offers no protection. JPMorgan, MI5, Public Health England and a $1.5 trillion sovereign wealth fund all shipped spreadsheet errors. Sophistication of the institution is unrelated to the fragility of the file.

• Most of these are input failures, not judgment failures. A missing minus sign, an extra zero, a column sorted on its own. The hidden cost of manual controls is that review processes check arithmetic rather than the completeness of the population.

• The fix is continuous verification, not better discipline. Controls that run without anyone remembering to run them catch what a month-end review is structurally unable to see.

Why are manual spreadsheets inherently vulnerable?

Spreadsheets fail differently from every other system in the finance stack. An ERP that receives a malformed journal rejects it. A database with a broken constraint refuses to write. A spreadsheet given a wrong instruction executes it perfectly and displays the result in the same font as everything else.

That is the whole problem. There is no error state to notice.

Oracle's analysis of spreadsheet risk makes the structural point that files intended for individual analysis end up carrying enterprise reporting, and the financial modeling consultancy Operis catalogues eight recurring failure classes drawn from reviewing models built by professionals. The categories repeat because the tool has no opinion about which numbers matter.

The economics of that gap are worth stating plainly. Allfirst, the US arm of Allied Irish Banks, would not pay $10,000 for a direct Reuters data feed into its risk control function. It had a trader download the feed into a spreadsheet instead, and he then substituted links to a private sheet he controlled. The manipulated rates concealed close to $700 million in losses. Reviewing the case for EuSpRIG, Ray Butler drew the conclusion that generalizes past this article, which is that one error in a spreadsheet will subvert all the controls in all the systems feeding into it.

A second structural weakness compounds the first. Spreadsheets have no concept of a population. A general ledger knows how many accounts exist and can tell you when one is missing. A spreadsheet knows only the cells it was pointed at, so a formula that covers 15 rows of a 20 row dataset is not incomplete from the file's perspective. It is finished.

1. Copy-and-paste errors: JPMorgan's $6.2 billion London Whale

Moving data between sheets by hand carries two risks at once. The paste can land in the wrong place, and the act of pasting can overwrite a formula that was doing real work.

JPMorgan's Chief Investment Office lost at least $6.2 billion in 2012 in the episode known as the London Whale. The bank's own Management Task Force report described a Value-at-Risk model that "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."

The specific defect is more instructive than the headline. One cell in that chain divided two rates by their sum rather than their average, which understated volatility by roughly half. The copy-and-paste process did not itself create the error, but it built an environment where a single wrong operator could sit inside a risk model for months with no validation step capable of finding it. The model told the desk its position was safer than it was, and the desk believed the model.

2. What happens when a file hits its row limit? Public Health England

File format limits are invisible until the moment data starts disappearing, and nothing announces the moment.

Public Health England lost 15,841 positive COVID-19 test results over eight days in September and October 2020, an average of 1,980 cases a day. Testing laboratories had filed their results correctly as CSV files. PHE's own automated process then loaded those results into Excel templates saved in the legacy XLS format, which caps out at roughly 65,000 rows. Because each test result consumed several rows, each template could hold only about 1,400 cases. Once a template was filled, further cases were simply left off.

Nobody received an error. The file did what XLS files do when they reach their limit.

The detail that matters for finance teams is where the failure entered. The upstream data was clean and the downstream system was capable. The break happened in the manual conversion layer between them, which is exactly where most finance teams put their spreadsheets.

3. The missing minus sign: Fidelity's Magellan Fund

Sign errors are the cheapest possible mistake to make and among the most expensive to discover late, because a sign flip converts a loss into a gain of identical magnitude and every downstream total still reconciles.

Fidelity's Magellan Fund told shareholders in November 1994 to expect a distribution of $4.32 per share. The estimate was withdrawn before year end. A tax accountant transcribing data into a new spreadsheet had omitted the minus sign from a $1.3 billion net capital loss, which recorded it as a gain, as the Washington Post reported at the time. The swing between the two readings was $2.6 billion, and the fund had already published a distribution estimate built on the wrong one.

No formula was broken. The arithmetic was flawless. A reviewer checking the calculation would have confirmed it, because the calculation was correct and the input was not.

4. Hidden rows and tabs: Boeing's 36,000 exposed records

Hiding a row or a tab changes what a reader sees. It does not change what the file contains, and it does not travel with the file as a restriction.

A Boeing employee emailed a spreadsheet to his spouse in November 2016 so she could use it as a formatting template. Hidden columns in that file carried the names, Social Security numbers and dates of birth of roughly 36,000 colleagues. Nothing on the visible sheet signalled that it held anything beyond the layout he meant to share.

The same mechanism reaches financial reporting. AstraZeneca was forced to reiterate its mid-term financial forecasts in January 2012 after confidential information embedded in a distributed template became accessible to recipients. The material had been hidden from view rather than removed. The European Spreadsheet Risks Interest Group records the incident alongside a related pattern, including a case where an employee assumed that filling a cell background in black would conceal black text.

The finance exposure here is rarely the headline forecast. It is the supporting tab. Reconciliation files sent to auditors, working papers shared with a lender, and board packs distributed as attachments routinely carry hidden columns holding customer names, salary lines, or the prior version of a number that was subsequently revised.

5. Excess zeros: Kodak's $11 million severance error

Manual numeric entry has an error rate that does not go to zero with training, and orders of magnitude are the most costly place for it to land.

Eastman Kodak disclosed an $11 million severance error in November 2005. A company spokesman explained the cause without ornament, stating that "there were too many zeros added to the employee's accrued severance." The record is held in the EuSpRIG horror stories database, drawn from MarketWatch's contemporaneous reporting.

The same failure class reaches the close in a less dramatic form. Georgia Gulf understated its provision for income taxes by $36.4 million after what the company described as a manual input error to a spreadsheet used in its tax calculations. No internal control caught it. The error surfaced in 2010 only because the company engaged a different firm of tax professionals to prepare its federal return, and that fresh review re-examined the treatment.

An extra zero is difficult to catch by eye precisely because it is not a wrong-looking number. It is a right-looking number of the wrong size, and it passes any check that confirms the digits rather than the magnitude.

6. Why does partial sorting scramble data? MI5's wrong numbers

Sorting one column without extending the selection breaks the row-level relationship between every field in the dataset. Names stay put while the values beside them move, and the resulting file looks completely normal.

The UK Security Service was recorded as having monitored incorrect telephone numbers after a spreadsheet cell formatting fault corrupted the numbers being processed. The EuSpRIG database catalogues the case under the cause "wrong cell format" in its record of interception errors reported in 2011, and a GRC review of spreadsheet compliance failures records the same surveillance error independently.

For finance, the equivalent damage is quieter but structurally identical. Sort an aging schedule by balance without including the customer column and every balance is now attributed to the wrong account. Each individual figure remains correct. Every relationship between them is wrong, and the total still ties, which removes the one check most teams rely on.

7. Version control drift: RedEnvelope's 25% share fall

Passing files between people creates a reconciliation problem that the files themselves cannot describe. There is no authoritative copy, only the most recent one somebody happened to send.

RedEnvelope's shares fell more than 25% in March 2005 after the online retailer sharply cut its fourth quarter outlook and its chief financial officer resigned. A company spokeswoman attributed the budgeting error to a single number that had been mis-recorded in one cell of a spreadsheet, which then distorted the entire cost forecast.

The single-cell origin is the point rather than a mitigating detail. Budgeting specialists name this pattern the Franken-Budget, where no copy is authoritative and every reviewer is working from a different truth. One incorrect entry reached a published forecast because no independent copy existed to disagree with it. In a versioned system the discrepancy surfaces as a conflict between two states. In a file passed by email it surfaces as a guidance revision and a share price. The absence of a controlled source of truth is what converts a typo into a disclosure event, which is the same reason spreadsheets that stay synced to the ledger behave differently from spreadsheets that are emailed.

8. Date formatting: Norway's $92 million typo

Dates are the most aggressively reformatted data type in any spreadsheet, and any calculation indexed to a period will follow a wrong date without complaint.

Norway's sovereign wealth fund, which manages approximately $1.5 trillion, disclosed a loss of NKr980 million, roughly $92 million, arising from an incorrect date entered into the calculation of its mandated benchmark. The error was reported in February 2024 and is catalogued in the EuSpRIG database.

Scale is the lesson. The institution runs one of the most scrutinized investment mandates in the world, with resourcing and oversight far beyond a typical corporate finance function, and a single misdated cell still produced a nine figure loss. Process maturity did not prevent it because the failure occurred below the level at which the process operates.

9. Can a formula nobody re-reads invalidate a model? Reinhart and Rogoff

Nested formulas and macros accumulate logic that nobody re-reads. The output remains plausible, so the error propagates into every conclusion drawn from it.

The 2010 Reinhart and Rogoff paper on public debt and economic growth was cited widely in support of austerity policy before a University of Massachusetts Amherst graduate student reproduced it and found an Excel formula that averaged across an incomplete range. The formula covered rows 30 to 44 where it should have covered rows 30 to 49, omitting Denmark, Canada, Belgium, Austria and Australia from the calculation. Correcting the range moved average growth in the high debt category from -0.1% to 0.2%, reversing the sign of the paper's headline finding.

Five missing rows in one average changed an international policy argument. The spreadsheet had been doing exactly what it was told for three years.

How do common spreadsheet pitfalls compare to system controls?

How does a continuous close eliminate spreadsheet risk?

The shared property of all nine failures is timing. In each case the error was created early, remained invisible through every intermediate step, and surfaced only when an external party or a downstream consequence forced the question. Monthly batch processing guarantees that gap, because a control that runs once a period cannot detect anything during the period.

Continuous control changes when detection happens rather than how hard people look. Transaction data flows from source systems on a schedule measured in hours rather than being exported, reformatted and rekeyed. Automated matching runs against live ledger data so that a break appears as an exception with an owner attached rather than as a variance discovered at close. Tolerance rules catch magnitude errors that look reasonable to a reader, which is the specific category that defeated Kodak and Magellan. Every calculation keeps its lineage, so the logic behind a number can be re-read rather than reverse-engineered, which is the check no one performed on the Reinhart and Rogoff average for three years.

None of this argues that spreadsheets should disappear from finance, and the argument would be wrong if it did. Scenario modeling, exploratory analysis and one-off investigation are genuinely better in a spreadsheet than in a rigid system, which is why the sensible position is to keep Excel where it earns its place rather than to replace it wholesale. The failure in all nine cases was not that people used a spreadsheet. It was that a spreadsheet had become the system of record and the control at the same time, and it was designed to be neither.

Why choose Stacks for enterprise financial control?

Every failure in this article passed through a step where a person moved a number by hand. Stacks removes the step rather than supervising it. Subledger and general ledger data arrive directly from ERP systems, so the manual conversion layer that dropped 15,841 test results at Public Health England has no equivalent in the workflow, and the copy-and-paste chain that carried JPMorgan's risk model has nowhere to form.

The controls map onto the specific failure modes above. Reconciliation runs continuously against live ledger data, so a break arrives as an assigned exception during the period rather than as a variance discovered after it closes. Tolerance rules flag figures that sit outside expected magnitude, the category that defeated both Kodak's severance calculation and Magellan's distribution estimate. Row relationships are held by the data model rather than by whoever last sorted a column, which is the failure that sent MI5 to the wrong numbers. Calculation logic is fixed and versioned, and journal entries post from a controlled source instead of a file that three people have edited in turn.

The audit position matters as much as the errors avoided. Every match, exception, adjustment and approval carries an evidence trail with preparer, reviewer and timestamp captured as the work happens, so tracing a figure to its origin is a query rather than a reconstruction from email attachments. For a team that has spent a close hunting the source of a variance, that is the difference between believing a number and being able to show why it holds.

FAQs for Spreadsheet Errors

What are the most common spreadsheet pitfalls?

Copy-and-paste transfer errors, file format limits that truncate data silently, hidden rows and tabs carrying data into shared files, partial sorting that breaks row relationships, magnitude and sign errors from manual entry, date formatting faults, undetected logic errors in nested formulas, and version drift across uncontrolled copies. Each shares one property, which is that the file produces a plausible result rather than an error.

What was the JPMorgan London Whale spreadsheet error?

JPMorgan's Chief Investment Office lost at least $6.2 billion in 2012. The bank's Management Task Force report found that the Value-at-Risk model operated through manually completed Excel spreadsheets populated by copying and pasting between files, and that one cell divided two rates by their sum instead of their average, understating volatility by approximately half.

How did an Excel error affect COVID-19 testing in the UK?

Public Health England lost 15,841 positive test results over eight days in late 2020. Laboratory results arrived correctly as CSV files, but PHE's automated process loaded them into Excel templates in the legacy XLS format, which holds about 65,000 rows. Since each result used several rows, each template capped at roughly 1,400 cases and silently dropped the rest.

Why is spreadsheet version control a risk?

Because no copy is authoritative. When files move between people by email, a single incorrect entry has nothing to contradict it, and discrepancies surface as published errors rather than as system conflicts. RedEnvelope's 2005 guidance revision traced to one mis-recorded cell that no independent copy was in a position to challenge.

How can finance teams prevent copy-and-paste errors in accounting?

Remove the transfer step rather than controlling it. Direct integration between source systems and the close platform eliminates rekeying, and matching that runs automatically against live ledger data removes the manual reconciliation stage where transcription errors are introduced.

What is the best alternative to Excel for month-end close reconciliations?

A dedicated close platform that ingests ledger and subledger data directly, reconciles continuously, tracks exceptions as assigned work items, and builds the audit trail as the work happens. The practical test is whether the system can tell you what is missing, which is the question a spreadsheet is structurally unable to answer.