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.
While trying to understand a spreadsheet mistake that erased 16,000 positive COVID-19 cases from the UK’s contract casing system, writer Tim Harford took the time to dig into the history of the humble spreadsheet (spoiler alert: it dates back to an Italian textiles merchant in the 1300s), how spreadsheets came to become 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.”
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's my list of greatest hits on the topic:
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?