Trace Dependents Excel

What Is Trace Dependents Excel?

The Trace Dependents Excel option enables us to determine the cells affected directly or indirectly in the same or different sheets by the value in the chosen cell. The option inserts an arrow from the active cell to the dependent cell in the same sheet and a black dashed arrow pointing to a sheet icon if the dependent cell is in another sheet.

Users can use the Trace Dependents option to audit multiple formulas in a worksheet quickly. It determines the origin of an error due to a formula by tracking the cells affecting the issue cell containing the formula.

For example, the following dataset shows an item’s Q1 and Q2 sales figures and their difference.

The aim is to determine the cells affected by the cell B2 value in the active worksheet.

Then, we can enable Trace Dependents Excel option to determine the required relationships.

In the above example, we select cell B2, the cell we aim to evaluate for dependency, and click the Formulas tab and enable Trace Dependents Excel option.

The option inserts arrows between the chosen cell and the cells it affects. In this case, only one cell, cell D2, is directly linked to cell B2 in the active worksheet, which the blue arrow, starting from cell B2 and pointing to cell D2, indicates.

• The Trace Dependents Excel option helps show the cells directly or indirectly related to the chosen cell. The active and dependent cells can be in the same or different worksheets and open workbooks.
• The Trace Dependents option links the active and dependent cells using Blue, Black dashed, and Red arrows based on different dependency conditions.
• Users can use the Excel Trace Dependents option to trace the source of error a formula returns.
• While we can use the Trace Dependents option in the Formulas tab to show the cells dependent on the chosen cell, using VBA codes is also a practical solution.

Shortcuts

The Trace Dependents option shortcuts are as follows:

1. Ctrl + ]
2.  Alt + T + U + D
3. Alt + M + D

The first shortcut highlights all the cells directly dependent on the chosen cell in the same worksheet.

On the other hand, the second and third shortcuts insert the Trace Dependents Excel arrows from the chosen cell to the directly dependent cells in the same sheet. But if the directly dependent cells are in different sheets, the shortcut inserts a black dashed arrow pointing to a sheet-like icon.

Furthermore, we can repeatedly press the shortcut key combinations to determine the indirectly dependent cells in the same worksheet. Excel indicates such cells by the Trace Dependents Excel arrows from the cells found to be directly dependent on the chosen cell to those indirectly dependent on the chosen cell. The reason is that the latter will be directly dependent on the former but indirectly affected by the chosen cell.

How To Use Trace Dependents Excel?

We can use the Trace Dependents option in Excel by following the below steps:

1. Choose the cell to analyze for dependency in the active sheet.
2. Select the Formulas tab - Trace Dependents option.

Clicking the Trace Dependents button will show the cells directly affected by the chosen cell in the active and different sheets in the same or other open workbooks using arrows.

The arrows can be in the following colors:

• The arrow will be Blue if the active cell is error-free, as shown in the previous section.
• The arrow will be Red if the active cell contains an error value.

However, it does not imply that an error value in the chosen cell leads to Trace Dependents Excel not working. On the other hand, the option will help us understand the source of an error value.

• The arrow will be dashed and black if the active and the directly dependent cells are in different sheets in the same or different workbooks currently open on the computer. The arrow will point to a sheet-like icon, and double-clicking the arrow will open the Go To window.

The Go To window lists the addresses of the affected cells in the other sheets in the same or different workbooks presently open on the computer. We can click on the required cell reference to choose it and click OK to go to the chosen cell address.

However, if a closed workbook contains the affected cells, the Go To window will not list it, leading to Trace Dependents Excel not working. Thus, the suggestion is to open all the concerned workbooks before using the option.

Furthermore, clicking the Trace Dependents option again will show the arrows pointing at the cells indirectly affected by the active or chosen cell. The arrow will be from the directly affected cell to the indirectly affected cell, while the former directly affects the latter.

On the other hand, we can select a cell containing the Trace Dependents arrows and choose the Formulas tab - Remove Arrows down arrow - Remove Dependent Arrows. The action will remove the arrows in the chosen cell. Otherwise, select the Formulas tab - Remove Arrows to remove Trace Dependents Excel arrow lines in the active sheet, all in one go.

Examples

Check out the following Excel Trace Dependents option examples to use it effectively.

Example #1

The following dataset contains two value sets, the descriptions explaining the calculations performed using them in column D and column E showing the column D formulas.

We shall see the steps to use the Trace Dependents option in the above dataset to determine the cells linked to cells A2 and A4.

Step 1: Choose cell A2. Next, select Formulas - Trace Dependents, or press Alt + T + U + D or Alt + M + D.

