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

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

 


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