How to Trace the Precedents and Dependents of a MS Excel Formula Cell

© Ugur Akinci

MS Excel cells usually contain formulas that depend on the calculation results of other cells.

For example, consider the following cells:

B1 is calculated by taking A1′s value and multiplying it with 2.

A1 is the PRECEDENT of B1 because it comes BEFORE (“Pre”) B1.

B1 is DEPENDENT on A1 because without A1 we cannot calculate B1.

When there are multiple such cells in a worksheet, it helps to clearly understand which cell formulas depend on which other cell formulas. This is important if you think there is something wrong with the result of a formula but you don’t know where else to look to correct it.

Here is an example:

On the face of it, it’s impossible to tell if there are any connections between these cells.

Here is how to trace any possible connections:

(1) Select the Formulas Tab and pay attention to the FORMULA AUDUTING group of controls:

(2) Select the cell with “315” value (C9) and then click the TRACE PRECEDENTS link:

As you can see, FOUR cells are immediately highlighted with BLUE ARROWS as the PRECEDENTS of cell C9.

(3) Click the SHOW FORMULAS link to make the connection even more obvious:

(4) To trace the precedents back by one more step, click the TRACE PRECEDENTS link once again to reveal the dependency relationship between ONLY the cells that are connected to C9:

IMPORTANT: Although C1 is a PRECEDENT for D1, the C1–D1 link is not displayed with a blue arrow because D1 is not within the C9 formula and does not “point at” C9.

(5) To display the dependency between C1 and D1, select D1 and then click the TRACE PRECEDENTS link:

(6) Keep clicking the TRACE PRECEDENTS link to display all the other dependencies between the same group of cells:

(7) Click the SHOW FORMULAS link to toggle the cell formulas on and off:

(8) Click the REMOVE ARROWS link at any time to clear off the blue arrows.