How to trace formula relationships

In this video, we'll look at how to quickly find formulas and trace how they are related to one another using the concept of precedents and dependents.

Here we have a simple model that shows the expense of making coffee at home vs. buying coffee in a coffee shop.

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 using Go To Special and then selecting Formulas.

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

To make it easier to see which cells contain formulas, I'll go ahead and add a  fill to these cells.

If I click into the first formula cell, I can see that it's using the CONVERT function to convert 3 pounds to grams. 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. The formula is dividing the cost of $15.00 by the number of grams in C9, to get a cost per gram, then multiplying that by the 11 grams needed to make one cup of coffee.

From the perspective of cell C11, the three cells above are called Precedents, because they directly contribute to the result in C11. A precedent can be either direct or indirect. Direct precedents contribute directly and indirect precedents aren't used directly in the formula, but they are used by a cell that is used in the formula.

On the Formulas tab of the Ribbon, Excel has some tools to show these relationships with arrows. With C11 selected, the Trace Precedents button shows that C8, C9, and C11 all contribute directly to the result.

Use remove arrows to clear arrows off the screen.

Dependents are formula cells that depend on other cells. If I select C11 and click trace dependents, Excel draws arrows to all the cells that depend directly on the result of the formula in C11.

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.

In this case, once the model has savings per day in F8, it goes on to calculate savings per week, savings per month, and savings per year, but these aren't used anywhere else in the model.

Note that you can 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.