MrExcel.com - Your One Stop for Excel Tips & Solutions

Before Deleting a Cell, Find out if Other Cells Rely on It

June 03, 2022 - by

Before Deleting a Cell, Find out if Other Cells Rely on It

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.

Select cell F4 and Trace Dependents. One blue arrow is drawn pointing to each cell that refers to F4.
Figure 503. 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.

With the same cell selected. click Trace Dependents again. Excel draws more arrows to the second level dependents.
Figure 504. Second-level dependents.

This article is an excerpt from Power Excel With MrExcel

Title photo by Bradyn Trollip on Unsplash