Why RefTreeAnalyser?
Pages in this article
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 now (Paypal and all major credit cards supported)
Moreover, the dialog shows all individual cells, rather than grouping contiguous ranges of cells.
RefTreeAnalyser does a much better job here:
Buy now (Paypal and all major credit cards supported)
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:
Buy now (Paypal and all major credit cards supported)
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:
Buy now (Paypal and all major credit cards supported)
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...
RefTreeAnalyser greatly simplifies this as well: it shows 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 now (Paypal and all major credit cards supported)
Table of Content
Documenting spreadsheets is hard work. Let RefTreeAnalyser be of help and have it generate a complete Table Of Content for you, including:
- Worksheets
- Tables
- Charts
- Pivot tables
- Controls
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. hover your mouse over the screenshot below for a short animated gif.
Buy now (Paypal and all major credit cards supported)
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:
If you click any of the sheet boxes you get detailed information about the sheet:
Buy now (Paypal and all major credit cards supported)
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:
And the Formula report looks like this:
Buy now (Paypal and all major credit cards supported)
Comments
All comments about this page:
Comment by: James (26-3-2009 07:37:09) deeplink to this comment
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) deeplink to this comment
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) deeplink to this comment
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) deeplink to this comment
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) deeplink to this comment
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) deeplink to this comment
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) deeplink to this comment
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 (12-11-2010 00:46:24) deeplink to this comment
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) deeplink to this comment
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 (30-12-2011 15:08:24) deeplink to this comment
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) deeplink to this comment
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) deeplink to this comment
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) deeplink to this comment
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) deeplink to this comment
Hi Bill,
Thank you and you're welcome!
Comment by: RP (15-1-2014 11:24:09) deeplink to this comment
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 (15-1-2014 14:20:34) deeplink to this comment
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) deeplink to this comment
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 (19-1-2015 11:04:23) deeplink to this comment
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) deeplink to this comment
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 (21-1-2015 11:11:52) deeplink to this comment
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) deeplink to this comment
Does this work in the Mac version of Excel?
Comment by: Jan Karel Pieterse (6-11-2015 17:49:24) deeplink to this comment
Hi Peter,
I'm afraid not.
Comment by: victor loisel (3-12-2015 12:11:56) deeplink to this comment
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) deeplink to this comment
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) deeplink to this comment
Thanks it works perfectly.
Comment by: William Howard Benson (22-6-2018 19:11:30) deeplink to this comment
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) deeplink to this comment
Hi Bill,
Yes, it does!
Comment by: Alexandra Rabman (16-4-2019) deeplink to this comment
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: 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.