Spreadsheet Errors

Abstract

The spreadsheet is the most widely used programming environment in the world, and almost nobody who uses it is trained as a programmer, tests their work, or has it reviewed. From the 1990s the information-systems researcher Raymond Panko measured the result: errors in roughly one formula cell in a hundred, and in the large majority of the real-world spreadsheets that auditors examined. The cases that reached the newspapers showed the same patterns at scale: a cut-and-paste slip that cost TransAlta $24 million in 2003, hidden rows that put 179 unwanted Lehman Brothers contracts into Barclays’s purchase in 2008, a value-at-risk model at JPMorgan that divided by a sum instead of an average, gene names silently turned into dates in a fifth of genomics papers, and 15,841 English COVID-19 cases lost in 2020 to a row limit. A small research field, organised around the European Spreadsheet Risks Interest Group, has studied why it happens and built tools to catch it.

Programming Without the Name

A spreadsheet formula is a program. =SUM(B2:B40)/B41 reads inputs, computes and produces an output that other cells depend on, and a large model can contain tens of thousands of such formulas in a dependency graph that no one has drawn (see The Spreadsheet Revolution). But the practices that professional software development built up over decades, such as version control, code review, automated tests and a separation between code and data, are mostly absent. The formulas are hidden behind their results. A formula copied down a column looks the same as one that was copied down and then overwritten by hand in row 23 with a typed number. Nothing in the program warns that the two differ.

These properties made the spreadsheet easy to learn and hard to check. Its users were accountants, analysts, engineers and scientists who had never been taught to test code, and usually did not think of what they were writing as code.

Measuring the Error Rate

Raymond R. Panko, a professor of information technology management at the University of Hawaiʻi, spent much of his career counting. In laboratory experiments, students and professionals built small spreadsheets from a written task, and Panko counted the cells that came out wrong. Across 14 such studies with 967 people working alone, the average cell error rate was 3.9 percent, within the band of 1 to 5 percent that research on human error in general would predict for careful, repetitive work such as typing or programming. A low rate per cell does not mean a low rate per spreadsheet: a spreadsheet with hundreds of formulas will almost certainly contain some errors.

Field audits of real business spreadsheets showed the consequence. In 2004 Computerworld reported Panko’s tally of audits finding errors in 49 of 54 spreadsheets examined, 91 percent. A later compilation of field audits from 1995 to 2007 put the figure at 88 percent of 113 spreadsheets. Panko also found that developers were confident their work was correct: “Developers are overconfident, and policies are lax.”

The European Spreadsheet Risks Interest Group (EuSpRIG), a voluntary association of auditors, consultants and academics, has held an annual conference on the subject in London since 2000 and publishes its proceedings openly on arXiv. Its website collects press reports of spreadsheet failures, a list that grows every year. The group’s work aimed at practice as much as research: inventories of which spreadsheets in a company feed its financial reports, review procedures, and rules for building models that can be checked.

The Public Cases

The errors that reached the press had a few recurring shapes.

Cut and paste. On 3 June 2003 the Canadian power producer TransAlta announced a $24 million charge. When it submitted bids for transmission hedging contracts in the U.S. in May, rows in the bid spreadsheet had been misaligned in a final sorting and ranking step, so that it bought more contracts at higher prices than it intended. The chief executive, Steve Snyder, called it “literally a cut-and-paste error in an Excel spreadsheet.” The same year, Fannie Mae restated its results by $1.2 billion after what it described as “honest mistakes made in a spreadsheet” used to implement a new accounting standard.

Hidden rows. When Barclays agreed in September 2008 to buy parts of the bankrupt Lehman Brothers, the list of trading contracts it would take over was prepared in Excel, with rows for contracts Barclays did not want hidden rather than deleted. A junior associate at the law firm Cleary Gottlieb, reformatting the file to convert it into a PDF for the court filing, unhid them, and the document filed with the bankruptcy court included 179 contracts Barclays had not agreed to buy. The mistake was found after the judge had approved the sale, and Barclays had to ask the court to exclude them.

The wrong formula. In 2012 JPMorgan Chase lost about $6 billion on credit derivatives traded by its Chief Investment Office in London, the “London Whale” trades. The bank’s internal task force report of January 2013 found that a new value-at-risk model, adopted that January, ran through a series of Excel spreadsheets filled in by manual copy-and-paste. In one step, after subtracting an old rate from a new one, the spreadsheet divided by their sum instead of their average, as the modeller had intended. The error reduced the measured volatility by about a factor of two and with it the reported risk, at a time when the risk limits were the main check on the trades.

