Precedents and Dependents: Easily Trace Related Cells in Excel
Let’s assume the following situation: You receive an Excel file from your colleague and have to understand it as quickly as possible. How do you start? In this article we’ll learn how to trace depending and preceding cells in Excel. The functions in Excel are called “Trace Dependents” and “Trace Precedents”.
Contents
Functions “Trace Dependents” and “Trace Precedents”
Basic usage
Trace dependents and precedents in Excel.
Here is how the basic functions of tracing depending and preceding cells.
- Before you start, select one cell. In the example on the right-hand side, it’s the result in cell C12.
- Then go to the “Formulas” ribbon.
- Within the “Formula Auditing” group, you have the following three choices.
- By clicking on “Trace Precedents”, blue arrows show the direct input cells. By clicking it the button again, the input cells of these input cells will also be shown and so on.
- The same way you can see the cells, which use the current cell as an input value. Just click on “Trace Dependents” for displaying the arrow in the opposite direction.
- By clicking on “Remove Arrows”, all arrows will be hidden.
The dotted line indicates that the cell is used on another worksheet.
Please note, that a black dotted arrow with some table symbol indicates, that some input cells are located on another worksheet. Double-click on the black dotted arrow and then double-click on one of the list entries to see the corresponding input cell.
Show all dependents and precedents for multiple cells simultaneously
Unfortunately, you can’t check the relations of multiple cells at the same time. Even if you select a cell range and click on on either “Trace Precedents” or “Trace Dependents”, only the arrows for one cell will be displayed. However, the following short VBA macros help you to highlight all relations of a selected cell range.
|
1 2 3 4 5 6 |
Sub showAllPrecedents() Dim cell As Range For Each cell In Selection cell.ShowPrecedents Next cell End Sub |
|
1 2 3 4 5 6 |
Sub showAllDependents() Dim cell As Range For Each cell In Selection cell.ShowDependents Next cell End Sub |
Show all dependents of a selected cell range.
Follow these steps to use the code above:
- Select a range of cells. Please keep in mind that there should be formulas within the selected cells. Otherwise you won’t see any arrows.
- Open the VBA editor by pressing Alt + F11 on the keyboard. Insert a new module.Please refer to this article for a description of how to insert a module.
- Copy and paste the code above into a VBA module. Place the cursor into one of the “SUBS”:
- Move the cursor to showAllPrecedents if you want to display the arrows of all preceding cells.
- If you on the other hand want to see all dependents, place the cursor into the SUB showAllDependents.
- Click on start on the top of the editor window.
Hold on a second. Was this information helpful so far?
If yes: Why don't you subscribe to our monthly, free Excel newsletter? You get all this:
The best Excel tips, tricks and tutorials.
1x per month.
No spam. Promised.
Subscribe now!
In case the sign-up form above doesn't work, please use this page. Sorry for the inconvenience.
Clear all arrows from all workbooks at the same time
Please use this short VBA macro to clear all blue arrows from all worksheets simultaneously.
|
1 2 3 4 5 6 |
Sub clearAllArrowsInCurrentWorkbook() Dim ws As Worksheet For Each ws In Worksheets ws.ClearArrows Next ws End Sub |
Just copy and paste this code into a new VBA module, place the cursor into the code and press the play button on top of the VBA editor window. If you need assistance with VBA macros, please refer to this article.
For experts: Use Professor Excel Tools
Trace all precedents and dependents with Professor Excel Tools
You want to simplify this process? Use Professor Excel Tools. The Excel add-in helps you to
- show all precedents of the selected cells,
- show all dependents of the selected cells and
- hide all arrows from all worksheets.
Just select the cells you want to highlight the precedents and dependents of and press the button of Professor Excel Tools.
Try Professor Excel Tools for free now. Click here to learn more!
This function is included in our Excel Add-In 'Professor Excel Tools'
Learn more Download Free Trial
(No sign-up, download starts directly)
More than 10k people on Facebook can't be wrong.
How to check, if a cell is used in another formula
Here is another use of this functions to trace precedents. Sometimes you want to delete a cell (or a certain worksheet), but you are not sure if you still need that cell. You have to differentiate various types of dependent cells.
Types of dependents
- The cell is directly linked or referred to. For example, cell A1 might be used within a formula in cell B1 in such way =A1+A2 or within a formula, e.g., =SUM(A1:A2).
- The cell is indirectly linked to, e.g. with the INDIRECT or OFFSET formula. Also VLOOKUP and HLOOKUP might use your cell as a return value.
- The cell is used in a VBA macro.
- Cell is used in a defined name.
- Cell is part of the source of a chart.
- Cell is part of the source of a PivotTable.
- The cell is referred to by another workbook.
- The cell is used in a conditional formatting rule.
Unfortunately, only the first of the types above can be checked with the “Trace Dependents” formula.
Excel too slow? Speed it up. Get the book now!
Tired of waiting for Excel? Use the 30 best methods described in this book to speed up Excel calculations!
- Learn how Microsoft Excel performs calculations
- Use the simple and effective step-by-step guide to master each method
- Get to know the impact each method will have on performance
Learn more or get it on Amazon!
Check if cell is direct input to another cell
Concerning item 1 above: You could use the “Trace Dependents” function to check, if your selected cell serves as an input for another cell. Just select the cell and click on “Trace Dependents”. If the error message “The Trace Dependents command found no formulas that refer to the active cell” (6) appears after clicking on “Trace Dependents”, you could most probably delete that cell.
Strategies to check if cell is referred to by an INDIRECT or OFFSET formula
Concerning item 2 above: Unfortunately, there is no universal way of checking, if a cell is still in use by another INDIRECT or OFFSET formula. But you could use some rather rough approaches.
- Check if your workbook has are any INDIRECT or OFFSET formulas. You could do this by searching for “INDIRECT” on all worksheets. If there are no INDIRECT formulas in your workbook, you can stop here. Do the same for OFFSET.
- If there are just a small amount of INDIRECT formulas in your Excel workbook, change the value within the cell in question and check if any of your INDIRECT values change. This method is unfortunately not 100% safe because the cells with the INDIRECT formula might not change the value (e.g. through the IF- of IFERROR formulas).
