In the first of our two-part series we look at best practice spreadsheet principles to avoid costly mistakes. Another 10 principles will follow in the second post.
1. Determine what role spreadsheets play in your business, and plan your spreadsheet standards and processes accordingly
If you have spreadsheets that play a key or critical role in your organisation, ensure that they are developed and tested, managed and monitored to an appropriate level. Spreadsheets that form part of an organisation’s key business processes will need to be managed differently from ad hoc spreadsheets for short-term use by an individual.
2. Adopt a standard for your organisation and stick to it
This might be one that is developed in-house, or adopted from outside and shared with other organisations. A common standard within an organisation facilitates collaboration, aids understanding and saves development time. The standard should include, among other things, consistent conventions on use of cell formatting. This may be achieved by using the ‘cell styles’ feature as illustrated below.
3. Ensure that everyone involved in the creation or use of spreadsheets has an appropriate level of knowledge and competence
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.
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.
5. Before starting, satisfy yourself that a spreadsheet is the appropriate tool for the job
Spreadsheets are not the answer to every problem. A lot of time can be wasted, and errors caused, by using a spreadsheet when some other application would be more appropriate. Very often the more appropriate tool might be a word processor (if it’s a table of text), a database (if it’s processing large quantities of similar data items) or an existing software package (if it’s to undertake well-established processes, such as bookkeeping, for which specialist packages are readily available). Even if a spreadsheet is still the right answer it’s worth looking for existing templates before starting a new one from scratch.
6. Identify the audience. If a spreadsheet is intended to be understood and used by others, the design should facilitate this
If the only ‘audience’ envisaged is yourself, you might perhaps justify less explanation and help. Even so, good documentation is helpful if you come back to a spreadsheet a while after you created it; and many spreadsheets come to have a much wider audience than originally intended. Ensure that adequate instructions, validation and help are included to promote ease of use and avoid input errors. Even if parts of a spreadsheet are ‘locked’, keep calculations visible.
7. Include an ‘About’ or ‘Welcome’ sheet to document the spreadsheet
This should give such basic information as author, purpose, version number, and description of general approach. Also include explanations of colour codes and other formatting conventions, any sources of input data (with, where appropriate, hyperlinks to the original data), and any macros and what they do. The more complex the workbook, or the more it needs to be shared, the greater the requirement for good documentation. Conversely, a simple spreadsheet to be used only by the person who designs it might be less rigorously documented.
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’.
In the first example above, if an organisation were to add a new department, a new worksheet could be added anywhere between DeptA and DeptD (DeptC1, for instance), and there would be no need to change the formula as the new worksheet would automatically be picked up by the formula. In the second example above, the formula would need to be changed every time a new worksheet is added.
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.
10. Separate and clearly identify inputs, workings and outputs
A properly structured spreadsheet will be easier to understand and to maintain. If pivot tables are used, it may be possible to relax this principle, but clarity remains crucial. Design to ensure that any input should be entered only once.
AAT members have free access to this and many other Excel Tips through their MyAAT account.
Alan Gurney is AAT Comment’s Excel tips writer.