Trace Dependents

What are Trace Dependents?

Trace Dependents in Excel is a formula auditing feature that helps analyze relationships between cells in a spreadsheet. It commands visually traces the dependency between cells, simplifying the analysis of complex data sets.

Excel, developed by Microsoft, is the most widely used spreadsheet software tool. It is extensively used by businesses and working professionals to store data more efficiently.

Excel is a user-friendly and simple-to-use spreadsheet tool as it offers a wide range of capabilities that simplify complicated mathematical and statistical computations involving data.

In addition to data storage and calculations, Excel allows users to create visually appealing charts and graphs that effectively represent data intuitively and informally.

Every two to three years, Microsoft publishes new versions of Excel, enhancing the software's functionality and time-saving capabilities. In this article, we will explore a useful concept in Excel called the Trace Dependents.

It often happens that to do some computations in a particular cell in Excel, we need to extract the values entered in different cells.

For example, for calculating the inverse of the standard normal cumulative distribution using the NORM.S.INV Function in Excel in a particular cell, say C7, we need to know the probability corresponding to the distribution.

Suppose the probability of the distribution is in cell C5. To use the NORM.S.INV in cell C7, we need to use the probability value in cell C5 as the function argument. Hence, the value in cell C7 will change if the value in cell C5 is changed. So, cell C7 is dependent on cell C5.

The formula used in cell C7 to get the inverse of the standard normal distribution is:

=NORM.S.INV(C5)

Understanding the Trace Dependents

Trace Dependents is a built-in formula auditing mechanism in Excel that assists users in analyzing the relationships between cells in a spreadsheet.

Understanding the dependence and relationship between different cells can be challenging when working with complex data and multiple formulas and functions.

While using Excel, we may encounter two terminologies related to cells. They are:

• Precedent Cells
• Dependent Cells

Cells used in the formula for calculations in another cell are called precedent cells. For example, in the example above, the cell C5 is referred to by a formula in another cell C7. Hence, cell C5 is a precedent of cell C7.

On the other hand, cells containing formulas referring to other cells to make some computations are called dependent cells. For example, in the above example, cell C7 has the formula: 

=NORM.S.INV(C5)

So, the value in cell C7 depends on the value in cell C5. Hence cell C7 is dependent on cell C5.

The cells whose value changes with the value of another cell are dependent cells. It happens when we reference a cell(C5 in the above example) in a formula in another cell(C7 in the above example). The referenced cell is the precedent cell.

If any formula uses precedent or dependent cells, it becomes difficult to check the formulas to find the source of error.

We have seen above what dependent and precedent cells are. But that was a simple case to visualize. Sometimes, in Excel, we might have to deal with large data sets. Here, manually identifying the dependent cells might be difficult and time-consuming.

To make things easier for the user, Excel provides the Trace Precedents and Trace Dependents commands to trace the relationship between such cells and formulas graphically, using arrow marks.

Using the Trace Dependents feature, users can easily analyze which cells are dependent on or affected by a selected cell. This graphical representation of related cells helps understand the dependencies within a spreadsheet.

How to Use the Trace Dependents Command in Excel

We have seen what trace dependents mean in the previous section. Let us now understand how to use the trace dependents command in Excel to link the related cells.

If the dependent cells lie in the same Excel worksheet, linking them with the active cell is very straightforward. In this case, Excel shows the related and active cells using blue-colored arrows.

To do so, proceed as follows:

1. Click on the FORMULAS tab at the top of the Excel worksheet.
2. Now, go to the Formulas Auditing section, and select Trace Dependents.
3. Excel displays all the dependent cells in the worksheet by linking them with the selected cell with the blue-colored arrows shown above. Therefore, selecting the cell you want to evaluate before using this command is important.
4. If no cell is selected, Excel displays an error message, as shown below:
5. If an active cell is related to the cells in another Excel worksheet, it is not straightforward to link the dependent cells. In this case, instead of a blue arrow, Excel uses a dotted black arrow pointing to a small icon or location on the worksheet we are working on. 
6. To view the cells related to the selected cells, double-click on the dotted line, which opens a dialog box showing the list of the dependent cells. To see the details of any dependent cell, click on that cell from the list.

