Leading the way in Microsoft Office Development

What is Auditing ?

This is the name giving to examining your formulas and making sure they are generating the correct results.

Formulas can be very complicated, especially if you combine them with named ranges, absolute and relative references as well as links to other worksheets and workbooks.

Understanding and stepping through complex formulas can be a real headache but there are lots of features that you can use to make this easier.

There is also a feature that lets you identify the cells responsible for any errors returned by your formulas.

They can be accessed from the (Tools > Formula Auditing) sub menu and the Formula Auditing toolbar.

Trace Precedents - Draws tracer arrows from the cells that are directly referred to by the formula in the active cell.

Trace Dependents - Draws tracer arrows from the cells that have formulas directly referring to the active cell.

Trace Error - Draws tracer arrows from any cells that contain error values.

Remove All Arrows - Removes all tracer arrows from the active worksheet.

Evaluate Formula - Displays the Evaluate Formula dialog box which allows you to break up your nested formulas into smaller parts and evaluate then separately.

Show Watch Window - Displays the Watch Window dialog box which allows you to monitor the output generated from formulas as the data in your workbook changes.

Formula Auditing Mode - This toggles your active worksheet between the value layer and the formula layer. (Tools > Options)(View tab, "Formulas").

Show Formula Auditing Toolbar - Displays the Formula Auditing toolbar.

What are Tracer Arrows ?

Using tracer arrows will allow you to quickly identify the following information:

1) All the cells that are used in a particular formula (these cells are called precedents).

2) All the formulas that refer to a particular cell (these cells are called dependents as the value of the formula depends on the value in this cell).

3) All the cells that contain errors (e.g. #DIV/0), #VALUE! etc).

This information will be displayed on the worksheet as blue coloured arrows. The arrows always point in the direction of the data flow.

The tracer arrows are considered to be objects so they will not be displayed if the following option is selected (Tools > Options)(View tab, "Hide all").

The Evaluate Formula dialog box

This allows you to break up the formulas into smaller pieces.

Press Step In to separate a formula into smaller chunks

The Watch Window

This modeless dialog box allows you to view the contents of any cell and the results of any dynamic formulas as you make changes to other parts of the worksheet (or even other workbooks).

Lets you keep an eye on particular cells as you make changes to other parts ?

This provides remote viewing of multiple cells on any open worksheet and provides dynamic data about each formula, including its location and its resulting value.

Formula Auditing toolbar

This toolbar can be displayed by selecting (Tools > Formula Auditing > Show Formula Auditing Toolbar).

Alternatively you can select (View > Toolbars > Formula Auditing).

For more details on this toolbar, please refer to the Formula Auditing Toolbar page.

Error Checking dialog box

Double click on the arrows to move between the linked cells.

When you press F2 the precedents of the active cell are highlighted in Blue if the following (Tools > Options)(Edit tab, "Edit directly in cell) is not checked.

Things to Remember

It is not possible to use any of the auditing features if the worksheet is protected.

Any tracer arrows are lost when a workbook is saved or closed. The arrows will also disappear if you insert or delete any rows, move, insert or delete any cells used in the formulas or if you change the formula (any formula ?).