Twenty principles for good spreadsheet practice
Twenty principles for good spreadsheet practice aims to reduce the amount of time wasted and the number of (sometimes costly) errors caused by businesses (including accountancy practices) as a consequence of how they and their employees use spreadsheets.
Preface by Michael Izza
Like it or not, spreadsheets are in use everywhere. They have become the lingua franca of business; no matter what your system or requirement, a spreadsheet can connect people like no other business tool.
However, the use of spreadsheets is not without risk, and approximately 90% of spreadsheets contain mistakes. Material errors such as incorrect models, sending out sheets with hidden columns or careless use of formulae, have been well publicised alongside the embarrassment and financial loss that arise as a result.
In addition, there is a serious problem of waste arising from spreadsheets that are created inefficiently or carelessly. 65% of members of the Excel Community are self-taught, and with no formal methodology there is a risk that sub-optimal models and processes become the norm.
This is why ICAEW’s Excel Community Advisory Committee came together to develop Twenty principles for good spreadsheet practice that look to reduce spreadsheet risk and inefficiency in all organisations regardless of size or sector.
I would like to thank all the members of the committee for developing these principles, and would encourage readers to act on the lessons laid out below.
Chief Executive Officer, ICAEW
The Twenty Principles
The spreadsheets business environment
1. Determine what role spreadsheets play in your business, and plan your spreadsheet standards and processes accordingly.
It is fundamental to consider what part spreadsheets play and how they are used. You need to consider how to create, implement, and communicate a plan of best practice. If you only use spreadsheets sparingly, then the Twenty Principles themselves are enough of a standard. But in a spreadsheet-heavy environment like a financial modelling organisation, then a more formal modelling standard might be more appropriate.
2. Adopt a standard for your organisation and stick to it.
This is one of the fundamental ideas that drives the Twenty Principles. Good standardisation is useless if it isn’t considered in the context of your organisation and what makes sense for what you’re trying to achieve with spreadsheets. The standard should include, among other things, consistent conventions on use of cell formatting.
3. Ensure that everyone involved in the creation or use of spreadsheets has an appropriate level of knowledge and competence.
Spreadsheets, no matter how well designed, are only as good as their users. For anyone designing, developing or maintaining (as distinct from just using) a spreadsheet, this will include: awareness of the range of functions available, clear understanding of such basic concepts as relative and absolute cell references, and an appreciation of the importance of carefully checking the results of functions.
The Spreadsheet Competency Framework is a free resource to help you identify and classify spreadsheet knowledge.
4. Work collaboratively, share ownership, peer review.
The extent of collaboration and review needed will depend on the size and complexity of your organisation and of each project. It’s important to coach reviewers; however, don’t provide instructions beyond what the spreadsheet‘s purpose, as not all users will have the benefit of your personal training.
Designing and building your spreadsheet
5. Before you start, satisfy yourself that a spreadsheet is the appropriate tool for the job.
Excel is a spreadsheet program and has its limitations – there are plenty of cases where other dedicated software will be more appropriate. A particular weakness of Excel is in its printability and it’s also not a useful word processor. Keep Excel for the data- and analysis- driven applications at which it truly excels.
6. Identify your audience. If a spreadsheet is intended to be understood and used by others, the design should facilitate this.
Remember that every spreadsheet of any meaningful size will sooner or later have to be read by someone that isn’t familiar with it. That could even be you, six months later when you’ve forgotten all about it. Keep the user in mind and make their experience of interacting with your spreadsheet as clean and simple as possible. Build in formatting and explanatory notes that help the user to find what they need and use the spreadsheet readily.
7. Include an ‘About’ or ‘Welcome’ sheet to document the spreadsheet.
Large, complex models – and more modest ones – benefit greatly from usability improvements. There are several key pieces of information you should include in any spreadsheet of moderate complexity, such as the title and purpose of the workbook the creator’s name, the version number, and a basic guide to how to find and update key inputs to the calculations. A little documentation can go a long way to helping others follow your work.
8. Design for longevity.
Design spreadsheets to adapt to any reasonably foreseeable future changes in values (tax rates, etc) or volume (eg, items in a data set) of data used in calculations. However, the need for adaptability should be balanced against following the agile principle of 'the simplest thing that could possibly work’.
9. Focus on the required outputs.
Work backwards: be clear about the purpose of the spreadsheet, what outputs achieve that purpose and therefore what inputs and logic are required to derive the outputs. For example, don’t add unnecessary diagrams or analysis that don’t add anything to the main message.
10. Separate and clearly identify inputs, workings and outputs.
Clear structure is absolutely vital to helping future users of your spreadsheet. Having a separate home for inputs and outputs means that users know how to, for example, edit a model’s assumptions, and won’t miss an input hidden among an output sheet. As well as physically separating input and output cells, also create a common formatting key to identify each element.
11. Be consistent in structure.
Laying out your spreadsheets in a clear, consistent manner is another key element in helping the end user to understand and interpret your work. Keep the same direction of work – say, adding new time series horizontally and new products vertically as this also helps with formula building. Wherever possible, work with your colleagues to define standard layouts and templates, allowing easier review and sharing of work due to familiarity.
12. Be consistent in the use of formulae.
On any worksheet use the smallest practicable number of different formulae. Where it is necessary to use different formulae, ensure that groups of cells using the different formulae are clearly separated. For example, do you use FALSE or 0 for VLOOKUP? Do you use multiply by 1 for converting text to numbers, or the VALUE function, or something else? Are IF functions written with line breaks? If possible, liaise with colleagues to try and build a common consensus.
13. Keep formulae short and simple.
Shorter formulae are easier to build (and therefore less likely to contain errors) and easier to understand and to review. Stage a calculation through multiple cells rather than build a long, complex formula.
14. Never embed in a formula anything that might change or need to be changed.
Instead, put such values into separate cells and reference them. This ensures that values enter the spreadsheet only once, and if change is needed would happen in just one place. It also allows for all formulae cells to be locked without denying access to input values. If cross-sheet references are making your formulas hard to follow, consider the use of named cells to help clarify them.
15. Perform a calculation once and then refer back to that calculation.
Do not calculate the same value in multiple places (except perhaps for cross checking purposes). The safer and better practice is to build the calculation you need only once in most cases, and use a direct cell reference or named range to pull through the number anywhere else it’s needed in the workbook.
16. Avoid using advanced features where simpler features could achieve the same result.
In particular, avoid using programming code unless necessary – in which case ensure that it is clearly documented within the code itself, as well as in a documentation worksheet. Similarly, avoid circular references, and control and document any exceptions. Do not change the software’s key default settings (for example, do not turn off automatic recalculation) unless essential, in which case include a prominent message to warn users.
Spreadsheet risks and controls
17. Have a system of backup and version control, which should be applied consistently within an organisation.
Save early and save often. Consider alternative backups options in case the file is corrupted, inadvertently deleted, or otherwise lost. Wherever relevant, include a version number in the filename of a workbook right from the start, and instil strict habits in your organisation that any substantial changes must be done in a separately numbered version.
18. Rigorously test the workbook.
Properly test your own workbooks and peer review spreadsheets of significance. For example, try putting outlandish inputs into the workbook to see if the expected swinging changes occur. You can also sketch out the structure of the workbook to see if it all makes sense and the flow of information matches your expectations.
19. Build in checks, controls and alerts from the outset and during the course of spreadsheet design.
Features such as error checks, data validation, user alerts, and other error-proofing aren’t just nice-to-haves, but are essential elements of any good spreadsheet. Add them as you build a spreadsheet, so you can make sure that you are actively considering error-proofing and controls in each and every part of the spreadsheet, and look for opportunities to build in vital functionality. That way you can also test both the spreadsheet and the controls incrementally, helping you to catch any design errors as you go.
20. Protect parts of the workbook that are not supposed to be changed by users.
Worksheet protection, whether it be achieved by locking cells or through the use of data validation, is essential to keeping users from changing things they aren’t intended to, either on purpose or by accident. Keeping formulas protected, inputs restricted to appropriate types etc, can help the user to avoid stumbling or making errors, and can also ensure that any formula changes are done only in collaboration with the original author.
Recognition of spreadsheet products
The IT Faculty has developed a scheme whereby spreadsheet standards, and other products and services such as training, can be formally recognised as compliant with the Twenty Principles.
So far, the following products have achieved recognition:
Any organisations interested in seeking recognition should contact the IT Faculty.