Excel will show the cells directly linked to the active cell A2 using Blue arrows. Thus, cells D2 and D3 are affected by the cell A2 value. Please note that the second line is not Red even though the corresponding affected cell D3 contains a formula returning an error value. The reason is that the active cell A2 does not contain any error.

.

The keyboard shortcut will show the corresponding affected or dependent cells D2 and D3 selected.]

Step 2: Select cell A4 and choose Formulas - Trace Dependents.

The option inserts an arrow between the chosen cell and the cell it affects, cell D4. However, the arrow is Red since the chosen cell contains an error value.

Likewise, we can remove Trace Dependents Excel arrow lines from one cell at a time or all the arrows in the active sheet in one go.

For instance, choose cell A4 and then select Formulas - Remove Arrows - Remove Dependent Arrows.

The Red arrow in cell A4 gets deleted.

Similarly, choose cell A2 and select Formulas - Remove Arrows - Remove Dependent Arrows.

The Blue arrows in cell A2 get deleted.

Example #2 - Trace Dependents On Another Excel Worksheet

The following two images show two sheets.

While the first one contains a list of employees and their contact numbers, the second lists their IDs and contact numbers based on the first worksheet data.

The aim is to determine the cells linked or affected in the second sheet by the cell A5 value in the first sheet.

Then, the steps are as follows:

Step 1: Choose the cell we aim to analyze, cell A5, and then select Formulas - Trace Dependents.

The option shows a black dashed arrow pointing to a sheet-like icon, indicating that the cells related to the chosen cell are in different sheets.

Step 2: Double-click the arrow line to access the Go To window.

The Go To window lists the addresses of all the affected or dependent cells in the other worksheets of the same and other open workbooks.

Step 3: Select the address of the affected cell we want to check for further analysis.

While the selected cell address appears in the Reference field, clicking OK will close the window, and Excel will show the specific dependent cell in the other sheet.

Likewise, we can double-click the black dashed arrow to open the Go To window and check the remaining dependent cells one at a time.

Example #3

The following image shows two datasets.

While the first one contains a list of fruits, their monthly and the total units sold data, the second shows their average units sold data based on the first dataset.

We shall check for cells directly or indirectly affected by the cell B4 data in the current sheet.

Step 1: Choose cell B4 and then select Formulas - Trace Dependents.

The option shows a Blue arrow linking the active cell B4 and the directly dependent cell E4.

Step 2: Let cell B4 remain chosen and select Formulas - Trace Dependents.

The option inserts another Blue arrow from cell E4, pointing to cell B11.

It implies that cell B4 indirectly affects cell B11, while cell E4 directly affects cell B11.

We can verify the above observations by choosing cells E4 and B11 individually to check the formulas they contain.

Cell E4 contains the Excel SUM function, which references the cell B4 value while determining the required total units sold value for Peaches.

On the other hand, the cell B11 formula determines the average units sold value for Peaches by dividing the cell E4 SUM() output by 3. It implies that the cell B11 formula indirectly depends on the value in cell B4. Thus, changing the cell B4 value will affect the formula output in cells E4 and B11 directly and indirectly.

Furthermore, consider the requirement to remove the arrows from a cell with directly and indirectly dependent cells in the active sheet. In such a case, we must select the active cell containing the arrows. Next, repeatedly click the Remove Dependent Arrows option under the Remove Arrows in the Formulas tab until all the arrows associated with the chosen cell are deleted.

The above images show that the arrow to the indirectly affected cell and the one pointing to the directly dependent cell get deleted in steps.

Limitations

The limitations of the Trace Dependents option in Excel are as follows:

• We can use the option to check the cells dependent on one active cell at a time.
• The option does not work properly if a cell contains a formula with a circular reference.
• Consider an active cell affects cells directly and indirectly. Then, choosing the option for the first time will insert the arrows pointing to only the directly affected cells. After that, we must choose the option repeatedly until the arrows pointing to all the indirectly affected cells display.
• The Trace Dependents arrows disappear when we save or close the file. Thus, we can either use the option whenever we must display the links between the active cell and its dependents or write a macro to retain or keep the arrows.

Important Things To Note

• The Trace Dependents Excel option works for one active cell at a time.
• The keyboard shortcuts to show the cells which the chosen cell affects are Ctrl + ], Alt + T + U + D, and Alt + M + D.
• The Trace Dependents option greys out if the worksheet is protected or multiple sheets are selected.
• The Trace Dependents arrows disappear once we save or close the workbook.

Frequently Asked Question (FAQs)

Download Template

This article must be helpful to understand the Trace Dependents Excel, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to What Is Trace Dependents Excel. We learn how to use Trace Dependents in Excel and their shortcuts with examples & their limitations. You can learn more from the following articles –

• Auditing Tools in Excel
• Hide Column in Excel
• Fibonacci Retracement