Problem: I am about to delete a section of a worksheet that I believe is no longer being used. However, I know that if I delete the cell, and some other far-off range relies on the cell, the far-off range will change to the dreaded #REF! error. How can I determine if any other range refers to this cell?
Strategy: You can select the cell that you are considering for deletion and then select Formulas, Trace Dependents. (Dependents are other cells that rely on the current cell for calculation.)
Blue arrows will draw from the active cell out to any dependents. Below, , you can see that cell F4 is used to calculate H4.
- Blue arrows point to dependent cells on this worksheet.
If a dependent is on another worksheet, Excel will draw a black arrow to the other worksheet icon. Double-click the line that leads to the other worksheet icon. Excel will show you a list of the off-sheet dependents.
Additional Details: If you re-click Trace Dependents, Excel will draw second-level dependents. Below, you can see that F4 is used to calculate H4, and H4 is used to calculate D15 and E20.
If you click Trace Dependents several times, you will see all of the formulas that would change to #REF! if you delete cell C4.
You also have a big mess on your spreadsheet! To get rid of all arrows, choose Remove All Arrows.
Gotcha: Some advanced functions such as =INDIRECT("F" & D4/600) might be pointing to your target cell and will not be detected by the Trace Dependents command.
- Second-level dependents.