MeadInKent
| Feedback | Auditing spreadsheets |

Excel: Testing and Mapping your spreadsheet

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

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:

Would you like to learn more?

book

Excel 2010 Bible by John Walkenbach (with CD)

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.

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.

Click here for links to the European Spreadsheet Risk Interest Group and other Excel sites.

Testing your Excel Spreadsheets with a Map

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:

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

A worksheet containing hidden errors < The original.
The map >
A map of the worksheet exposing potential errors

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.

help with excel functions Get this information as a document
accompanied by Excel worksheets
Document is in PDF format Click here for details about obtaining this
file. It has been rewritten for Excel 2010.

file: xltestingandmapping.htm meadinkent.co.uk 2015CMIDX S7 P2 Y Last updated Feb14 View a selection of recommended books on Excel