Today I’d like to share a story from The Financial Times that’s very near and dear to my heart: The Tyranny of Spreadsheets: How One of Our Most Powerful Tools Became One of the Most Misused.
If you’ve been using spreadsheets to manage processes like risk management, compliance, audit, vendor management, business continuity, or any other non-numerical task, then I encourage you to give it a read. It might make you rethink if it’s really the best way forward.
Our story begins in October 2020 when Public Health England (PHE), the government agency overseeing the U.K.’s COVID-19 response, used an Excel spreadsheet to track confirmed cases of COVID-19. Reports suggest that PHE underreported around 16,000 confirmed cases of COVID-19 to its national tracking numbers in October 2020 because a spreadsheet had reached its max size and was unable to update. (In case you’re curious, Excel maxes out at 1,048,576 rows and 16,384 columns).
At the time U.K. cases were just beginning to surge, rising from around 7,000 cases per day at the beginning of October to 22,000 at the end of the month—making the addition of 16,000 cases a significant number.
Writer Tim Harford wanted to understand how a spreadsheet mistake could erase 16,000 positive COVID-19 cases from the UK’s contact tracing system. As part of his research, he dug into the history of the humble spreadsheet (spoiler alert: it dates back to an Italian textiles merchant in the 1300s), how spreadsheets came to be such a common tool, and why they are extremely misused today.
Among my favorite takeaways from the piece (which I encourage you to read):
Excel works for accountants because they have a double-entry system to create checks and balances for their work. That makes them far more likely to catch inconsistencies and mistakes than those using spreadsheets for other, non-numerical purposes.
“For accountants, digital spreadsheets were revolutionary, replacing hours of painstaking work with a few taps on a keyboard. But some things didn’t change. Accountants still had their professional training and their double-entry system. The rest of us did not, but that did not prevent Excel from becoming ubiquitous. It was, after all, easily accessible and flexible, a tool like a Swiss Army knife for numbers, sitting in your digital back pocket…
When used by a trained accountant to carry out double-entry bookkeeping, a long-established system with inbuilt error detection, Excel is a perfectly professional tool. But when pressed into service by genetics researchers or contact tracers, it’s like using your Swiss Army Knife to fit a kitchen because it’s the tool you have closest at hand. Not impossible but hardly advisable.”
Lots of people are misusing spreadsheets. A group of geneticists used Excel to track results from a genetics study. One problem: The gene Membrane Associated Ring-CH-Type Finger 1 is abbreviated March1. You can probably guess what happened when that name was entered into the spreadsheet. It was converted into a date, a problem so bad that one study estimates that as many as 20 percent of genetics papers have errors resulting from autocorrection of March1 and other unfortunately named genes.
As the author puts it: “Microsoft’s defense is simple enough: the default settings are intended to work in everyday scenarios. Which is the polite way of saying: Guys, Excel wasn’t designed for genetics researchers. It was designed for accountants. But it’s understandable that scientists picked up Excel and started to use it. It’s powerful, it’s flexible. It’s ubiquitous. It may not be the right tool, but it’s the tool that’s right there."
Even Bill Gates thinks people use spreadsheets incorrectly. In trying to understand where Public Health England went wrong with its COVID-19 tracking spreadsheet, Harford spoke to numerous experts who all agreed that there need to be controls in place to ensure spreadsheets are accurate.
“I didn’t understand it, so I showed it to some members of Eusprig, the European Spreadsheet Risks Group. They spend their lives analyzing what happens when spreadsheets go rogue. They’re my kind of people. But they didn’t understand what PHE had told me, either. It was all a little light on detail. They agreed that the basic problem was that whatever PHE had done wrong, it didn’t have the right checks and controls to flag problems.” Or as Gates put it, “It’s good to have people double-check things.”
We alter how we work to fit the needs of a spreadsheet—instead of the other way around. When we use a spreadsheet, we need processes that can work with the spreadsheet. We send emails reminding people of tasks because a spreadsheet can’t tell you what needs to be done. We hope that updates have been made and that no one downloaded the spreadsheet and is maintaining another version. We dig through spreadsheets looking for the information we need. It can hold information, but it can’t offer reminders or reporting. It’s easy for pieces of information to be lost or overwritten.
In the case of geneticists, the international committee that names genes actually re-named some genes just to make them work better with Excel.
As Harford notes: “The decision is understandable. But it also neatly illustrates the contortions we go through as a result of treating data as an afterthought, just something to slap together on a spreadsheet. That is a shame, because history suggests that well-managed information can be transformative.
When a Good Tool Isn’t Good Enough
Over the years, I’ve spent a lot of time sharing spreadsheet horror stories because, as The Tyranny of the Spreadsheet reminds us yet again, they are not the universally useful tool that many think they are.
Here are two of the most recent:
“Unfathomable” spreadsheet loses 40,000 complaints. When Bristol City in the U.K. upgraded its complaint system, as many as 40,000 of its old complaints were moved over to an “unfathomable” spreadsheet that made it “virtually impossible” to find information about cases under investigation. Government employees would spend days or weeks looking for information in the unwieldy spreadsheet—even after a new employee was added to help with the task. Meanwhile, complaints entered into the new system are easy to track and resolve.
Lesson learned: Spreadsheets can be great for managing large amounts of data, but not so great at large amounts of task management.
Spreadsheet error delays flights over false safety concerns. A spreadsheet error caused Southwest to pause flights at Midway International Airport in Chicago in January 2021. An error created during a system update caused issues with aircraft weight and balance data. The airline said the error was only a paperwork problem and did not imperil any flights—but it had to be a stressful few hours while the airline sorted out the source of the problem. Plus, it incurred the cost of flight delays and bad press.
Lesson learned: When different versions of the same document exist, it can result in inconsistencies that cause stress, extra work, and costly delays.
Excel is a good tool, but it’s not always good enough for the job at hand. It was designed for accounting and numbers—not risk management. Using it for risk management poses numerous risks with one researcher comparing spreadsheet applications to “dark matter” in physics “in the sense that they pose a number of overlooked risks for organizations, including errors, privacy violations, trade secret extrusions, and compliance violations.”
When considering using Excel to manually manage risk management processes, make sure you weigh the cost savings of not buying a more specialized product versus the potential cost of having an exam finding or other oversight that results in a violation.
You wouldn’t manage your institution’s financials using an abacus or adding machine—tools that can help with math but provide little in terms of insight, graphing, sorting, or other capabilities. Are you sure you want to use Excel to manage risk?
Michael Berman is the founder and CEO of Ncontracts, a leading provider of risk and compliance management solutions, and the author of The Upside of Risk: Turning Complex Burdens into Strategic Advantages at Financial Institutions. His extensive background in legal and regulatory matters has afforded him unique insights into solving operational risk management challenges and drives Ncontracts’ mission to efficiently and effectively manage operational risk.