Microsoft Excel: Before Deleting A Cell, Find Out If Other Cells Rely On It

Problem: Before deleting a section of the worksheet you believe is no longer needed know that if you delete the cell and some other far-off range relies on the cell, the far-off range will change to the #REF! error. How do you find out if another range refers to this cell?

Strategy: Select the cell that you are considering for deletion. From the menu, select Tools – Formula Auditing – Trace Dependents as shown in Fig. 474. Dependents are other cells that rely on the current cell for calculation.

Blue arrows will draw from the active cell out to any dependents. In Fig. 475, cell D4 is used to calculate H4, and also a hidden cell in C26.

What if a dependent is on another worksheet? Excel will draw a black arrow to the “other worksheet” icon as shown in Fig. 476.

Gotcha: Of course, it does not tell you which other worksheet has a dependent.

Additional Details: If you immediately invoke Tools – Formula Auditing – Trace Dependents, Excel will draw arrows from each of the dependent cells to their dependent cells. In Fig. 477, D4 is used to calculate H4. H4 is then used to calculate D15 and H20.

If you immediately ask to Trace Dependents several times in a row, 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 Tools – Formula Auditing – Remove All Arrows.

Additional Information: If you think that there are no cells that use the current cell and you are right, then Excel will give you thes message shown in Fig. 478.

Summary: To determine if a cell can be deleted without affecting any other formulas, select the cell, and then select Tools – Formula Auditing – Trace Dependents.

Commands Discussed: Tools – Formula Auditing – Trace Dependents