Archive for June 10th, 2013

Excel Tip: Trace Precedents / Dependents June 10th, 2013

Vinod Kumar

I am a big fan of Office products (Word, Excel, PowerPoint, Access and OneNote) and I always find it interesting to share my tips with customers almost every single day. I have found it tough to explain them over my blog till date except for one post (Excel Tip – Find invalid data). I am sure if you have played around with Excel 2013 there are loads of features to look out, I am not going to explain the common super cool features of PowerPivot, PowerView, GeoFlow etc. Those will be way in the future maybe :).

So stay tuned because this week will be “Excel 2013 Tips and Tricks” that I love to share with customers daily and now with you. Some of these features are already available in previous versions but they may look refreshing every single time. Incase you don’t find the option I am talking, it is an Excel 2013 feature.

Trace Precedents and Trace Dependents

In this blog post, we will look at the options available in “Formula’s” toolbar of Trace Precedents and Trace Dependents. We can will find these options:

Let us assume we have a P&L statement which is using tons of Excel references to different cells. In our below example, you can see that we have selected the cell “D13”.

This is a very big Excel sheet spanning multiple sheets and running close to 80-90 rows of cross references.

Trace Precedents

Now let assume you want to check which are the values that affect the value in cell “D13”. This is where “Trace Precedents” help. Click the same from “Formulas” toolbar.

You will now magically see an arrow (as above), this means the value in D13 is dependent on values coming from “B11” in our example. If there are other dependent values then we will get multiple arrows. Here is a typical example below:

Trace Dependents

Now the “Trace Dependents” on “Formulas” toolbar does exactly the opposite. This option shows which are the cells affected by values from this cell. In our example it is cell “D13”. Interesting part is, if we click the “Trace Dependent” again, it runs the dependency in recursion. It can give you an visual treat like below on values that can get affected because of “D13” (in recursion) in our example.

This is a powerful representation and can be quite useful too. To remove the arrows, use the “Remove Arrows” button from “Formulas” toolbar.


How cool is this? Did you find it useful? Have you used this feature in the past? Do let me know how you have used this feature in your daily life. More such fun coming your way this week :).

Continue reading...