Excel formulas frequently refer to other cells. A cell that contains a formula will almost always use values in other cells to calculate a result. Cells containing formulas are called dependent cells, the cells referenced in the formula are precedents.
Excel contains a collection of formula auditing tools that can help you analyze formulas and better understand which cells are being referenced in a calculation. These tools are available on the formulas tab on the ribbon in Excel.
Let’s take a look at how to use Trace Precedents and Trace Dependents in Excel.
Find the Dependents for an Active Cell
The sample spreadsheet in this article contains a number of calculations. If I want to know which cells utilize the value in cell B4, I would do the following:
- Click to select cell B4.
- Click the Trace Dependents button in the Formulas > Formula Auditing group.
A tracer arrow is added to the worksheet indicating that cell B4 (the price of coffee) is a dependent cell, referenced in the total displayed in D4.
If I click the Trace Dependents button again, another tracer arrow is added. The blue arrows indicate that the value in cell B4 is also referenced in the grand total in cell D7.
Tracer arrows make it easier to visualize where values in a selected cell are referenced throughout the Excel worksheet. Tracer arrows are added based on the active cell so don’t forget to select the cell first.
OK, we know there are tools to help you locate dependent cells. Can you locate precedent cells too? Absolutely!
Find the Precedents for an Active Cell
Precedents are cells that contain the values referenced in a formula.
If you need to locate a precedent cell, you would click on a cell containing a formula then click the Trace Precedents button in the Formulas > Formula Auditing group.
In the screenshot above, D7 is the active cell. A blue arrow indicates that the subtotals in D4:D6 are referenced in the grand total in D7.
If I click Trace Precedents again, additional blue arrows are displayed. In the screenshot below, every cell that contains a value is a precedent (directly or indirectly) to the grand total in D7.
How to Remove the Tracer Arrows
To remove the tracer arrows, just click the Remove Arrows button in the Formulas > Formula Auditing group.
Clicking Remove Arrows removes all of the blue arrows. Precedents and dependents are no longer indicated.