To link the dependent cells located on a different worksheet, follow the following steps:

1. On the worksheet, select the active cell you want to evaluate.
2. Go to the FORMULAS tab and select the Trace Dependents icon in the Formulas Auditing section. 
3. This will display all the dependent cells on the same worksheet by linking them to the active cell using a blue arrow.
4. Click on the Trace Dependents icon. If there are cells in the other Excel workbooks dependent on the active cell, they will be shown by using a black dotted line with an arrowhead pointing to some location on the worksheet we are working on.
5. To see the details of the related cells on other worksheets, double-click on this black dotted line, which opens a dialog box showing the list of the related workbooks and their corresponding cells.

Let us now see examples demonstrating the use of Trace Dependents in Excel.

Practical Examples of Trace Dependents in Excel

Having looked at the theoretical aspects of trace dependents in Excel, let us now see a few examples to understand its practical applications.

Example 1

Let’s consider hypothetical data representing the marks of five students in Science, Economics, and History. First, we have calculated the total and average marks in Excel, shown below:

In the above example, if we want to find the dependent cells, we first select an active cell we want to evaluate. The active cell is the starting point from which Excel identifies the cells that are dependent on it.

Suppose we want to find the dependent cells of cell C4. We start by selecting cell C4 as our active cell.

To find the dependent cells of cell C4, proceed as discussed in the previous section. Then, click on the FORMULAS tab, go to the Formulas Auditing section, and click on Trace Dependents. Excel will display blue-colored arrows linking all the dependent cells of cell C4.

Any changes made to the values of the active cell C4 will affect the value of the dependent cell D4. Clicking the Trace Dependents option again shows all the cells directly or indirectly related to the active cell we are evaluating.

Similarly, we can select any cell and use the Trace Dependents option in Excel to know about its dependent cells or the cells whose values it can affect.

To remove the blue-colored arrows, we can click on the Remove Arrows option below the Trace Dependents option in the Formula Auditing section. Doing this will remove all the arrows on the Excel worksheet.

To remove the dependent arrows only, we need to click on the arrow next to the Remove Arrows option, and from the drop-down menu, select Remove Dependent Arrows.

Example 2

Continuing from Example 1, let's explore how to trace dependents located in another Excel worksheet.

Consider the same scenario with the marks data in Sheet 1. We have created another worksheet, Sheet 2, to demonstrate the external linking of related cells in different worksheets. In Sheet 2, we check who has more average between John and Daniel and print the student's name with more average marks using the IF Function.

The IF statement is a conditional statement in Excel. Its syntax is as follows:

IF(logical_test, [value_if_true], [value_if_false]), here

• logical_test is the condition to be checked
• value_if_true is the value returned if the condition is True
• value_if_false is the value returned if the condition is False 

We can reference a cell or a group of cells in another worksheet in the same workbook by using the worksheet name followed by an exclamation mark(!) before the cell address. The syntax to do this is as follows:

Sheet_name!Cell_address

Suppose we want to identify all the related cells of cell D4 in Sheet 1. To do this, we first select the cell D4.

This is our active cell in this case. As discussed earlier, on clicking the Trace Dependents option in the Formula Auditing section, we get the dependent cells of cell D4 on the same Excel worksheet. These are linked by a dark blue arrow line.

However, this does not show the related cells of cell D4 located in Sheet 2. So, to get the related cells in Sheet 2, we need to click the Trace Dependents option again. On doing this, Excel produces a black dotted line pointing to a small image on Sheet 1.

Double-click this dotted line to open a dialog box containing a list of related cells external to Sheet 1.

On double-clicking the listed cell, Excel directly takes us to the location of the particular cell. Hence in this way, we can obtain all the dependent cells of a selected active cell in an Excel worksheet using the Trace Dependents option.

Researched and authored by Devang Shekhar | LinkedIn

Reviewed and edited by Parul Gupta | LinkedIn

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources:

• Trace Precedents
• Types of Errors in VBA for Excel
• VLOOKUP
• Waterfall Charts
• XLOOKUP