How to trace formula relationships

Let’s take a look through the formulas in this model to see how they work.

First, let’s find all the formulas.

We can easily do that by usingGo To Specialand then selecting Formulas.

When I click OK, all formulas in the worksheet are selected.

There’s no references in this formula, so this is just a one-off conversion.

In cell C11, we see a formula that refers to the 3 cells above.

A precedent can be either direct or indirect.

On the Formulas tab of theRibbon, Excel has some tools to show these relationships with arrows.

Use remove arrows to clear arrows off the screen.

Dependents are formula cells that depend on other cells.

Let’s try the same thing with cell F8 selected.

We can see that F8 calculates savings per day using C5, C11, and F5.

These cells are direct precedents.

Cells F9, F10, and F11 are all dependents of the results in F8.

Note that you could also use keyboard shortcuts to select precedents and dependents.

Control + Left Bracket selects direct precedents, and Control + Right Bracket selects dependents.

Adding the Shift key to these shortcuts will select both direct and indirect precedents and dependents.

So that how precedents and dependents work.

When you’re looking over an unfamiliar worksheet, use these tools to trace relationships between formulas.