😲 Nearly 90% of spreadsheets contain errors and many are also hard to use!
💰 These can be expensive in terms of money, time and reputation.
So how can you avoid these issues?
For my new book I went back to basics and completely overhauled my best practice advice. The result is my new, improved…
🥇 Gary’s Golden Ground Rules
These will help ensure that your spreadsheets are clear and easy to follow, which in turn reduces the risk of error during both development and usage.
1. Use a clear, logical workbook structure
Decide what worksheets you need, give them clear names and get them in order.
- Spread content logically over worksheets
- Organise worksheets into sections to reflect data flow from inputs to outputs
- Include a cover sheet, ideally with hypertext links to facilitate navigation
- Separate inputs, calculations and outputs
2. Keep your worksheets as clear and simple as possible
Ensure each worksheet is clearly laid out and easy to use.
- Use a logical structure within each worksheet such as
- Use consistent columns, row, formulas and whole sheets
- Formulas
- Ensure calculations flow from left to right and from top to bottom
- Use the KISS principle: Keep It Short and Simple
- Follow formula priorities: (i) correct, (ii) understandable and (iii) short
- Follow the COUNT principle: Calculate Once, Use Numerous Times
3. Use a clear, clean, consistent design
Ensure the whole workbook has a clear, professional look and feel.
- Use consistent (company-defined) fonts, colours, styles and data formats
- Use the four key design principles: contrast, repetition, alignment and proximity
- Clearly mark all inputs, for example as grey cells
- Minimise non-data ink such as borders
4. Restrict access, inputs and changes
Ensure that only authorised users can access and change your spreadsheet and that inputs are valid.
- Restrict access to the spreadsheet using folders with restricted access and passwords
- Use data validation where relevant to help ensure inputs are valid - see my video for details
- Consider protecting workbooks so that changes can only be made in input cells - see my blog for details
5. Write instructions for users
Ensure that users know what to do and in what order e.g., how to update the model with new data.
- Create a worksheet with an action list for users to follow
- Add a status column to ensure progress is clear
Interested to find out more? My book contains lots more details and plenty of examples and screenshots. Check it out on Amazon.
