Excel > Excel 2007 Advanced > Auditing Worksheets

Tracing Formulas

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 the Formulas tab on the Ribbon
  2. Click the Show Formulas button in the Formula Auditing group
  3. 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.

Display formulas on the worksheet

Click on the Show Formulas button again to hide 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 the Formulas tab on the Ribbon
  3. Click either the Trace Precedents or Trace Dependents button in the Formula Auditing group
  4. 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.

    Trace dependents

To remove arrows from the worksheet:

  1. >Click the Remove Arrows button in the Formula Auditing group
  2. Remove arrows

  3. Select Remove Arrows, Remove Precedent Arrows or Remove Dependent Arrows
  4. 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 Formulas tab on the Ribbon
  2. Click the Watch Window button in the Formula Auditing group
  3. The Watch Window appears ready for you to add the cells you want to watch

    Excel watch window

  4. Click the Add Watch button
  5. Select the cell or cell range you want to watch
  6. The cells are added to the Watch Window

    Watching cells on other worksheets

    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

Follow us on

Facebook  Twitter  You Tube