Spreadsheet good practice guidelines
Why good practice matters
Spreadsheets are important. Essential, even.
Far ahead of any other software, spreadsheets are the most widely used tools for analysis, reporting, and presentation. In 2016, Microsoft CEO Satya Nadella said that 1.2 billion people use Microsoft Office, of which Excel is a key part. The number of users has only grown since then. And that isn't counting other spreadsheet software, such as Google Sheets – which is reported to have another almost 1 billion users in 2023. Many organizations simply couldn't operate without spreadsheets. They use spreadsheets for critical tasks in finance and many other fields.
With such wide use, often for critical tasks, yet with extraordinarily high error rates, it is clearly essential that we need to do better. That is, concisely, why good practice matters.
In this section, we delve into some aspects of why we need to take spreadsheet good practice seriously.
Guidelines:
- These guidelines are based on academic research and practical experience
Specifically, we've developed these guidelines based on:
- Extensive practical knowledge from building, testing, and using spreadsheets.
- Decades of consulting experience offering modelling and analysis services globally.
- A review of the academic literature about spreadsheet practices, especially spreadsheet risks.
Guidelines:
- Treating spreadsheets as a simple tool is a mistake
- Recognize that even small spreadsheets have risks
We know that spreadsheets are important for many applications, including informing decision making. We also know that spreadsheets have real and substantial risks.
Therefore, an obvious question is: Why is spreadsheet practice generally so poor?
Guidelines:
- Use good spreadsheet practices to avoid horror stories
Spreadsheet errors are ubiquitous. But, relative to their occurrence frequency, few spreadsheet errors become public. When an organization's spreadsheet errors are publicized, the revelation is seldom made by choice.
Nonetheless, there are many examples of spreadsheet errors being made public. Some errors have been widely reported in the media, due to their substantial impacts.
Guidelines:
- We treat spreadsheet results as "the truth", generally without justification
- The problem is partly the way spreadsheets work, but mostly it is us
- Overconfidence is the biggest issue
We have more confidence in our spreadsheets than we should. Because the computer says so, and computers don't make mistakes, we think that the result must be right.
That is, a spreadsheet becomes "truth", losing any uncertainty that it may have had while being developed. The phenomenon of abstract results gaining a sense of concreteness and reliability beyond what is reasonable, is called reification.
Guidelines:
- Research shows that 95% of spreadsheets have errors
- Humans error rate is 1% to 5% per task, but spreadsheet errors cascade
Really, that statistic needs to be repeated: 95% of spreadsheets have errors.
This is not just a random, made-up statistic. Extensive research into spreadsheets has consistently found that almost all spreadsheets have errors.
Large spreadsheets are more likely to have errors, meaning that they're almost certainly wrong.
