Display the Relationships Between Formulas and Cells

Sometimes checking formulas for accuracy or finding the source of an error can be difficult when the formula uses precedent or dependent cells:

Precedent cells
Cells that are referred to by a formula in another cell.

Dependent cells
Cells that contain formulas that refer to other cells.

To assist you in checking your formulas you can use the Trace Precedents and Trace Dependents commands to graphically display or trace the relationships between these cells and formulas with tracer arrows.

Trace cells that provide data to a formula (precedents)

Select the cell that contains the formula for which you want to find precedent cells.

To display a tracer arrow to each cell that directly provides data to the active cell.
Click on the Formulas tab.
In the Formula Auditing group click Trace Precedents.

Blue arrows show cells with no errors.
Red arrows show cells that cause errors.
If the selected cell is referenced by a cell on another worksheet or workbook a black arrow points from the selected cell to a worksheet icon.
The other workbook must be open before Excel can trace these dependencies. If the other workbook is not open Excel may prompt you to locate and open it.

To identify the next level of cells that provide data to the active cell click Trace Precedents again.

To remove tracer arrows one level at a time starting with the precedent cell farthest away from the active cell.
Click on the Formulas tab.
In the Formula Auditing group click the arrow next to Remove Arrows and then click Remove Precedent Arrows.
To remove another level of tracer arrows click the button again.

To remove all the arrows Click on the Remove Arrows command once.

Trace formulas that reference a particular cell (dependents)

Select the cell for which you want to identify the dependent cells.

To display a tracer arrow to each cell that is dependent on the active cell.
Click on the Formulas tab.
In the Formula Auditing group click Trace Dependents.

Blue arrows show cells with no errors.
Red arrows show cells that cause errors.
If the selected cell is referenced by a cell on another worksheet or workbook a black arrow points from the selected cell to a worksheet icon.
The other workbook must be open before Excel can trace these dependencies. If the other workbook is not open Excel may prompt you to locate and open it.

To identify the next level of cells that depend on the active cell click Trace Dependents again.

To remove tracer arrows one level at a time starting with the precedent cell farthest away from the active cell.
Click on the Formulas tab.
In the Formula Auditing group click the arrow next to Remove Arrows and then click Remove Dependents Arrows.
To remove another level of tracer arrows click the button again.

To remove all the arrows Click on the Remove Arrows command once.

If Excel beeps when you click Trace Dependents Button image or Trace Precedents Button image either Excel has traced all levels of the formula, or you are attempting to trace an item that cannot be traced.

The following items on worksheets that can be referenced by formulas cannot be traced by using the auditing tools:

  • References to text boxes, embedded charts or pictures on worksheets
  • PivotTable reports
  • References to named constants
  • Formulas located in another workbook that refer to the active cell if the other workbook is closed




Be the first to comment

Leave a Reply