

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
Trace dependents and precedents in Excel.
Here is how the basic functions of tracing depending and preceding cells.
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.
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.
Sub showAllPrecedents() Dim cell As Range For Each cell In Selection cell.ShowPrecedents Next cell End Sub
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:
Hold on a second. Was this information helpful so far?
...or on other networks!
Twitter: Follow @professorexcel
Facebook:
Please use this short VBA macro to clear all blue arrows from all worksheets simultaneously.
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.
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
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.
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.
Unfortunately, only the first of the types above can be checked with the “Trace Dependents” formula.
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.
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.
'Professor Excel Tools': Add more than 60 amazing features to Excel!
Professor's Error Helper for Excel: Solve any error in Excel.
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
Subscribe to our popular Excel newsletter! You get all this:
Your welcome gift: Our big 45 pages keyboard shortcuts package.
Subscribe now! In case the sign-up form above doesn't work, please use this page. Sorry for the inconvenience.
Please use this download link to download the Currency Converter Excel add-in!
Please use this download link to download the comment extraction example: http://bit.ly/Extract_Comment_Example