Search Not Just Numbers

Tuesday 19 November 2013

Twenty Principles for Excel Good Practice (from the ICAEW)

You may remember that back in May, I told you about some work I was doing as part of the ICAEW IT Faculty's Excel Community Advisory Committee. The other committee members and I have been working towards a set of Excel Principles to be published by the ICAEW (the Chartered Accountancy body for England and Wales),

In my post back in May, I posted my initial contribution. This was then merged with the contributions of the rest of the committee. Over the past few months these have been argued about, compromised, honed and, in no small part due to the diplomatic skills of Paul Booth of the IT Faculty, improved, to arrive at a set of Twenty Principles that the whole committee can get behind.

This process has now reached the stage where the principles can be released into the wild, to get the views of the wider Excel world.

I have reproduced the headline principles, as they currently stand, below, but I would strongly recommend that you visit the ICAEW's IT Counts website, where you can download the full document, including explanations and examples illustrating each principle, as well as join in the debate.

THE TWENTY PRINCIPLES IN BRIEF 

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.



Excel Expert Course

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

4 comments:

  1. Kathie Gordon-Brooks19 November 2013 at 17:10

    I think this could be titled "Principles of running a Business" - change a few words and the tips become universal. Thank you!

    ReplyDelete
    Replies
    1. Thanks. Your comment suggests we're on the right track!

      Delete
  2. This is a good article, although some rules crafted around (i) The automatic update of links (ii) Documentation of underlying reports used in the given document and (iii) Ensuring all documents are edited using a consistent version (i.e 03/07/10) of excel would also counter frequent issues.

    ReplyDelete
    Replies
    1. Thanks Paul. We got into some of those areas, but most of them come under some of the broader principles (most of those would be covered in the standard set in point 2.

      Delete