MeadInKent
| Feedback | Spreadsheet design tips |
Excel 2010

Using Excel tools to audit spreadsheets

On this page ...

 [Finding links to other workbooks] - [Highlighting cells containing formulas] - [Comments in cells] - [Cell or range names]

Errors and design flaws are very common in spreadsheets and it is important to be aware of steps that can be taken to design more reliable and robust models. Excel includes some tools which can be used to investigate spreadsheets and clarify the processes and calculations being performed.

Formula auditing

The Excel auditing tools

A section of the Formulas ribbon contains tools to help interrogate cells and worksheets to find the order and flow of data through a model.

Trace Precedents - if the current cell contains a formula, arrows will be drawn leading back to the source cells.

Trace Dependents - arrows will be drawn from the current cell to any other cells that incorporate it into their formulas.

Remove Arrows - either remove all arrows or just those associated with precedents or dependents.

Show Formulas - displays all formulas in the current worksheet.

Data validation dialog box

Error Checking - contains 3 options. Error Checking will identify any cells which contain errors such as #VALUE! and help you fix the formulas. If you select a cell containing an error, Trace Errors will display arrows to precedents.  The Circular References option will list any cells containing formulas which use a range that includes that same cell.

Evaluate formula - a dialog box helps you analyse the parts of a complex formula.

Other tools which were included on the Auditing toolbar in previous versions of Excel are now to be found in different ribbon locations.

Adding a comment to the current cell ([Review] Comments | Insert Comment) lets you describe the purpose of a formula or to note the source of piece of information. A comment will only appear when the mouse cursor hovers over a particular cell and cells containing a comment are marked with a small red triangle in the top right hand corner of a cell. To Edit or Delete a comment, select the option from the smart menu that appears when you click on the cell with the right mouse button.

Data validation - Rules can be defined which specify appropriate values for a range of cells ([Data] Data Tools | Data Validation). Once these have been set, users will be prevented from entering inappropriate values. The ribbon option to Circle Invalid Data will highlight any existing cell values which fail Data Validation rules that have been defined for the current range. Although the validation rules prevent you from typing an illegal value into a cell, a formula may produce an exceptional value. For example, if all values in a range must be less than 50, a total of several other values may break the rule.

Would you like to learn more?

book

Excel 2010 Bible by John Walkenbach (with CD)

Finding links to other workbooks

Occasionally a spreadsheet opens and a dialog box informs the user that the 'workbook contains links to information in another workbook ... Do you wish to update ...'. This can be perplexing if you have no idea which cell in your model is linked. The ribbon option [Data] Connections | Connections lists the source of any links. Another way of finding an external link is to select one or more worksheets and then choose [Home] editing | Find and Replace. In the Find dialog box type .xls and then <Find Next>.

Highlighting all cells containing formulas

It may improve the clarity of your spreadsheet if all cells containing formulas are coloured in a particular way. Select [Home] editing | Find and Replace | Formulas. All such cells on the current sheet will be selected. Then choose a fomatting option to add any font, border or patterns to the selected cells.

Defining cell or range names

cell B3 has been given the name 'My_Int_Rate'

A spreadsheet can be made much easier to understand and audit by using named cells to define important and commonly used variables. Numbers representing values such as the inflation rate, the current period number or a tax rate can all be given names which are then used in formulas instead of a distant cell reference.

To name the current cell (or range) simply type a name into the 'Name Box' (located to the left of the formula bar). Cell names can also be added, modified and deleted from the ribbon section [Formulas] Defined Names. A name can then be used throughout your spreadsheet as an alternative to the cell reference. E.g. = SUM(F20:F40) * My_Int_Rate.

[Tips on design and a tool for checking spreadsheets ...]

help with excel 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: excel-audit.htm meadinkent.co.uk 2014 Last updated Feb14CMIDX S7 P1 Y View a selection of recommended books on Excel