Excel has a very useful feature when it comes to formula auditing: It allows you to insert arrows to all preceding or depending cells. The problem: You can only apply it for one cell at once. But what, if you want to audit a larger range of cells? Here is how to bulk insert trace dependents and precedents arrows at once.
Method 1: Insert arrows manually cell by cell
At first, let’s take a quick look at how to insert the arrows for one cell only. If you know how to do that please proceed to the next method below.
For inserting one arrow at a time, select your desired cell. Then click on “Trace Precedents” or “Trace Dependents” on the Formula ribbon. Now, you can see the arrows.
If you want to clear the arrows, click on Remove Arrows.
Method 2: Bulk insert trace dependents and precedents arrows for many cells
The most convenient method: Use Professor Excel Tools. This Excel add-in comes with an entirely new ribbon full of powerful features.
Two of the features are bulk tracing depending and preceding cells for many cells at the same time.
- Select all the cells you would like to trace.
- Click on either “Trace Precedents” or “Trace Dependents” on the Professor Excel ribbon (highlighted in yellow on the screenshot above).
You can try it for free. Click here and the download starts right away.
This function is included in our Excel Add-In ‘Professor Excel Tools’
(No sign-up, download starts directly)
More than 35,000 users can’t be wrong.
Method 3: Use a VBA macro to bulk trace dependents and precedents
Another option to bulk trace precedents and dependents is via a short VBA macro.
Open the VBA editor, insert a new module, copy and paste one of the following code snippets and press start. Too fast? Here is a short tutorial with more pictures.
Code for showing all precedents:
Sub showAllPrecedents() Dim cell As Range For Each cell In Selection cell.ShowPrecedents Next cell End Sub
Code for showing all depending cells:
Sub showAllDependents() Dim cell As Range For Each cell In Selection cell.ShowDependents Next cell End Sub
I have written a larger guide about tracing depending and preceding cells. Just follow this link to learn more.
Image by Paul Barlow from Pixabay