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.

Trainings

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

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
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. RefTreeAnlayser 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!



Comments

All comments about this page:


Comment by: James (3/26/2009 7:37:09 AM)

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 (3/26/2009 8:55:24 AM)

Hi James,

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

 


Comment by: Bill (7/2/2009 9:56:51 AM)

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

 


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

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 (11/9/2010 12:45:13 AM)

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 (11/9/2010 1:47:58 AM)

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:

=IF(BreakCircle=1,DefaultValueCell,PreviousCircleCell)


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 11:13:56 PM)

Hi!

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 (11/12/2010 12:46:24 AM)

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 (12/21/2011 4:10:16 AM)

As I am working on excel and I have to see many signal are dependent on many things which are coming from multiple sheets.so I have to do explode and see and copy manualy nad prepare the equation in a sheet.so 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 sawan.cute@gmail.com It will be gr8ness.
will be looking forward for your reply

 


Comment by: Bill Benson (12/30/2011 3:08:24 PM)

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.

Bill

 


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

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 (4/17/2012 11:58:07 AM)

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 (8/13/2013 4:15:59 PM)

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

 


Comment by: Jan Karel Pieterse (8/13/2013 5:30:04 PM)

Hi Bill,

Thank you and you're welcome!

 


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

Hi

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 (1/15/2014 2:20:34 PM)

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 (1/18/2015 11:32:55 PM)

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

Thanks,
Beau

 


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

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 (1/21/2015 12:47:11 AM)

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?

Thanks,
Beau

 


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

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 (11/6/2015 5:41:41 PM)

Does this work in the Mac version of Excel?

 


Comment by: Jan Karel Pieterse (11/6/2015 5:49:24 PM)

Hi Peter,

I'm afraid not.

 


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

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 excell 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 (12/7/2015 11:12:09 AM)

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 (12/14/2015 10:24:21 AM)

Thanks it works perfectly.

 


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.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

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