I recently received a link to an article from a fellow Excel fan in the UK that detailed 20 principles for good spreadsheet practices. I read through the article and I have to say they’ve done an excellent job of listing them. I am constantly asked to repair or redesign spreadsheets that adhere to few if any of these principles.
The CEO of ICAEW (The Institute of Chartered Accountants in England and Wales) Michael Izza states that approximately 90% of spreadsheets contain mistakes in either their design or structure and that 65% of members of the Excel community are self-taught, with no formal methodology. I would venture a guess to say that the figures for the Canadian community are not too dissimilar. I did search the BC and Alberta equivalents of ICAEW but couldn’t find any similar data or information published.
Some of the principles are self-explanatory while some need a more detailed explanation which you can find in this document.
In brief the principles are:
(I just love numbers 🙂 – I can help everyone with that one!)
- Determine what role spreadsheets play in your business, and plan your spreadsheet standards and processes accordingly.
- Adopt a standard for your organisation and stick to it.
- Ensure that everyone involved in the creation or use of spreadsheets has an appropriate level of knowledge and competence.
- Work collaboratively, share ownership, peer review.
- Before starting, satisfy yourself that a spreadsheet is the appropriate tool for the job.
- Identify the audience. If a spreadsheet is intended to be understood and used by others, the design should facilitate this.
- Include an ‘About’ or ‘Welcome’ sheet to document the spreadsheet.
- Design for longevity.
- Focus on the required outputs.
- Separate and clearly identify inputs, workings and outputs.
- Be consistent in structure.
- Be consistent in the use of formulae.
- Keep formulae short and simple.
- Never embed in a formula anything that might change or need to be changed.
- Perform a calculation once and then refer back to that calculation.
- Avoid using advanced features where simpler features could achieve the same result.
- Have a system of backup and version control, which should be applied consistently within an organisation.
- Rigorously test the workbook.
- Build in checks, controls and alerts from the outset and during the course of spreadsheet design.
- Protect parts of the workbook that are not supposed to be changed by users.
Feel free to explore this yourself at http://www.icaew.com/en/technical/information-technology/excel/twenty-principles
Send me an email or leave a comment if you want to discuss the list. It would be interesting to hear some Canadian views. Could we add to this list maybe?