Checking Formulas: Tracing Dependents & Precedents

checking1“Is this formula in cell A10 used for anything?” “Where are these inputs in cell Y54 coming from?” If you ever asked yourself these questions, read on.

 

1. Tracing Precedents
“Precedent cells are cells that are referred to by a formula in another cell. For example, if cell D10 contains the formula =B5, cell B5 is a precedent to cell D10.” Microsoft

To easily see which cells are referred to in a particular formula:

  1. Click on the cell you want to evaluate
  2. Click on the Formulas -> Formula Auditing -> Trace Precedents

checking2

When you click on this, Excel will create blue lines from the cells that make up the formula in the cell back to the cell you’re analyzing.

checking3

2. Tracing Dependents
“Dependent cells contain formulas that refer to other cells. For example, if cell D10 contains the formula =B5, cell D10 is a dependent of cell B5.” Microsoft

To easily see which cells use a particular formula:

  1. Click on the cell you want to evaluate
  2. Click on the Formulas -> Formula Auditing -> Trace Dependents

checking4

When you click on this, Excel will create blue lines from the cells that are dependent on the analyzed cell back to the cell you’re analyzing. In the image below we see that cell F2 is dependent on the Payment cell in C5. Trace Dependent is especially helpful when you are trying to clean up a spreadsheet. Many times people will put random calculations to the side or have redundant calculations. You can quickly see which formulas and cells are actually being used and delete the others.

checking5

If there are no Dependents or Precedents, the following message will appear:

checking6

To remove the blue arrows, click on Remove Arrows:

checking7