In this article we describe how spreadsheets are riddled with errors, drawing on our own experience and on the research presented in the academic literature on spreadsheet errors and testing.
Around 95% of all spreadsheets contain errors. So, unless you've been extremely diligent, or perhaps just very lucky, then it is almost certain that your spreadsheets are wrong.
The spreadsheets that you rely on – to inform decision making, do reporting, and generally conduct analysis of just about everything – are probably giving you incorrect results.
How do we know your spreadsheets are wrong?
We know your spreadsheets are wrong because we've been there, done that. Our own extensive experience in building, working with, and testing spreadsheets tells us that just about every spreadsheet contains serious flaws.
From hard fought experience we understand what it is like developing and using spreadsheets in the real world – the time pressures, budget limitations, short-cuts, approximations, and everything else that is needed to just get the job done.
Along the way we've made our fair share of mistakes, so we know that awful feeling of going to see the boss to confess that the spreadsheet was wrong.
Consider the example spreadsheet in the figure. There are two errors that effect six cells. Can you identify the errors?
Every study that has looked for spreadsheet errors has found them
It isn't just our experience that tells us that spreadsheet errors are common. The following quotes from the academic literature on spreadsheet errors are quite sobering:
- "Every study that has looked for errors has found them... in considerable abundance", Panko & Halverson (1996).
- "Spreadsheets are extraordinarily and unacceptably prone to error", Dunn (2010).
- "94% of the 88 spreadsheets audited in 7 studies have contained errors", Panko (2008).
- "Untested spreadsheets are riddled with errors", Miller (2005).
- "1% of all formulas in operational spreadsheets are in error", Powell, Baker, & Lawson (2009).
That last quote is particularly interesting. A 1% error rate may not sound like a significant problem. However, it is the rate per formula, and a typical spreadsheet contains hundreds or thousands of formulae. If your spreadsheet contains just 300 formulae, then a 1% error rate means that there is a 95% probability that the spreadsheet contains at least one error.
Note that the issue isn't that spreadsheets are error prone, as such. Rather, the issue is that humans are error prone. We make errors all the time, but we tend to not notice most of them. This human tendency is well understood in the context of developing other types of computer programs, so programmers do extensive testing of their applications. Spreadsheet developers, conversely, generally do little or no testing of their spreadsheets.
Your spreadsheets need to be tested
Because your spreadsheets almost certainly contain errors, they need to be tested. Beware of being over-confident about your spreadsheets – as Caulkins, Morrison, & Weidemann (2006) observed, "People tend to believe their spreadsheets are more accurate than they really are".
Further information on spreadsheet errors
Here are a couple of good places to start learning about how to make better spreadsheets:
- iⁿ spreadsheet methodology.
- Bibliography of the spreadsheet literature.
If you have any comments about this article, then please contact us.
