find dependent cell in worksheet in Excel
Q104. In Excel, how can I identify the cells that are dependent on a particular cell?
For example, I have inherited a complex WorkBook. I have to make a particular change - I want to analyze the impact of changing the particular cell on the rest of the WorkBook before actually making the change.
In the example below, I want to change the interval period for analysis to 2 years - I want to analyze what happens if I amend the formula in cell B9 as part of changing year interval.
To do it in Excel, here is the answer:
a) Select the cell of interest ("B9" in this case). Click on "Trace Dependents" under "Formulas".
b) All the cells that are dependent on the cell of interest are connected by arrows. This can help analyze the impact of change to cell of interest.
c) To remove the arrows, Click on "Remove Arrows" under "Formulas". Click on "Remove Dependent Arrows".
d) All the "Dependent Arrows" would be removed as shown below.