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.
Instant Connection to an Excel Expert
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.
Leave a Comment