Trace Dependents

What are Trace Dependents?

Trace Dependents[1] are defined as the cells or group of cells that are affected by the selected cell. When using the formula auditing tool, Excel displays blue arrows that link the cells that are related to the active cell.

An Excel spreadsheet sometimes contain multiple formulas, and understanding the relationship between the cells would be a difficult task in the absence of the Trace Precedents and Trace Dependents features in Excel.

How Trace Dependents Work

Trace Dependents is an Excel auditing tool that shows the cells that are affected by an active cell by displaying arrows linking the related cells to the active cell. When the cells are located on the same worksheet, it is relatively straightforward since Excel will link from the related cells to the active cell using blue arrows.

If the cells are linked to another sheet, instead of an arrow linking the cells, there will be a dotted black arrow pointing to a small icon in the worksheet. To view the details of the cells related to the active cell, double-click the dotted line, and it will open a dialog box with a list of the related cells. Click on any of the listed cells to view the details of the cell.

To see the cells that are dependent on the active cell, follow the steps below:

1. Open the worksheet and locate the active cell.
2. Select the cell you want to analyze.
3. Go to Formulas tab > Formulas Auditing > Trace Dependents.
4. Click on the Trace Dependents button to see the cells that are affected by the active cell. It will show a blue arrow that links the active cell and the other cells related to the selected cell.
5. Click the Trace Dependents button again to show more cells that are related to the active cells. If there are other cells in other workbooks that are related to the active cell, they will be indicated with a black dash line and arrowhead pointed to a small picture. Double-click the black dash line to open a dialog box that lists the related workbooks and their respective cells.
6. The arrows can be removed by selecting the cell with an arrow and then clicking the Remove Arrows button next to the Trace Dependents button.

Practical Example: Trace Dependents

XYZ Limited is making revenues projections for the year 2019, and the revenue projections are categorized per quarter. The Excel worksheet for the projections is shown below:

Using the example above, we can find the dependent cells by first selecting a cell that we want to evaluate. The cell marks the starting point from which we can identify the cells that are dependent on it. C5 is our active cell, and we can use it to find dependent cells.

To view the dependent cells, go to the Formulas tab, Formulas Auditing section, then click the Trace Dependents button. Excel will display blue lines that link the cells that you are analyzing to the dependent cells. In our case, C5 is the active cell, and D5, E5, and F5 are the dependent cells. Any changes made to the active cell, C5, will affect all the dependent cells.

Blue arrows show that cells have no errors, whereas red arrows show the cells that cause errors. Clicking the Trace Dependents button more than once will show all the cells that are related to the active cell that you are evaluating.

To remove the tracer arrows, click the arrow next to the Remove Arrows button under the Formulas Auditing tool and click Remove Dependent Arrows.

Tracing Dependents on Another Excel Worksheet

Excel also allows you to trace dependents that are located in a separate Excel worksheet. Select a cell that you want to evaluate and then go to Formulas Tab > Formulas Auditing > Trace Dependents. It will show all the related cells in the worksheet as shown below:

Dependents located in a separate Excel worksheet or workbook are shown by a black dotted line pointing to a picture/icon.

To view the related cells, double-click on the dotted line to bring up a dialog box that includes a list of all cells located outside the current worksheet. Double-click on one of the cells to open the worksheet with the related cells. Excel will take you to the specific dependent cell.

How to Use the Watch Window Option with Trace Dependents

Microsoft Excel comes with a Watch Window function that allows users to add cells from anywhere in the current workbook and view the cells and their values through a small dialog box in the current worksheet.

If the current worksheet contains dependent cells in other workbooks, you can switch between the multiple worksheets and add the dependent cells located in the other worksheets to the current worksheet’s watch window. Double-clicking on any of the watched cells will take you to the specific workbook(s).

Additional Resources

Thank you for reading CFI’s guide to Trace Dependents. To keep learning and advancing your career, the following CFI resources will be helpful:

• Dynamic Financial Analysis
• Excel Modeling Best Practices
• Excel for Beginners
• List of All Excel Courses
• See all Excel resources