10 principles for good spreadsheet practice – Part 2

This two-part series is based on an ICAEW guide, 20 Principles for Good Spreadsheet Practice, which can be downloaded from this page.

These principles will not only reduce the possibility of error in your documents but help to combat waste arising from spreadsheets that are created inefficiently or carelessly.

So take all of these on board and you’ll be able to introduce best practice across your team or organisation and increase productivity and efficiency.

Without further ado, here are the final ten principles:

1. Be consistent in structure

Use the same columns for the same items in each workbook, especially when working with time series. A consistent convention within a workbook reduces the risk of error where one sheet refers to another.

2. Be consistent in the use of formulae

On any worksheet use the smallest practicable number of different formulae. Where it’s necessary to use many formulae, ensure that groups of cells using different formulae are clearly separated.

3. Keep formulae short and simple

Shorter formulae are easier to build, less likely to contain errors, easier to understand and review. Stage a calculation through multiple cells rather than build a long, complex formula.

4. Never embed in a formula anything that might change or need to be changed

Instead, put such values into separate cells and reference them. This ensures that values enter the spreadsheet only once, and if change is needed it would happen in just one place.

It also allows for all formulae cells to be locked without denying access to input values.

We can fix a reference by inserting ‘$’ signs in the cell reference before the column reference (so B becomes $B) and before the row reference (3 becomes $3).

Thus if the reference =B3 is replaced by ‘=$B$3’ the link will be fixed in this way.

5. Perform a calculation once and then refer back to that calculation

Do not calculate the same value in multiple places (except perhaps for cross checking purposes). This reduces risk of error, and is more efficient, since fewer calculations are being performed.

6. Avoid using advanced features where simpler features could achieve the same result

In particular, avoid using programming code unless necessary – in which case, ensure that it’s clearly documented within the code itself, as well as in a documentation worksheet.

Similarly, avoid circular references, and control and document any exceptions. Do not change the software’s key default settings (for example, do not turn off automatic recalculation) unless essential, in which case include a prominent message to warn users.

7. Have a system of backup and version control, which should be applied consistently within the organisation

The appropriate levels of backup and version control will depend on the organisation and the nature of the work, but there should always be, at the very least, a reliable means of preserving, identifying and restoring earlier versions of a workbook.

8. Rigorously test the workbook

The level of testing required will depend on the size, complexity and criticality of the workbook, with riskier workbooks needing a greater degree of independent testing.

The examples above illustrate the use of ‘trace precedents’, which shows all the cells which affect the value of the currently selected cell and ‘trace dependents’, which shows all the cells containing formulae that refer to the active cell.

9. Build in checks, controls and alerts from the outset and during the course of spreadsheet design

These checks might include, for example, tests to ensure that a balance sheet balances, assets do not depreciate below zero, and so on.

One approach would be to build in a set of audit tests to check validity and use flags to signal compliance or non-compliance. Use a master flag to summarise all the individual flags and place it prominently (on the output sheet, or even throughout the workbook eg, on sheet headers) so that users are bound to see it.

10. Protect parts of the workbook that are not supposed to be changed by users

The level of protection will vary according to the nature of the spreadsheet and the kind of use/users it will have. It might include locking whole worksheets, all cells containing formulae, or everything except designated input cells.

Read more from AAT Comment:

• Avoid mistakes with protect sheet in Excel
• Excel tips: Save time with named ranges
• Master the VLOOKUP function in Excel

Alan Gurney is AAT Comment’s Excel tips writer.