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.

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):

• 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:

Would you like to learn more?

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.

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:

• 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:

These codes may be combined with additional indicators:

< 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 document accompanied by Excel worksheets

Click here for details about obtaining this file. It has been rewritten for Excel 2010.

file: xltestingandmapping.htm

© meadinkent.co.uk 2015

CMIDX S7 P2 Y

Last updated Feb14