Tech

The most expensive Excel mistakes in history and what you can learn from them

Published

2 weeks ago

24 November 2025

The most expensive Excel mistakes in history and what you can learn from them

Excel’s power lies in its accessibility, though this very trait makes it a breeding ground for disastrous errors. A single typo or logical oversight can—and has—led to losses scaling into the billions. Here are the most infamous and costly Excel mistakes in history and the lessons we can learn to ensure they’re never repeated.

JPMorgan Chase (2012): The faltering function

The so-called “London Whale” trading incident occurred because a flawed model was used to manage risk. Specifically, the formula was intended to calculate a Value-at-Risk (VaR) by aggregating and averaging a complex set of financial data points. However, instead of correctly summing the data before the eventual division, the formula averaged the rates directly. This fundamental flaw in the formula logic resulted in a highly underestimated VaR calculation, giving traders a false sense of security that led to over $6.2 billion in losses.

The key lesson here is that, alongside procedural and management problems, the wrong function or incorrect formula order can instantly derail an entire model. The mishap has reminded me to always verify my formulas using the tools in the Formula Auditing group of the Formulas tab on the Excel ribbon:

The Formula Auditing group of Excel's Formulas tab.

Trace Precedents: This tool visually confirms that all and only the correct input cells feed into a calculation, allowing you to spot if a range is too large or too small for the intended sum. After selecting a cell, head to the “Formulas” tab, and click “Trace Precedents.”
Watch Window: This lets you monitor the output of critical formulas in real-time while changing inputs elsewhere. To use it, in the Formulas tab, click “Watch Window,” and add the cell references you want to monitor.
Evaluate Formula: This feature lets you debug complex formulas by walking through the calculation step by step, revealing how Excel resolves the formula and identifying where an unintended function is executed.

Fidelity Investments (1994): The missed minus

One day, an in-house accountant manually transcribed financial records into a spreadsheet to calculate the year-end dividend distribution. However, shockingly, the accountant failed to include the minus sign for a $1.3 billion net capital loss. As a result, the positive entry was processed as a gain, flipping the figures in subsequent formulas and throwing the final dividend calculation off by $2.6 billion.

The most critical lesson in this case is that manual data transcription is a dangerous failure point, highlighting the importance of importing data directly using Excel’s tools. When direct imports aren’t possible, Excel offers tools to ensure you’re entering the correct data into the correct cells:

Power Query: This powerful tool allows you to link and import data directly from trusted sources, eliminating the human error that comes with manual re-typing. To initiate this process, open the “Data” tab on the ribbon, click “Get Data,” and select the source.

The Get Data options in the Data tab on Excel's ribbon.

Data Validation: You can reject inputs that don’t match the expected format, such as a positive number being entered when a negative number is required. Select the input cell, and in the Data tab, click “Data Validation.” Then, see the options for the different types of rules you can set.

The Data Validation dialog box in Excel, with the Allow drop-down menu expanded.

Lazard and Tesla (2016): A duplication disaster

When advising SolarCity on its acquisition by Tesla, investment bank Lazard used a Discounted Cash Flow (DCF) model built on SolarCity’s financial spreadsheets. However, things went south when a set of projected liabilities was double-counted within the model’s formula. This error inflated SolarCity’s debt and liabilities, resulting in an initial valuation that understated the company’s worth by about $400 million.

There’s one clear takeaway from Lazard’s costly error. Where possible, I try to move away from relying on anonymous cell coordinates when referencing variables in even simple models. There are two main ways I do this:

Name Manager: This allows you to assign simple, unambiguous names to critical figures. This prevents confusion or accidental inclusion of the same value twice in a formula. You can name a cell or range in the Name Box in the top-left corner of the Excel window. Alternatively, select the input cell, and in the Formulas tab, click “Define Name.”

The Name Manager button in the Formulas tab on Excel's ribbon.

Excel Tables: Formatting datasets as Excel tables forces formulas to use column names instead of direct cell references, making it easier to trace which inputs are used in which formulas. To restructure your data this way, select the range, and in the Insert tab, click “Table.”

The Table button in the Insert tab on Excel's ribbon.

Kodak (2005): Multiplied millions

When a Kodak employee was tasked with preparing a spreadsheet for accrued severance payments, they made a simple but critical data entry error—typing one too many zeros. After realizing the mistake had led to an $11 million overstatement of liabilities, the company had to restate earnings.

This mistake is a timely reminder to make the most of Excel’s data integrity tools. Indeed, every manual input should be treated as a potential liability, and two main guardrails can help address this:

Data Validation: You can block people from entering figures with too many zeros or values that go above a reasonable limit. After opening the Data Validation window, set the criteria to “Decimal,” and set a threshold by selecting “Less Than Or Equal To” in the Data field.

The Data Validation dialog box in Excel, with Decimal selected in the Allow field, Less Than Or Equal To in the Data field, and 1000000 in the Maximum field.

Conditional Formatting: This tool provides a quick but crucial visual check by highlighting any cell whose value exceeds a pre-set threshold. Select the output cells, open the “Home” tab, and select Conditional Formatting > New Rule. Then, set a rule to highlight cells containing values “Greater Than” a safe threshold.

Conditional Formatting is applied in Excel in the form of an orange cell fill to all cells whose value is greater than one million.

TransAlta (2003): The rogue relative reference

TransAlta, the Canadian power generator, lost about $24 million during a high-stakes bidding process for electricity contracts. This costly mistake was caused by a formula being copied and pasted. Since the formula used relative cell references, when it was pasted into another area of the spreadsheet, it shifted incorrectly. As a result, the bid calculation pulled cost data from the wrong, unrelated rows, effectively pairing the wrong price with the wrong contract.

This mishap illustrates the danger of relying on positional referencing. I’m often tempted to leave cell references as-is, but this habit is a massive calculation risk when I start copying formulas. Instead, I’m mindful to use these workarounds:

Absolute and mixed references: Press F4 once after adding a cell reference to a formula to add dollar signs ($A$1). This prevents it from shifting when the formula is copied to another location. Press it again to lock only the row reference (A$1), and press it again to lock only the column reference ($A1).
XLOOKUP or INDEX with XMATCH: These functions retrieve data by matching an identifier rather than relying on its physical position. This ensures the correct data is paired with the correct formula, even if the data changes.

Two of the most effective ways to reduce errors in Microsoft Excel are to ensure your spreadsheets are easy to read and logically structured. Indeed, a confusing layout encourages mistakes, and poorly structured datasets mean that tools designed to audit your figures are less likely to pick up major errors. By prioritizing your worksheets’ structure and auditability, you’re one step closer to ensuring you’re not responsible for the next billion-dollar blunder.