Most Valuable Professional

View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

File crashes Excel!!

Save my workbook!
The best tool for salvaging problematic workbooks.


Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

The best tool to optimise your Excel model!

What has changed?

PerfectXL Compare, for Spreadsheet Comparison
This Excel compare tool is fast, reliable, and easy to use. Compare two Excel files now and see for yourself!
Home > English site > Products > RefTreeAnalyser > Why RefTreeAnalyser?
Deze pagina in het Nederlands

Why RefTreeAnalyser?

Easily navigate the reference tree

The formula auditing capabilities of Excel are limited in that they display a bunch of arrows from the current cell to its dependents and/or precedents. Navigating these arrows is relatively hard to do, especially if there are more than just one off-sheet reference and the combination of workbookname and sheetname is long (you're unable to see the cell addresses):

Buy RefTreeAnalyser now!

Moreover, the dialog shows all individual cells, rather than grouping contiguous ranges of cells.

RefTreeAnalyser does a much better job here:

Buy RefTreeAnalyser now!

The RefTreeAnalyser greatly eases the navigation of dependents and precedents. You simply click on a found reference in a simple tree-like structured view and the accompanying cell or range of cells is selected.

If you prefer a more visual way of detecting precedents, try the visualize option:

Visualize option of RefTreeAnalyser

Buy RefTreeAnalyser now!

Find out what your objects are doing

It is hard to see which cells are used by objects in Excel. RefTreeAnalyser helps you with this:

RefTreeAnalyser Object References Screen

Buy RefTreeAnalyser now!

Quickly see what cells are referred to

Although Excel does enable you to see what cells precede your cell and/or what cells use the current cell, getting an overview of them is quite hard. See the example below...

The RefTreeAnalyser greatly simplifies this as well: you get one tree-like overview of all cells that depend on the active cell and all cells that precede the current cell (and both may go up to 5 levels deep down/up the dependency tree!). The screenshot below shows what the same analysis looks like with RefTreeAnalyser:

RefTreeAnalyser also scans your on-sheet objects (such as charts, pivot tables, form controls, ...). If it finds your analysed cell is within their source range it will show that object in the dependents list too, in black font (not shown in the screen shots here). If your cell contains data validation formulas or conditional formatting formulas, those will be analysed for precedents or dependents too.

Buy RefTreeAnalyser now!

Locate all circular references

Excel does detect if a workbook contains circular references, but only enables you to navigate one. The RefTreeAnalyser detects all your circular references and shows them to you in one -easy to navigate- treeview.

Circular references

Buy RefTreeAnalyser now!

Get insight in your workbook structure

RefTreeAnalyser allows you to get a view on which worksheets in your workbook are using data from what other worksheets. Note that the arrows related to the blue colored worksheet are highlighted in color:

Circular references

If you click any of the sheet boxes you get detailed information about the sheet:

Circular references

Buy RefTreeAnalyser now!

Generate a report quickly

Excel's formula auditing tools do not have any reporting options.

The RefTreeAnalyser comes with a Report option which writes the current analysis to a new worksheet in your workbook. The report may look like this:

Buy RefTreeAnalyser now!


All comments about this page:

Comment by: James (26-3-2009 07:37:09)

How does your tool handle named ranges. Does it display the named range or the underlying cell reference? cheers -j


Comment by: Jan Karel Pieterse (26-3-2009 08:55:24)

Hi James,

It shows the named range at level 1, for level 2 refs it shows addresses.


Comment by: Bill (2-7-2009 09:56:51)

Does it allow you to jump between different tabs to trace a precedent or dependent?


Comment by: Jan Karel Pieterse (2-7-2009 12:18:31)

Hi Bill,

Yes it does. And if there are external links and the source file(s) are open those will be shown and travelled too.


Comment by: Chun Lee (9-11-2010 00:45:13)

Does the RefTreeAnalyser pinpoint the specific cell to break the loop?

Does the RefTreeAnalyser give suggestion on how do break the Circular Reference identified?


Comment by: Jan Karel Pieterse (9-11-2010 01:47:58)

Hi Chun Lee,

Not exactly. It shows you all cells that form the circle. It is up to you to decide how to break the circle, since that is too closely tied into your model for RefTreeAnalyser to assess.

A general advice on circular references:
Always include one cell within each circle which can function as a break-the-circle cell and (at the same time) enables you to set default values to start the iteration.
Typically, this cell would contain an IF function like this:


In this example:
BreakCircle: A named cell in which you can enter a 1 to break the circle and reset the values

DefaultValueCell: A named cell that holds the starting value for the iteration

PreviousCircleCell: The previous cell in the circle


Comment by: Chun Lee (11-11-2010 23:13:56)


I use the RefTreeAnalyser to find the circular reference in the individual worksheet of a workbook. The result is no circular reference is being found. I separate the worksheet from the workbook by creates a new worksheet in a new book.

But, the RefTreeAnalyser found many circular references when the RefTreeAnalyser is used on the workbook. It hangs after the record show more than 200 circular references. Please advise how to solve this problem. I would like to break or avoid these circular references. Thanks.


Comment by: Jan Karel Pieterse (12-11-2010 00:46:24)

Hi Chun Lee,

By separating the worksheet maybe you have broken the circular references. It is also possible that you created circular references accross several workbooks. If the workbook contains links to other workbooks, make sure you open all linked files before running the circular reference check.


Comment by: karu (21-12-2011 04:10:16)

As I am working on excel and I have to see many signal are dependent on many things which are coming from multiple I have to do explode and see and copy manualy nad prepare the equation in a I want that if I explode one signal then all dependents from multiple sheet should be fetched and get added in a particular sheet.
If any one could do it pls mail me at It will be gr8ness.
will be looking forward for your reply


Comment by: Bill Benson (30-12-2011 15:08:24)

JKP, Hi again. Does this software allow you to navigate to ranges in the precedents and/or dependents area while KEEPING the original item (and list of precedents and/or dependents) so that one may fully analyze a single starting point to one's satisfaction... or does each navigation cause an update to the analysis? If this question doesn't make enough sense to you, I might answer it for myself if I come back to this page and study a little harder what you're already saying the product does. Sure looks handy.

Meanwhile I am off to go play with Name Manager.



Comment by: Jan Karel Pieterse (1-1-2012 11:58:48)

Hi Bill,

It does not currently keep the analysis results, just the cells you analysed. If you don't dive in too many levels, it is fast enough to redo a cell though. Just DL the demo version, it should give a good idea.


Comment by: mike (17-4-2012 11:58:07)

This was a huge help and made it much easier to retrace circular references. I inherited a very complex model and have been in "circular reference hell" for the past couple days. I've made more progress since I downloaded this an hour ago than I had in the past 6 hours.


Comment by: Bill McNair (13-8-2013 16:15:59)

This is a wonderful Excel Add-In. Thank you! Glad to see the automated updates.


Comment by: Jan Karel Pieterse (13-8-2013 17:30:04)

Hi Bill,

Thank you and you're welcome!


Comment by: RP (15-1-2014 11:24:09)


First let me say this is a fantastic tool . I use it on a daily basis . Unfortunately , since the last upadte (object search being added ) I've been experiencing some errors :

Error -2147467261: This object is no longer valid. in modObjSearch.ScanSheet

Error 424: Object required in modObjSearch.ScanSheet

Any ideas ?

thanks ! RP


Comment by: Jan Karel Pieterse (15-1-2014 14:20:34)

Hi RP,

Thanks for letting me know.

I have two questions if I may:

1. Which build of the tool are you currently using?
2. What Excel version do you use?


Comment by: Beau (18-1-2015 23:32:55)

Is this compatible with 64 bit excel? When you use it, is the list of undo actions cleared?



Comment by: Jan Karel Pieterse (19-1-2015 11:04:23)

Hi Beau,

Yes it supports 64 bit Office.
And of course it zaps undo in Excel (but only if VBA changes anything in Excel).


Comment by: Beau (21-1-2015 00:47:11)

Thanks for the feedback. Can you be more specific? It does or does not zap the undo memory? Is there a trial version I can try?



Comment by: Jan Karel Pieterse (21-1-2015 11:11:52)

Hi Beau,

Some actions zap the undo stack, others do not. For instance, visualising the cell references zaps the undo stack, but using the trace precedents dialog doesn't.
The download is a free demo.


Comment by: Peter (6-11-2015 17:41:41)

Does this work in the Mac version of Excel?


Comment by: Jan Karel Pieterse (6-11-2015 17:49:24)

Hi Peter,

I'm afraid not.


Comment by: victor loisel (3-12-2015 12:11:56)

Hy, i download a demo this beautifull tools to test but i have some issues :

1. regulary the refTreeAnalyser ribon disapear and i have to reinstal it to make it appear.

2. each time i open Excel i have a warning message :
'c:\Users\....\Local\Temp\Temp1_reftreeanalyser.xlam' not found. and the refereeTreeAnalyser is not present

3. i have a bug when i ask for dependents it don't work :
03/12/2015 11:40:18         RefTreeAnalyserXL.xlam     Error 1004: La mĂ©thode 'Intersect' de l'objet '_Global' a Ă©chouĂ© in GetDependents.Form ufReferences

Yhanks for the help


Comment by: Jan Karel Pieterse (7-12-2015 11:12:09)

Hi Victor,

This is because you opened the add-in file directly from inside the zip file.

The proper way is:

- Download latest version from my website
- Unpack all files in the downloaded file to any folder on your system
-Now open the xlam file and click Yes to install.


Comment by: victor loisel (14-12-2015 10:24:21)

Thanks it works perfectly.


Comment by: William Howard Benson (22-6-2018 19:11:30)

Jan Karel, does this tool work for Excel tables, which are excluded (very regrettably) from Excel's Formulas Trace Precedents feature?


Comment by: Jan Karel Pieterse (22-6-2018 20:13:10)

Hi Bill,

Yes, it does!


Comment by: Alexandra Rabman (16-4-2019)

Hi there - I have an old version of this tool and LOVE it, but when I updated my Excel to 64-bit on my new laptop I was getting a message that the VBA was incompatible. Does this version work for 64-bit Excel? Or, do you know what the problem could be with my incompatible message?


Comment by: Jan Karel Pieterse (17-4-2019)

Hi Alexandra,

Hmm, very strange since I've tested the tool on 64 bit Office 2010 and 2016 and have no errors on my end. Can you please send a screenshot of the error to me using the email address shown below?


Comment by: Alexandra Rabman (17-4-2019)

Hi Jan - thanks so much for writing back. I ended up just purchasing a new license for the current release and now everything is working just fine!


Comment by: Jan Karel Pieterse (18-4-2019)

Hi Alexandra,

Outstanding, thanks.


Comment by: Juan Figueroa (20-5-2020 22:57:00)

Hi Jaen,

I been using the demo version of your code and was wondering how can I disable the RefAnalyzer so it doesn't ask me for the password of protected sheets everytime I try to trace back precedents.

I find this request annoying as I typically audit protected model and this issue is slowing me down. Any suggestions on how to solve this?



Comment by: Jan Karel Pieterse (21-5-2020 12:23:00)

Hi Juan,

Good suggestion, I'll add an option to disable this!


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here:

Please enter your name (required):

Your e-mail address (optional, will only be used to inform you when your comment is published or to respond to your question directly):

Your request or comment (max 2000 characters):

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].

I give permission to process this data and display my name and my comment on this website accoring to our Privacy Policy.