Authors
New Zealand Treasury
Abstract
Spreadsheets have become the pre-eminent application for staff dealing with numerical data in most organisations. Users with a high level of business knowledge now have access to a powerful tool that is flexible and easy to use. Business processes are sometimes adapted to fit in with spreadsheets, as a result there is no practical alternative open to a user other than to use a spreadsheet.
Simple applications with no risk are ideal for spreadsheet development, however as the level of complexity and dependency on the resulting data increases so does the risk. Spreadsheets have inherently weak controls, especially in the areas of data validation, testing, and change control. Users while knowing how to use spreadsheets, do not always know the best ways to design and test models.
The trade-off between extra time developing a spreadsheet against the assurance in quality of data has to be made in view of the risks associated with that spreadsheet. Being aware of the risks will allow an informed user to make a decision on the extent to which best practice guidelines should be incorporated into a spreadsheet.
This document aims to highlight the main risk areas, and provide best practice guidelines in order for the user to make the correct trade-off decision.
The scope of these guidelines exclude macros, as modern macros tend to be more like programming languages. This is a separate practise area, and as such contains totally different guidelines and areas of risk.
Sample
Key best practices for spreadsheets include:
- Spreadsheets should have a consistent layout.
- Data input should be in the same order as the source.
- Put critical values in separate cells.
- Have a catalogue of spreadsheets in use.
- Document what tests were done and what the results were.
- Have the spreadsheet tested by someone other than the developer.
- Check spreadsheet validity by printing it out and then doing the test on the printout.
- Try to avoid complex formulae.
- When using the SUM function try to ensure that the range to be summed includes a blank cell at either end.
- incorporate validation checks on data input.
- Have a batch total to check the total of data input.
- Use formulas that foot and cross foot when summing data.
- Large columns of data should not contain sub totals and totals.
- If you have set-up an input area, print it out whenever the data changes.
- Protect all formulas and data that should not change.
- Every time you change the logic (workings) of the spreadsheet, change the name of the spreadsheet to reflect the change.
- With each change ensure you retest the spreadsheet and make sure you change the documentation.
Publication
1997, November
