Home Excel Access | Feedback | Auditing spreadsheets |

Spreadsheets may contain hidden errors. These can present a degree of risk whenever decision making is based upon your calculations. Follow these steps to reduce the risk of errors occurring.

Many studies by academics and accountancy firms have shown that a consistently high proportion of spreadsheets contain errors. Few companies recognise this enormous risk.

## Stage 1. Create well designed spreadsheets

Errors are easier to detect if the spreadsheet is well designed. It is best to create three distinct and separate sections (probably on different worksheets):

 Inputs for all user inputs and defined variables (constants) Calculations to process the inputs Outputs any reports and tables to be printed
• Ensure that there is a natural flow of information down each page, rather than jumping backwards and forwards.
• Use error checking formulae in adjacent cells (make use of the IF function).
• References are simplified when inputs are all contained in the same column.
• Keep formulas simple and have one unique formula in each row.
• Consider the effects of rounding and number formatting.
• Don't use statistical functions (such as Trend) unless you fully understand their limitations and correct application.

## Stage 2. Risk assessment

The level of risk will depend upon the size and complexity of the model and also the use to which it will be put. The highest elements of risk are:

Excel 2010 Bible by John Walkenbach (with CD)

• Models containing complex relationships between the inputs and outputs
• Outputs which are used to make critical decisions
• Outputs which are quoted in financial results
• The level of expertise of the person designing the spreadsheet
• The size of the model, particularly those with linked files
• The frequency with which the model is used

The greater the element of risk, the more thoroughly your spreadsheet should be tested.

## Stage 3. Testing

Models should be tested by somebody else, who has the sole aim of trying to prove that it doesn't work.

• If range names have been used, test that they are correct
• Test that formulae are entered and copied correctly
• Test that reports appear as intended when printed
• Are the results reasonable and as expected?
• How do the results compare with last months / previous versions

Excel auditing tools can help review the flow of data through your model.

Draw a spreadsheet map to identify whether formulae appear in the correct places and whether they are copied across columns correctly.

Maps can be a useful aid to testing large models containing numerous formulae and functions. By describing the contents of each cell it is easy to highlight cells which contain:

• number values which were thought to be formulae
• formulae which have not been correctly copied to other cells
• values in cells which have been missed out of ranges in formulas
• links to other worksheets need careful monitoring

I have created a spreadsheet called SheetMap containing a macro which will insert a worksheet into your existing spreadsheet. The new sheet will display an analysis of all cell contents using the following key:

 Code Meaning Example Tx Text Dr Smith Nu Absolute Number 3400 Ref Reference to another cell =B3 Ca Calculation =b4*0.13 Fn Function =SUM(B4:B7) LFn Long function =AVERAGE(D4:D7)*12

These codes may be combined with additional indicators:

 Code Meaning > Contents are copied from cell to the left ^ Contents are copied from cell above ! The cell refers to another worksheet (shading) Coloured cells have no dependents

 < The original. The map >

In the example above there are two hidden errors on the worksheet. The formula in column C has not been copied correctly from C6 to C7. Also, the pink shading in D4 indicates that this value is not referred to by any formula - i.e. it has no dependents. The SUM formula in cell D9 (which is shown in the first image) has mistakenly omitted the first value in D4.

If you are interested in obtaining a copy of SheetMap.xls, it is included with the Excel functions book below.

 Get this information as a documentaccompanied by Excel worksheets Click here for details about obtaining thisfile. It has been rewritten for Excel 2010.

 file: xltestingandmapping.htm © meadinkent.co.uk 2015 CMIDX S7 P2 Y Last updated Feb14