FLASH SALE! - "CHATGPT AND ARTIFICIAL INTELLIGENCE FOR MICROSOFT EXCEL AT 60% OFF" Enroll Now

Trace Dependents Excel

Updated on January 2, 2024
Article bySuganthi S
Edited bySuganthi S
Reviewed byDheeraj Vaidya, CFA, FRM

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.

Trace Dependents Excel Intro

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.

Trace Dependents Excel Intro

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.

Key Takeaways

  • 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.

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

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.
Trace Dependents Excel - Trace

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.
Trace Dependents Excel - Red arrow.jpg

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.
Trace Dependents Excel - Black arrow.jpg
Trace Dependents Excel - Go To.jpg

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.

Trace Dependents Excel - Example 1.jpg

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.

Trace Dependents Excel - Example 1 - Step 1.jpg

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.

Trace Dependents Excel - Example 1 - Step 1 - Trace.jpg

[Alternatively, choose cell A2 and press Ctrl +].

Trace Dependents Excel - Example 1 - Alternative.jpg

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.

Trace Dependents Excel - Example 1 - Step 2

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.

Trace Dependents Excel - Example 1 - Step 2 - Red arrow.jpg

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 ArrowsRemove Dependent Arrows.

Trace Dependents Excel - Example 1 - Step 2 - remove dependent arrow

The Red arrow in cell A4 gets deleted.

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

Trace Dependents Excel - Example 1 - Step 2 - A2 - arrow

The Blue arrows in cell A2 get deleted.

Trace Dependents Excel - Example 1 - Step 2 - remove blue arrow

[Alternatively, click on any cell in the active sheet and choose Formulas Remove Arrows.

Trace Dependents Excel - Example 1 - Remove Arrows

The above step will remove all the arrows in the active sheet in one go.]

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.

Trace Dependents Excel - Example 2 - Emp-Contact.jpg
Trace Dependents Excel - Example 2 - Emp-id.jpg

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.

Trace Dependents Excel - Example 2 - Step 1.jpg

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.

Trace Dependents Excel - Example 2 - Step 1 - sheet icon.jpg

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.

Trace Dependents Excel - Example 2 - Step 2.jpg

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

Trace Dependents Excel - Example 2 - Step 3.jpg

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.

Trace Dependents Excel - Example 2 - Reference field.jpg

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.

Trace Dependents Excel - Example 3.jpg

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.

Example 3 - Step 1.jpg

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.

Example 3 - Step 2.jpg

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

Example 3 - Step 2 - Blue arrow.jpg

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.

Example 3 - Step 2 - E4.jpg

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

Example 3 - Step 2 - B11.jpg

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.

Example 3 - Remove Arrows.jpg
Example 3 - Remove Trace

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.

Example 3 - Output.jpg

[Alternatively, we can click anywhere in the current sheet and select Formulas Remove Arrows.

Example 3 - Alternative.jpg

The above action will remove all the arrows in the active sheet.]

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)

1. How do you trace dependents in Excel macro?

You can trace dependents in Excel macro using the following steps, explained with an example.

The following dataset shows the principal value, interest rates and terms, based on which we calculate the simple interest values in cells B4:D4.

FAQ 1.jpg

The aim is to determine the cells affected by the cell B1 value in the active sheet. Then, here is how to use VBA coding to achieve the required outcome.

Step 1: Open the source dataset worksheet and press Alt + F11 to open the VBA Editor.

FAQ 1 - Step 1.jpg

Step 2: Select the applicable VBAProject and choose the Module option under the Insert tab in the menu.

FAQ 1 - Step 2 - Module.jpg

The above step will open a new module window.

FAQ 1 - Step 2 - new module.jpg

Step 3: Enter the VBA code in the new module window to display all the cells affected in the active sheet by the chosen cell.

FAQ 1 - Step 3.jpg

Step 4: Select the Play icon in the menu to run the code.

FAQ 1 - Step 4

Finally, open the active sheet to view the Trace Dependents arrows from the chosen cell to the cells dependent on it.

FAQ 1 - Output

The above output shows that the active cell B1 affects cells B4:D4 in the current sheet.

2. When tracing dependents in Excel what happens when you click the Trace Dependents option more than once?

When tracing dependents in Excel, clicking the Trace Dependents option more than once shows the cells indirectly related to the cell chosen for evaluation, one at a time.

3. Why is Excel not letting me trace dependents?

Excel is not letting you trace dependents, perhaps because of the following reasons:

• The sheet is protected, or you have selected multiple sheets.
• The number of dependent cells is large.
• The chosen cell contains a formula involving a circular reference.

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.

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 –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *