What Is Trace Precedents Excel?
The Trace Precedents Excel feature helps us identify one or more cells that impact the value in the selected cell directly or indirectly in the same or different worksheet. The feature displays an arrow from the precedent cells to the current cell in the same worksheet. But the arrow will be black dashed with a sheet icon if the precedent cell or cells are not in the same sheet as the active cell.
Users can utilize the Trace Precedents feature to identify the cells contributing to the calculations or the error value a formula performs or returns in the current cell.
For example, the source dataset holds a firm’s quarterly sales data and its total sales figure in cell D4.
The task is to identify cells that influence the target cell D4 value.
Then, we can turn on Trace Precedents Excel feature to find the required cells.
In this case, we choose cell D4, for which we aim to find the cells affecting the formula it contains. Next, select the Formulas tab and the Trace Precedents option to turn on Trace Precedents Excel feature.
We will see blue arrows from all the precedent cells to the chosen cell in the active worksheet, directly affecting the formula in the selected cell. In the above illustration, the blue arrows from cells B2:B5 show that they directly impact the cell D4 formula in the current sheet, in line with the Trace Precedents Excel.
Table of contents
Key Takeaways
- The Trace Precedents Excel feature enables us to locate the cells impacting a specific cell value directly or indirectly. The chosen cell and the precedents can be in the current or different sheets and files open on the PC.
- The Trace Precedents option shows the link between the precedents and the chosen cell using Blue, Black dashed, and Red arrows according to the impact conditions.
- The Excel Trace Precedents option is useful to track the reason for the error in a chosen cell.
- We can select the Trace Precedents option from the Formulas tab to view the cells influencing the chosen cell. On the other hand, using the ShowPrecedents option in VBA is another practical technique to apply the option.
How To Use Trace Precedents Excel?
We can utilize the Trace Precedents feature in Excel using the following steps:
- Choose the cell we aim to review for finding cells affecting its value.
- Choose the Formulas tab – Trace Precedents.
We will see arrows to the chosen cell from the precedent cells. They could be in the current and different worksheets in the same or other open files, adhering to the Trace Precedents Excel definition discussed earlier.
- Next, click the Trace Precedents option again to view the arrows from the cells indirectly impacting the value in the chosen cell. These arrows will be from cells indirectly affecting the chosen cell’s formula to the corresponding cells directly affecting the chosen cell’s formula.
The arrows follow the color coding mentioned below:
- A blue arrow indicates the precedent cell does not hold an error value, leading to the chosen cell containing an error-free value, as depicted in the previous section.
- A red arrow indicates the specific precedent cell contains an error value, leading to the current cell holding an error value.
Please note that a red arrow does not indicate Trace Precedents Excel not working. Instead, this aspect enables us to find the source of the error value in the chosen cell.
- A black, dashed arrow, with a sheet icon at the start of the arrow, indicates the precedent cells are in different worksheets of the same or different files open on the PC. We can double-click the arrow to access the Go To window.
The Go To window shows the location of the precedent cells in the other worksheets in the same or different files open on the PC. We can click on the required address to select it and click OK to go to the selected cell reference.
However, if a closed file contains the precedent cells, it will not show in the Go To window, leading to the scenario of Trace Precedents Excel not working. Thus, ensure all the required workbooks are open on the PC before applying the option.
Shortcut
Furthermore, the following are the Excel keyboard shortcuts to access the Trace Precedents option:
- Ctrl + [
- Alt + M + P
The first Trace Precedents Excel shortcut highlights all the cells directly impacting the chosen cell value in the active sheet.
On the other hand, the second Trace Precedents Excel shortcut shows the arrows from the cells directly influencing the value in the chosen cell in the active worksheet. However, when the precedent cells are not in the current sheet as the chosen cell, the shortcut shows a black dashed arrow with a sheet icon at the start of the arrow.
Furthermore, using the shortcuts repeatedly will show the arrows for the indirect relationships in the active worksheet, with the arrows following the color codes accordingly.
Examples
The following Trace Precedents feature illustrations explain its practical uses.
Example #1 – Trace Indirect Precedents
We have two datasets. The first one contains the total tasks completed by a team at a firm, along with the monthly breakdown of the tasks completed count. On the other hand, the second dataset shows the average of the tasks completed for the concerned team, based on the first dataset.
The aim is to locate the indirect precedents in the current sheet for cell B5, which contains the formula to determine the average of the tasks completed for the concerned team.
Then, we can use the Trace Precedents feature to secure the required output.
Step 1: Choose cell B5, and then Formulas – Trace Precedents.
An arrow appears from cell E2 to B5, indicating that cell E2 is a precedent cell, directly affecting the cell B5 formula output.
Step 2: With cell B5 remaining selected, choose Formulas – Trace Precedents again.
A second arrow shows from cell B2 to E2, representing the fact that cell B2 is a precedent cell, directly affecting the cell E2 formula output. Thus, it implies that cell B2 is a precedent cell, indirectly affecting the cell B5 formula output.
Logically, the cell B2 value contributes to determining the total tasks completed value in cell E2, which the cell B5 formula uses to evaluate the average of the tasks completed.
Example #2 – Trace Precedents For Multiple Cells
The following image shows the sample covariance calculations based on two input datasets, x and y.
We know that multiple cells in the above worksheet contain formulas and the aim is to find the precedents for all such cells in the sheet.
Then, the steps are as follows:
Step 1: We shall select a cell, say, cell D15, and enter the ‘=’ sign.
Next, click the triangle on the top-left corner of the sheet work area to select all the rows of the sheet.
And then press Enter. A message box will appear, notifying us about an Excel circular reference. Click OK to proceed.
Step 2: Cell D15 will show the output as 0. Choose cell D15, and then Formulas – Trace Precedents.
We will see an arrow from cell A1 to D15, indicating that cell A1 is a precedent cell, directly impacting the cell D15 formula output.
Step 3: With cell D15 remaining chosen, select Formulas – Trace Precedents again.
We can now view all the arrows between the precedents and the corresponding cells, whose values they affect directly and indirectly.
Example #3 – Trace Precedents Referring To Another Worksheet
There are two datasets, one in the sheet INV_Order Quantity and the other in the sheet INV_Data.
The first sheet contains the invoice numbers and the corresponding order quantity values. On the other hand, the second holds the same invoice numbers with the respective order dates. However, it also contains the order quantity data fetched from the first sheet using the Excel VLOOKUP function in column C.
The aim is to find the precedents impacting the cell C2 formula return value in the second sheet. Then, we can secure the required information using the Trace Precedents option, as explained below:
Step 1: Choose cell C2, and then Formulas – Trace Precedents.
Two arrows, one blue and the other black and dashed, appear. The blue arrow is between cell A2 and cell C2, representing the fact that cell A2 is a precedent, directly influencing the cell C2 formula output. However, there are one or more precedents not in the same sheet, directly or indirectly impacting the chosen cell, which the black dashed arrow indicates.
Step 2: Double-click the black arrow to access the Go To window.
We can see the address of the precedents, present in a different sheet.
Step 3: Select the address in the Go To window and click OK.
The first sheet opens with the precedents influencing the cell C2 value selected.
How To Remove Arrows Of Trace Precedents?
The steps to remove the Trace Precedents arrows are as follows:
- Have the sheet containing the Trace Precedents arrows open.
- Select the Formulas tab – The Remove Arrows option down arrow – The Remove Precedent Arrows option.
Please note that clicking the Remove Arrows option will delete all the arrows in one go. However, the Remove Precedent Arrows option will first delete the arrows corresponding to the indirect precedents. Next, we must iterate step 2 to delete those representing direct precedents.
Important Things To Note
- The Trace Precedents Excel option works only for one chosen cell at one time.
- The keyboard shortcuts to locate cells impacting the chosen cell value are Ctrl + [ and Alt + M + P.
- The Trace Precedents arrows will not be visible once we save or close the file.
- The Trace Precedents feature appears disabled when the concerned worksheet is protected, or two or more worksheets are selected.
Frequently Asked Questions (FAQs)
You can trace precedents in Excel macro VBA by following the steps below, described with an illustration.
The source dataset holds the parameters associated with a bond, based on which we evaluate the count of coupon payments for the specific bond in cell E4.
The task is to find the cells impacting the cell E4 value in the current sheet. Then, you can utilize VBA coding to secure the desired result.
Step 1: Choose cell E4 in the sheet holding the source dataset. Next, using Alt + F11, access the VBA Editor.
Step 2: Choose the current VBAProject and select Module under the Insert tab.
A blank module will appear.
Step 3: Feed the code in the blank module to show all the cells influencing the chosen cell value in the current sheet.
Step 4: Choose the Play option in the menu to execute the VBA code.
Finally, open the current worksheet to view the Trace Precedents arrows from the precedents to the chosen cell.
The above outcome shows that the cells C2:C5 values impact the cell E4 value in the active worksheet.
The Trace Precedents in Excel is greyed out because the concerned worksheet or workbook is protected, or we have selected multiple worksheets simultaneously.
We cannot save Trace Precedents in Excel. The arrows disappear once we save or close the workbook and we must use the Trace Precedents option to view the arrows again after reopening the workbook.
Download Template
This article must be helpful to understand the Trace Precedents Excel, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is Trace Precedents Excel. Here we learn how to use trace precedent feature in Excel with examples and points to remember. You can learn more from the following articles –
Leave a Reply