Silent conversion. Excel interprets entries that look like dates or numbers. Typed or imported, the human gene symbol SEPT2 (Septin 2) becomes 2 September and MARCH1 becomes 1 March, and some identifiers in scientific notation become floating-point numbers. In 2016 Mark Ziemann, Yotam Eren and Assam El-Osta screened 35,175 supplementary Excel files from 3,597 genomics papers and found gene name errors in 704 of them, about one fifth. A follow-up in 2021 by Mandhri Abeysooriya and colleagues found the problem had grown, with errors in 30.9 percent of articles with Excel gene lists published from 2014 to 2020. The genetics community changed first: in August 2020 the HUGO Gene Nomenclature Committee published guidelines that renamed 27 genes, SEPT1 to SEPTIN1 and MARCH1 to MARCHF1, and ruled out new symbols that a spreadsheet would read as dates. In October 2023 Microsoft added an option to Excel for Windows and Mac to switch off individual automatic conversions.

The format’s limits. Between 25 September and 2 October 2020, Public Health England left 15,841 positive COVID-19 tests out of its daily figures. Commercial laboratories sent results as text files, which were loaded into Excel’s older .xls format, limited to 65,536 rows per sheet. Each test took several rows, so a sheet held about 1,400 cases, and the rows beyond the limit were dropped during import without an error. The cases were reported late, and up to 48,000 contacts of the infected were not traced on time. The government called it a “file size” issue and fixed it by splitting the files.

The academic case that drew the most attention was in economics. In 2013 a graduate student, Thomas Herndon, found that an AVERAGE formula in the spreadsheet behind Carmen Reinhart and Kenneth Rogoff’s paper on public debt and growth left out five countries (see Fun Fact: The Excel Error Behind an Austerity Argument).

The Enron Corpus

Research on spreadsheet errors had a data problem: companies do not release their internal spreadsheets. The exception came from a bankruptcy. During its investigation of Enron, the U.S. Federal Energy Regulatory Commission collected the company’s email, and the collection was later made public for research. In 2014 Felienne Hermans of Delft University of Technology and Emerson Murphy-Hill of North Carolina State University extracted the attachments: 51,572 Excel files, 16,189 of them unique, from about 15 months of email at a large energy trader, with about 100 spreadsheet emails a day. They presented the dataset at the International Conference on Software Engineering in 2015.

The spreadsheets were simpler than anyone had expected. They used 134 distinct functions of Excel’s more than 300, and about three quarters of them used only the 15 most common. Of the spreadsheets that contained formulas, 24 percent contained at least one Excel error value such as #REF! or #DIV/0!, the kind of error that is visible in the file. The emails showed spreadsheets as a working medium: about 6 percent of the messages sending or discussing them mentioned errors.

Tools

The software-engineering response was to treat spreadsheets as programs and analyse them like programs. Hermans’s group adapted the idea of “code smells,” patterns that often indicate a defect, to formulas: a formula with too many references, a long chain of dependencies, or a constant typed into a formula where a cell reference belonged. Microsoft Research sponsored several analyses.

ExceLint, by Daniel Barowy, Emery Berger and Benjamin Zorn, presented at OOPSLA in November 2018, used the geometry of the sheet. Most spreadsheets are built from rectangular blocks of formulas that do the same thing at different positions, such as one formula per row of a table. ExceLint encodes each formula’s references relative to its own cell and looks for formulas whose reference pattern breaks the pattern of their neighbours, the cell in row 23 that points one column too far or was overwritten with a constant. On a benchmark of 70 spreadsheets it found errors more precisely than earlier tools, with a median running time of a few seconds.

Dead End: More Discipline

The standard recommendation since the 1990s has been to apply software-engineering discipline: specify, design, test, review. Panko’s review of the evidence found only one technique that had been shown to work, cell-by-cell inspection of the formulas, and it is slow. Almost no organisation spent that effort on spreadsheets, whose whole attraction was that anyone could build one quickly without process. The models that caused the large losses were built under deadline by competent people who did not consider what they were doing to be programming.

What changed the outcomes was usually not discipline but changes to the tool and the data. Excel’s later formats raised the row limit to 1,048,576; geneticists renamed their genes; Microsoft eventually let users turn off the conversions. Each removed one class of error and left the error rate per formula, the human one, where Panko had measured it.