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

The power of Excel lies in its accessibility, although this very feature makes it a breeding ground for disastrous errors. A single typo or logical oversight can – and has – resulted in losses worth billions. Here are the most infamous and costly Excel mistakes in history and the lessons we can learn to ensure they never happen again.

JPMorgan Chase (2012): The failing function

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

The key lesson here is that, alongside procedural and management issues, one bad function or incorrect formula order can instantly derail an entire model. This incident reminded me to always check my formulas using the tools in the Formula Auditing group on the Formulas tab of the Excel ribbon:

• Trace Precedents: This tool visually confirms that all and only the correct input cells are going into a calculation, allowing you to spot if a range is too large or too small for the expected sum. After selecting a cell, go to the “Formulas” tab and click “Trace Back”.

• Watch Window: This allows you to monitor the output of critical formulas in real time while editing inputs elsewhere. To use it, in the Formulas tab, click “Monitoring Window” and add the cell references you want to monitor.

• Evaluate Formula: This feature allows you to debug complex formulas by walking through the calculation step by step, revealing how Excel solves the formula and identifying where an unintended function is being performed.

Fidelity Investments (1994): Least missed

One day, an in-house accountant manually transcribed financial documents into a spreadsheet to calculate the year-end dividend distribution. However, shockingly, the accountant failed to include the minus sign for a net capital loss of $1.3 billion. As a result, the positive entry was treated as a gain, reversing the numbers in the following formulas and scattering the final dividend calculation of $2.6 billion.

The most important lesson in this case is that manually transcribing data is a dangerous point of failure, highlighting the importance of importing data directly using Excel tools. When direct imports aren’t possible, Excel offers tools to ensure you enter the right data in the right cells:

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

• Data Validation: You can reject inputs that do not match the expected format, such as entering a positive number when a negative number is required. Select the input cell and on the Data tab, click “Data Validation”. Next, see the options for the different types of rules you can set.

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 wrong when a set of projected liabilities were counted twice in the model formula. This error inflated SolarCity’s debt and liabilities, resulting in an initial valuation underestimating the company’s value by approximately $400 million.

There is one clear takeaway from Lazard’s costly mistake. Where possible, I try to no longer rely on anonymous cell coordinates when referencing variables, even in simple models. There are two main ways to do this:

• Name Manager: This allows you to assign simple and unambiguous names to critical characters. This avoids 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 upper left corner of the Excel window. You can also select the input cell and on the Formulas tab, click “Set Name”.

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

Kodak (2005): Millions multiplied

When a Kodak employee was tasked with preparing a spreadsheet for accrued severance pay, he made a simple but critical data entry error: typing one zero too many. After realizing that the error had caused the liability to be overstated by $11 million, the company had to restate its earnings.

This error is a timely reminder to get the most out of Excel’s data integrity tools. Indeed, every manual entry should be treated as a potential liability, and two main safeguards can help resolve this problem:

• Data validation: You can prevent users from entering numbers that contain too many zeros or values ​​that exceed 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.

• Conditional formatting: This tool provides a quick but crucial visual check by highlighting any cell whose value exceeds a predefined threshold. Select the output cells, open the “Home” tab and select Conditional Formatting > New Rule. Next, set a rule to highlight cells containing values ​​”greater than” a safe threshold.

TransAlta (2003): The relative rogue reference

TransAlta, the Canadian power producer, lost about $24 million in a high-stakes bidding process for electricity contracts. This costly error was caused by copying and pasting a formula. Because the formula used relative cell references, when pasted into another area of ​​the worksheet, it did not shift correctly. As a result, the bid calculation extracted cost data from the wrong unrelated lines, thus associating the wrong price with the wrong contract.

This incident illustrates the danger of relying on positional SEO. I’m often tempted to leave cell references as is, but this habit poses a huge computational risk when I start copying formulas. Instead, I make sure 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 moving when the formula is copied to another location. Press it again to lock only the row reference (A$1), then 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 that the correct data is associated with the correct formula, even if the data changes.

Two of the most effective ways to reduce errors in Microsoft Excel are to ensure that your spreadsheets are easy to read and logically structured. This is because a confusing presentation encourages errors, and poorly structured data sets mean that tools designed to audit your numbers are less likely to detect major errors. By prioritizing the structure and auditability of your spreadsheets, you’re one step closer to ensuring you’re not responsible for the next billion-dollar mistake.

Operating system

Windows, macOS, iPhone, iPad, Android

Free trial

1 month

Microsoft 365 includes access to Office apps like Word, Excel, and PowerPoint on up to five devices, 1TB of OneDrive storage, and more.