Bulk Trace Dependents & Precedents: How to Add Arrows for Many Cells!

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.

1. Select all the cells you would like to trace.
2. 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.

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

Further reading

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