20 principles for good spreadsheet practice

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.

Michael Izza
Chief Executive Officer, ICAEW

The Principles are the culmination of several months’ work carried out under the auspices of the Excel Community Advisory Committee, which brings together a group of 17 expert Excel users with vast experience in a variety of roles across business and practices, large and small.

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.

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, SUMIFS can replace SUMIF and go further than it, so consider only using the more flexible version.  Likewise try to standardise the conversion of text to numbers – whether it be with a VALUE function or by multiplying by 1.

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. Allocating range names to cells containing these values can make it easier to understand the purpose of the formulae which reference 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.

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

The level of testing required will depend on the size, complexity and criticality of the workbook, with riskier workbooks needing a greater degree of independent testing.  Simpler workbooks can be tested through self-review or peer review, but critical workbooks might need a formal review process or even an external validator.

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 is essential to prevent users from changing things that should not be changed, either intentionally or accidentally. Protecting all formulae helps the user to avoid introducing errors, and can also ensure that any formula changes are only made in collaboration with the original author.