Spreadsheet good practice guidelines
What we need to do
Having established Why good practice matters, what do we need to do?
The answer is that we need a more deliberate approach to building, using, and managing spreadsheets. This means an approach focussed on applying good practices with the goal of making better spreadsheets that are easier to use and have fewer errors.
Based on research and practical experience, the prescription for making better spreadsheets is clear. That is:
- Learn from software developers.
- Spreadsheet users need to recognise the risk.
- Managers need to recognise the risk.
- Control the development lifecycle of critical spreadsheets.
- Adopt good development practices to reduce risk.
- Enhance spreadsheet training to focus on quality.
- Test, test, test.
Guidelines:
- Spreadsheet formulae are a form of programming language
- Software development issues and lessons apply to spreadsheets
When looking to understand how to make better spreadsheets, we need to be clear that spreadsheet formulae are a programming language.
Professional software developers learnt long ago that they need to apply good practices to their work. Spreadsheet developers need to learn and apply the same lessons.
Guidelines:
- We need to apply software engineering principles to spreadsheets
- Rules are too rigid, so we define guidelines
Excel formulae are a programming language, so we need to learn from our software development colleagues.
Software development went through a crisis in the 1970s to the 1990s, with high-profile delivery failures resulting from the difficulty of writing reliable computer programs within time and budget constraints.
Spreadsheet development has the same issues, but we have not learnt the same lessons.
Guidelines:
- Feedback from testing your spreadsheets can be sobering
- Training is an effective way to reduce overconfidence
Spreadsheet users need to recognize that spreadsheet error is a real and substantial risk. This is difficult to do, as we are generally overconfident about the quality of our spreadsheets, believing that they are much better than they really are.
One way to help users recognize spreadsheet risk is to have them test their own spreadsheets. They are often shocked by what they find. Finding errors can be a very sobering experience.
Guidelines:
- A manager's role is to mitigate spreadsheet risk and overconfidence
- Apply a positive feedback mechanism to reinforce and improve spreadsheet practice
Like spreadsheet users, managers are overconfident about the quality of the spreadsheets that they rely on for making decisions. Therefore, managers also need to recognize that spreadsheet error is a real and substantial risk.
As motivation, managers should be aware that they are accountable for any spreadsheet problems that occur.
