Tracing Formulas in Excel 2003

When errors occur in worksheet formulas it can be difficult to diagnose where the problem lies. It is not always the cell directly referenced by the formula causing the problem. There are a number of auditing tools provided by Excel to assist you in identifying the cause of a formula error.

Displaying formulas

Excel displays the result of formulas by default. It can be useful when tracing errors to be able to see all the formulas on a worksheet, so you can see how they are put together.

1. Click Tools > Options
2. Click the View tab in the Options dialogue box
3. Check the Formulas checkbox in the Window options panel
Formulas are shown in the worksheet and the columns widen where necessary to accomodate them.

When the formulas are displayed, select a cell containing a formula to see coloured borders appear around any cells referenced by the formula.

Uncheck the Formulas checkbox in Options to hide the formulas.

Tracing precedents and dependents

Tracing precedents and dependents show you what other cells are affected by or are affecting a certain cell. Arrows are displayed to show the precedent and dependent cells.

1. Select the cell containing the formula you want to trace
2. Click either the Trace Precedents or Trace Dependents button on the Formula Auditing toolbar

Trace Precedents: Displays arrows showing the cells affecting the selected cell.
Trace Dependents: Displays arrows showing the cells being affected by the selected cell.
Arrows appear showing cells related to the formula in the selected cell. Dots appear on the arrows to identify the specific cells. An icon is used to indicate precedents or dependents on another worksheet.
The image below is displaying the dependents of cell D11.

To remove arrows from the worksheet:

• >Click either the Remove Precedent Arrows, Remove Dependent Arrows or Remove All Arrows button on the Formula Auditing toolbar, or
The relevant tracing arrows disappear.

Using the Watch Window

The Watch Window allows you to monitor the values of certain cells as changes are made to worksheets. This can include cells from different worksheets and workbooks.

1. Click the Show Watch Window button on the Formula Auditing toolbar

The Watch Window appears ready for you to add the cells you want to watch.

2. Click the Add Watch button
3. Select the cell or cell range you want to watch and click Ok
The cells are added to the Watch Window.

When a cell value is changed on the worksheet, the Watch Window displays how it affects the cells in the other worksheet or workbook.
If you no longer need to track a certain cell. Select the cell in the Watch Window and click the Delete Watch button.