Trace dependents in Excel

  • Post author:
  • Post category:Uncategorized

Dependent cells are cells whose values depend on the active or selected cell.  Trace Dependents is a built-in feature in Excel that helps us easily understand how a specific cell affects the values in other cells.  

Figure 1.  Final result: Trace dependents

The above image shows the dependent cells of C4 and C9.  

Trace dependents

In order to trace dependents, we follow these steps:

  • Select cell C4
  • Click Formulas tab > Trace Dependents or use the keyboard shortcut  Alt + M + D

Figure 2.  Trace Dependents button in Formulas tab

Figure 3.  Output:  Trace Dependents

One blue arrow and one dashed black arrow are shown attached to cell C4.  Blue arrows point to cells within the worksheet that are dependent in value to the selected cell.  Black arrows point to a worksheet icon, which means that there are dependent cell or cells in a different worksheet.  

In our example, one blue arrow points from C4 to C9.  This means that the value of C9 depends on C4. This is supported by the formula for C9 which is =SUM(C4:C8).

Figure 4.  Tracer arrow pointing to dependent cell

The black arrow, on the other hand, points to a worksheet icon.  We have to double-click the black arrow to show the cells in another worksheet that depend in value to C4.  

Figure 5.  Black tracer arrow pointing to worksheet icon

After we double-click on the black arrow, the Go To dialog box will appear on screen.  It shows the reference to the worksheet and cell that is dependent on our selected cell.  

Figure 6.  Go To dialog box

Click on the reference name and click OK.  We will then be redirected to the other worksheet, selecting the dependent cell.  

Figure 7.  Dependent cell in another worksheet

Trace next level of dependents

In order to identify the next level of dependents to our active cell, we have to click the Trace Dependents button again.  

Below image shows blue arrows pointing from cell C9 to G9.  G9 is the grand total which sums the total for each set of data in C9, D9 and E9.  

Figure 8.  Tracer arrows to next level of dependents

Trace precedents

Conversely, we can also trace precedents through the Trace Precedents button.  Suppose we want to retrace our steps and identify the cells affecting the value of C9, we follow these steps:

  • Select cell C9
  • Click Formulas tab > Trace Precedents or Alt + M + P

Figure 9.  Output: Trace Precedents

A blue arrow is shown pointing from C4 to C9, and the range C4:C8 is enclosed in a box with blue borders.  This means that the precedents to C9 are C4:C8. This is validated by the formula in C9 which is =SUM(C4:C8).  

Remove tracer arrows

In order to hide all of the dependency tracer arrows at once, we click Formulas tab > Remove Arrows, shown below.

Figure 10.  Remove Arrows button in Formulas tab

If we want to remove only precedent arrows or dependent tracer arrows, we click the down arrow beside Remove Arrows and choose from the drop-down menu.