As part of my role on the Institute of Chartered Accountants in England and Wales (ICAEW) Excel Community Advisory Committee, I (along with the other members) have been asked to share my personal principles for Excel good practice – and I thought you might be interested to see them too.
So here goes…
My Ten Excel Principles:
Always separate out the three stages – Obtaining the data, Analysing the data, Presenting the data (OAP). This makes your spreadsheet far more flexible and easy to follow.
Never include numbers in formulae – always enter the variable in a cell and refer to the cell in the formula. This makes the spreadsheet easy to follow and to update when variables change.
Don’t fiddle with the data – when using data from elsewhere, don’t edit it, report on it in other sheets. This minimises errors and increases efficiency.
Use data validation where possible, to minimise data entry errors, and to make it easier to analyse and report on the data.
Always think “Is there a way I could do this that would save me time next time?”
Lay out data to suit Excel – not to suit the eye. Every heading, subtotal or empty column you place among the data makes it far less useable.
Get used to using the dollar signs when you refer to cells. Used well, you can write a formula once and copy it everywhere – Saving time and reducing errors.
Protect cells with formulae in them – it can be very difficult to find an incorrect (or missing) formula caused by accidental typing.
Use colour and formatting (as well as descriptions) to make it obvious where data entry is required.
Avoid merging cells unless absolutely necessary – merged cells make a spreadsheet far harder to edit.
I’m sure I will have missed a few key ones – these are very much my personal ones – and I would love to hear yours in the comments.