Excel is often at the heart of business decisions big and small. The modern day business runs on data and in a lot of companies, spreadsheets and specifically Excel is the tool people use to manipulate their data. Excel is brilliantly accessible, people can start creating simple models and manipulating data sets with some basic training.
This accessibility is also a limitation of spreadsheets as with freedom for someone to do what they want with data comes the possibility that they will get things wrong. Spreadsheet errors regularly make the news (at the least the news we watch) and costs businesses around the world both big and small, a huge amount each year.
If only there was a document that helped outline some spreadsheet best practice to help users and companies design and build quality, error free spreadsheets…
Hang on - here comes the 20 Principles for Good Spreadsheet Practice from the ICAEW (Institute of Chartered Accounts in England & Wales)!
The 20 Principles are a guideline for spreadsheet users that aim to ‘help reduce the amount of time wasted, and number or errors caused, by businesses… as a consequence of the way they and their employees use spreadsheets’. The Principles themselves were crafted by the ICAEW Excel Community Advisory Committee – a group of spreadsheet experts taken from the Accounting and Business sector, of which Filtered’s co-founder, Vinit Patel was one.
The event to launch the 20 Principles took place on 17th June 2014 at Chartered Accountants Hall, London and featured speakers from BDO, Fairfield Energy, Microsoft and Mazars. As providers of Excel training to the ICAEW Excel Community and sponsors of the 20 Principles Filtered was also on hand to demonstrate how members could make the most of their online training.
The event at the ICAEW
So should you carefully consider these 20 different principles each time you open Excel? Probably not, as that simply isn’t practical. Instead, read them, understand why they are important and bear them in mind when constructing a model, analysing a data set or creating a workbook that is likely to be used by others.
The event captured by F1F9 and FAST in a short film
Below are the 20 Principles in brief but you can download and keep the full document here.
The 20 Principles for Good Spreadsheet Practice
The spreadsheet’s business environment
1. Determine what role spreadsheets play in your business, and plan your spreadsheet standards and processes accordingly.
2. Adopt a standard for your organisation and stick to it.
3. Ensure that everyone involved in the creation or use of spreadsheets has an appropriate level of knowledge and competence.
4. Work collaboratively, share ownership, peer review.
Designing and building your spreadsheet
5. Before starting, satisfy yourself that a spreadsheet is the appropriate tool for the job.
6. Identify the audience. If a spreadsheet is intended to be understood and used by others, the design should facilitate this.
7. Include an ‘About’ or ‘Welcome’ sheet to document the spreadsheet.
8. Design for longevity.
9. Focus on the required outputs.
10. Separate and clearly identify inputs, workings and outputs.
11. Be consistent in structure.
12. Be consistent in the use of formulae.
13. Keep formulae short and simple.
14. Never embed in a formula anything that might change or need to be changed.
15. Perform a calculation once and then refer back to that calculation.
16. Avoid using advanced features where simpler features could achieve the same result.
Spreadsheet risks and controls
17. Have a system of backup and version control, which should be applied consistently within an organisation.
18. Rigorously test the workbook.
19. Build in checks, controls and alerts from the outset and during the course of spreadsheet design.
20. Protect parts of the workbook that are not supposed to be changed by users.
If you enjoyed this post then join us for our free webinar on 'Data Analysis in Excel 2016', Tuesday 19 January at 4.30pm GMT.
Filtered's course author and Excel expert Simon Hurst will take you through:
- Power Pivot
- Visualisation features including Power Map
- Recent additions and developments
The webinar will last 30-40 minutes and you'll be able to ask questions throughout. Register here for free.
Filtered is an online education platform which customises learning material for each user. By asking trainees questions at the outset, our platform’s algorithm filters out anything a user doesn’t need, or already knows.