When making changes to your spreadsheet, such as deleting or moving cells, you should be aware of the impact it can have on the cells that rely on that data. If you’re using data from a separate worksheet, the risk of error or miscalculation is even higher. Therefore, it’s crucial to carefully consider which cells may be affected before taking any actions that could disrupt your formulas.
You can use Trace Dependents to identify which formula is using a particular cell, within or outside the worksheet. Here, dependents are basically cells containing formulas that can change if a particular cell value changes.
As you can see, the feature displays the blue arrows leading you to dependent cells in the same worksheet. Similarly, the black dotted arrows redirect you to dependent cells in another worksheet.
Alternatively, you can also track the cells referring to a formula using the Trace Precedent feature.
How to Use Trace Dependents in Excel?
To understand the feature, let’s take a look at an example where we have calculated the total sales of a clothing store.
Here, every cloth has a 10% discount label. So, the After Discount price is the Total Price minus the 10% discount.
We also have another sheet “Month 2” that is currently using the total sales amount of the “Month 1” sheet in its formulas.
Now, if we try to change the Discount value later, any formulas using it within the same/different worksheet will have different miscalculated values.
So, to avoid it, we can find the exact formulas that are referencing the Discount value using the Trace Dependents feature as follows:
- Select the cell for which you want to check the dependent cell. Here, we are tracing dependent cells of the 10% discount cell value.
- Then, click the Trace Dependents option under the Formulas tab. Alternatively, you can use the shortcut key; Alt + M + D.
- After tracing the dependents of the 10% discount cell, we can see a dotted arrow that indicates, another worksheet is using the same value for its cell(s).
- Repeat the above steps to find the dependents of other cells (specifically 20% and 30%).
- Additionally, press the Ctrl + ] shortcut to quickly navigate to its dependent cell (cell containing the formula).
How to Use Trace Dependents for Multiple Cells?
You can only use the Trace Dependent option for one cell at a time.
Nonetheless, you can click the option multiple times to track all the dependents of the dependent cell itself, if any.
For instance,
In the above image, the Total column is using cells in Num1 and Num2 columns to calculate the sum. And, the “Total * 2” column is using cells in the Total column to calculate the double.
So, when we clicked the Trace Dependent option for the A4 cell with value 3000, it pointed to one of the cells in the Total column.
But as we clicked the above option multiple times, Excel displayed another dependent cell (D4 cell with value 16000) under Total*2.
Alternatively, you can also use the Go To feature to highlight such dependent cells.
- Select the cell.
- Then, press Ctrl + G and click Special on the next prompt.
- Click Dependents and choose the All levels option to highlight.
Note: You can also press the Ctrl + Shift + ] shortcut to highlight all the direct and indirect dependents of a cell.How to Trace Dependents in Another Worksheet?
Even if the dependent cells are in another worksheet, you can follow the same steps as mentioned above.
However, this time you will see a black dotted arrow pointing to a worksheet icon as follows.
Now, to re-direct to the dependent cell,
- Click anywhere on the black dotted arrow.
- Then, double-click the dependent cell you are looking for from the list.
How to Remove Arrows Inserted by Trace Dependents?
Once you know which cells are dependent on a cell, you may no longer need the arrow. So, to remove it,
- Select the Formulas tab.
- Click the down arrow icon next to Remove Arrows and select Remove Dependents Arrows.
