Reality by the Numbers – What the Spreadsheet Has Done to Us
The above screenshot contains four common and egregious spreadsheet errors. Can you find them all?
There is a hidden cost behind the reliance on spreadsheets that is invisible to those who are dependent on them. Most people use spreadsheets for multiple purposes, so using spreadsheets to manage water data seems “free” relative to the cost of purpose-built software for data management. A National Public Radio Podcast about spreadsheets was recommended to me by colleagues at the CWRA conference in Lethbridge last week. The podcast added new insight but also confirmed many things about spreadsheets that I have long believed to be true.
Spreadsheets originated as 11×17 sheets of paper which were used to tabulate data, primarily for financial management. If the sheet size was too small, multiple sheets were taped together to extend the columns and rows. However, in 1978, Dan Bricklin, while a student at Harvard Business School, got the idea that spreadsheets could be seamlessly extensible if they were rendered on a computer. He partnered with a computer programmer, Bob Frankston, to develop the first spreadsheet program – Visicalc.
Visicalc was a $99 application with such great utility that it was enough to convince people to buy a $2,000 computer. Soon, spreadsheet programs became the go-to tool for all kinds of industry. In fact, spreadsheet applications totally transformed the finance industry and became the language of Wall Street. “What if” questions – that previously would have been too expensive to answer – led to lots of opportunity for improving business efficiency by experimenting with the effects of adjusting business inputs on business outputs. Since 1980, 400,000 accounting clerk positions disappeared, but 600,000 accounting positions were created. Basically, spreadsheet applications made accounting cheaper, which increased the demand for accounting services.
“A virtual cult of the spreadsheet has formed, complete with gurus and initiates, detailed lore, arcane rituals – and an unshakable belief that the way the world works can be embodied in rows and columns of numbers and formulas.”
Stephen Levy
Spreadsheet results look really good no matter what goes into them. Less apparent are:
- how difficult spreadsheets are to troubleshoot and test;
- how susceptible spreadsheets are to trivial human error – studies show that 20% to 40% of spreadsheets contain errors;
- how poorly spreadsheets scale – it has been reported that an average of five spreadsheets are needed to answer one business question;
- how vulnerable spreadsheets are to data loss and separation of data from essential metadata;
- how often spreadsheets are used in phishing attacks to spread malware;
- how risky spreadsheets are for business continuity.
In summary, spreadsheets are easy to trust, but they are fundamentally untrustworthy.
Given that spreadsheets are so widely used and yet so untrustworthy, you would expect to be able to find many examples of expensive spreadsheet horror stories. Probably the most expensive was the $6 billion London Whale debacle, which, in part, was due to: “the model operated through a series of Excel spreadsheets, which had to be completed manually, by a process of copying and pasting data from one spreadsheet to another…” There are many other egregious examples of spreadsheet errors causing great harm. Copy and paste, hidden rows and columns, data and formula entry errors, deleted cells, lack of documentation about how the sheet was designed and subsequently modified, and multiple versions of the same data without version control are but a small sample of spreadsheet problems that most people have likely had some personal experience with, including yourself.
Don’t get me wrong. I love spreadsheets. I use them all the time and they are brilliant for one-off, ad hoc, data analyses, and investigations. However, for the processing and archiving of data fundamentally important to business processes, the real cost of a robust data management system is a very effective alternative to the much larger, but hidden, cost of spreadsheets.
