Over the weekend, the U.K. saw its number of COVID-19 cases spike in large part because of a Microsoft Excel error.
Between Sept. 25 and Oct. 2, 15,841 cases went unreported in the government database. According to Public Health England, the files with positive results sent by the National Health Service’s test-and-trace system exceeded the maximum size. Because of the error, which was discovered Friday, nearly 48,000 people who had had contact with those who tested positive weren’t traced. All told, it could have put many lives at risk.
And you thought you had had frustrating experiences with Excel.
According to the BBC, the error was caused by the fact that Public Health England developers stored the test results in the file format known as .XLS. These .XLS files were then sent to the NHS after uploading to a central system. .XLS is an outdated file format, however, and each spreadsheet can have only 65,000 rows. By contrast, the .XLSX file format, which was first released in 2007, allows for more than 1 million rows. Because of the limited number of rows, each spreadsheet could contain about 1,400 cases, leaving excess cases off the file altogether. Although the issue was reportedly fixed by splitting the files into smaller batches, many are slamming Public Health England. “Why are critical databases in a national pandemic posted on Excel spreadsheets?” Jonathan Ashworth, the Labour Party’s shadow health secretary, said. “Why aren’t they using specialist data-based software?”
This isn’t the first time that misuse of Excel has caused a massive error. Although the platform can look simple, one basic error can end up creating ghastly results for entire companies. Here are six other supremely costly spreadsheet mistakes.
The Reinhart-Rogoff Economic Study
In 2010, Harvard economists Carmen Reinhart and Ken Rogoff published “Growth in a Time of Debt,” a highly influential study that looked at 20 nations and concluded that when a country’s debt reaches 90 percent of its gross domestic product, its economy will shrink by an average of 0.1 percent. This finding supported the worldview of small-government conservatives—indeed, it was part of the basis of the House Republicans’ budget proposal in fiscal year 2012 to severely cut public spending. (The proposal was shot down 57–40 in the Senate). However, three University of Massachusetts economists pointed out in 2013 that an Excel coding error had left out data from five countries on the list. The results dramatically changed after fixing the error—showing that when a country’s debt reaches 90 percent of its GDP, the economy will grow by 2.2 percent. Reinhart and Rogoff eventually confirmed that error affected the study’s results, but still stood by their conclusion that higher government debt is associated with slower economic growth. The admission sent the media in a frenzy, as many policymakers across the world, including former U.K. Chancellor George Osborne, had also cited the paper in their budget proposals. Because the paper was published in a certain edition of the American Economic Review, it was not subjected to a rigorous peer review, leading researchers to question whether the mistake would have been caught earlier if it had gone through a strict peer review process.
Excel Sheets Altering Human Gene Names
Scientists who use Excel to track their research had to rename 27 genes in early August because the program continuously got confused. Each of the thousands of human genes has an alphanumeric code, also known as a symbol. But Excel kept thinking that those symbols were dates. For example, it would convert the gene symbol SEPT1 to 1-Sept. The problem went on for years until the HUGO Gene Nomenclature Committee, the standards organization for naming genes, renamed the ones causing the most confusion—SEPT1 became SEPTIN1.
The problem is widespread, however—according to a 2016 study, one-fifth of 3,597 genomic research papers had gene name conversion errors.
The 2012 London Olympics’ 10,000-Ticket Overbook
There were numerous ticketing controversies before the London Olympics, and one of them was caused by a lousy mistake on a spreadsheet. A staff worker typed 20,000 instead of 10,000 into a cell, leading to nearly 3,000 customers buying 10,000 tickets for nonexistent seats at four synchronized swimming events. As compensation, customers got to exchange their tickets for more popular (and expensive) events.
MI5 Bugging More Than 100 Wrong Phones
Back in 2010, the British intelligence agency MI5 tapped 134 of the wrong phones for an investigation, leading to the actual suspects going unsurveilled. A formatting error on a spreadsheet altered the original last three digits to “000,” causing the security service to request subscriber data on people who had no connection to the investigation. The data was destroyed when the error was spotted, although it’s unclear what caused it in the first place.
Barclays’ Hiding-vs.-Deleting Mistake
Lehman Brothers—once the United States’ fourth-largest investment bank—filed for bankruptcy in 2008, triggering that year’s financial crisis and leading British multinational investment bank Barclays to offer to acquire the company. The law firm Cleary Gottlieb Steen & Hamilton listed the assets it intended to buy through Excel cells, but hid, instead of deleted, the 179 bad contracts it did not want to acquire. However, when the spreadsheet was converted into a PDF file, the hidden contracts reappeared, but the mistake was only caught after U.S. Bankruptcy Judge James Peck approved the $1.75 billion deal. Barclays eventually had to swallow the losses, although the amount was not disclosed.
TransAlta’s $24 Million Cut-and-Paste Mistake
In 2003, Canadian electricity company TransAlta bought more U.S. power transmission hedging contracts than it should have, costing it $24 million. The company later discovered that an employee had used a cut-and-paste action on Excel but misaligned the rows, resulting in higher bidding prices assigned to the wrong contracts. The mistake had severe consequences, wiping out 10 percent of the company’s profit that year.
Future Tense is a partnership of Slate, New America, and Arizona State University that examines emerging technologies, public policy, and society.
