This tutorial demonstrates how to display dependency tracer arrows (showing which cells depend on the current cell) in Excel and Google Sheets.
Show Dependency Tracer Arrows in Excel
Dependent cells contain formulas that refer to other cells. For example, if cell B8 contains the formula =B2, cell B8 is dependent on cell B2.
You can use trace dependents commands to graphically display and trace the relationships between cells and formulas with tracer arrows. Trace Dependents shows arrows to indicate which cells are affected by the value of the currently selected cell. This helps a lot when you are checking formulas for errors and need help identifying the source of any errors. Follow these steps to show formula relationships between cells:
- The first step is to select the cell you want to show dependency tracer arrows for (indicating which other cells’ values are based on the value of that cell).
- Then in the Ribbon, go to Formulas > Formula Auditing > Trace Dependents.
Dependency tracer arrows appear for the selected cell. You can see below, for this example, that there are two cells that depend on the selected cell. (Lisa’s QTR 1 revenue affects the total revenue for that employee and the total revenue for that quarter.)
- If there is more than one layer, click Trace Dependents (in the Ribbon) again. It shows additional layers with new tracer arrows.
For example, F2 and B8 depend on B2, and F8 depends on F2. Therefore, an error in B2 has the capacity to affect F2, B8, and F8.
- When you’re done looking at the relationships between cells, hide the arrows to view your spreadsheet without them.
- You can use a VBA loop to trace multiple cells without clicking on each cell.
Show Dependency Tracer Arrows in Google Sheets
The Trace Dependents feature is not available in Google Sheets, but there is a workaround to show the relationships between cells and formulas.
- In the Menu, go to View > Show formulas (or use the CTRL + ` shortcut).
Tip: The key to press while holding CTRL is the grave/acute symbol, on the same key as the tilde (~), not an apostrophe.
- Use CTRL + F shortcut to open Find in sheet box. In it, enter the cell for which you want to find dependent cells. Any dependent cells are highlighted.