Why RefTreeAnalyser?

Pages in this article

  1. Why RefTreeAnalyser?
  2. Screenshots of RefTreeAnalyser
  3. Version information
  4. Operating instructions

Download

The most recent version is is available for download here, click the link that says "Download RefTreeAnalyser (Free demo!!!)".

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!

Excel goto dialog showing off-sheet references which are hard to read

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

RefTreeAnalyser does a much better job here:

RefTreeAnalyser showing dependents of a cell

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...

A formula containing worksheets with a long name

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 showing a formula with long sheetnames, easier to read

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 shown by RefTreeAnalyser

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 precedents report may look like this:

RefTreeAnalyser report of cell precedents

And the Formula report looks like this:

RefTreeAnalyser report of workbook formulas

Buy RefTreeAnalyser now!



Comments

Showing last 8 comments of 37 in total (Show All Comments):

 


Comment by: Alexandra Rabman (17-4-2019) deeplink to this comment

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 (17-4-2019) deeplink to this comment

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: Jan Karel Pieterse (18-4-2019) deeplink to this comment

Hi Alexandra,

Outstanding, thanks.


Comment by: Juan Figueroa (20-5-2020 22:57:00) deeplink to this comment


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?

Thanks.


Comment by: Jan Karel Pieterse (21-5-2020 12:23:00) deeplink to this comment

Hi Juan,

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


Comment by: Sergi (15-7-2020 12:05:00) deeplink to this comment

We need uninstall ref tree analyzer.
Can you tell me who to proceed it
Thanks
Sergi


Comment by: Jan Karel Pieterse (18-7-2020 19:06:00) deeplink to this comment

Hi Sergi,

Click File, options Addins tab, click Go and uncheck Reftreeanalyser


Comment by: Brett Gaspers (22-10-2020 19:12:00) deeplink to this comment

Hi Jan:

Does RefTreeAnalyser work on protected workbooks/worksheets? This may have been answered somewhere in the 200 comments, but thought I'd ask directly. If so, please consider adding to the features list (built-in Excel trace precedents/dependents doesn't work on protected sheets).

Thanks,
Brett


Comment by: Jan Karel Pieterse (23-10-2020 14:37:00) deeplink to this comment

Hi Brett,

If you check the "Unprotect sheets" box in settings it will unprotect sheets and prompt you for a password if a sheet is protected with a password. It will try to unprotect all sheets using that password.


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: www.eileenslounge.com.




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