For the second time in the last three months, I had a grateful client report back to me that a new report commissioned out of my software product uncovered errors in their former spreadsheet-based billing system. In this case, they would have under-billed a client by several hundred dollars for the quarter. Considering that this spreadsheet had been in use for years and copied to other spreadsheets for billing other customers, one wonders what the total cost was.
Spreadsheets are like high blood pressure: the silent killer of businesses. Of course, this means every business except yours and mine. It’s all those other users who have error-riddled spreadsheets with mistyped formulas and errant cell references. I have every confidence that yours and mine fall into the ten percent of all spreadsheets that contain zero errors.
90% of All Spreadsheets Have Errors
Raymond Panko has been researching and documenting spreadsheet errors since 1997. In 2009, he conducted seven field audits of operational spreadsheets and found that an astonishing 88% of them contained errors. At first blush, this is hard to swallow. But what if I were to tell you that one in every twenty cells with a formula is flawed? That doesn’t seem so hard to believe. If you then allow that the typical operational spreadsheet averages about a hundred cells with formulas, then it suddenly becomes quite plausible.
Another field audit of fifty operational spreadsheets by Stephen Powell at Dartmouth College found that only three of them (94% percent) were error-free. In this study, six main types of errors were reported:
- Hard-coded numbers in formulas where cell references were intended
- Formulas that contain incorrect cell references
- Logic errors caused by using the wrong function or incorrectly implementing the function
- Copy/paste errors
- Inaccurate calculations due to cells with omitted data
- Data input error
These sorts of errors are known to cost businesses billions of dollars every year. The European Spreadsheet Risks Interest Group published a list of 89 “horror stories” encountered in their own evaluations.
But I’m sure yours and mine are perfectly fine.
What’s the Point?
Databases. Most operational spreadsheets I’ve come across are a case of trying to hammer a nail with a banana. They are situations that are best suited by a bona fide database back end and user interface. The fundamental problem with a spreadsheet is that the data and business logic are not separated from the user. One inadvertent keystroke can wipe out a carefully crafted formula written five years ago by a statistical guru no longer employed by the company. Fault tolerance is another issue, as is data type checking and many other standard features of a properly designed databases that will protect both the data and the formulas.
I’m particularly sensitive to this topic after responding to Gary Mintchell’s recent blog post, “You should all learn SQL.” In it, he noted the proliferation of databases and subsequent need for more non-IT personnel to learn SQL. As this discussion points out, however, maybe the proliferation is not quite fast enough.
I’d love to hear your thoughts and, particularly, any spreadsheet horror stories you’d like to share.
I couldn’t agree more. Excel is an analysis tool, not a database engine and definitely not an accounting application. If you have a small business, use Quickbooks or some similar purpose-built application for accounting!
I see spreadsheets mis-used more often than not. Even the most simple list created in Excel often grows into an unmanageable table that belongs in a database. If you’re managing short lists, consider moving them to SharePoint so that people can collaborate on the list and simple views can easily be created.
Examples range from using a spreadsheet for a form instead of Word to using multiple spreadsheet files cross-referencing each other via vlookup(), hlookup(), and index(), and the Dxxxx() database functions. I have run across spreadsheets that were so complex that changing a single cell would induce a PC-stopping refresh cycle that would last a minute or more. Moving this kind of “application” to a simple database like MS Access would generate more detailed and useful reports in less than a second. Spreadsheets aren’t databases. They’re used for analyzing data, not managing it.
I once inherited a monster spreadsheet that was used for business forecasting. I’m sure that it started out very simple but then grew in complexity over time. The spreadsheet that I was using eventually became so complex that it would often crash Excel. Any minor error in a single cell could throw off the overall results, and auditing the spreadsheet for errors was nearly impossible. Tracking different versions of the spreadsheet and tracking changes was a huge pain. What a nightmare!
When I moved the forecasting model to Access, I was able to easily add new functions by tweaking a simple query. There was no copying of formlas across rows, columns, or sheets. You just add a formula in the query and then it is always applied across all of the data. Eventually, we’d even move some of the queries I built into our SAP business warehouse so that they were even more standardized across the business.
I’d often export the database results to Excel for quick decision and what-if analyses, but if I ran a certain anaysis often enough then I’d create a query in Access to standardize it.