RefTreeAnalyser Operating instructions

Pages in this article

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

  • The most recent version is is available for download here, click the link that says "Download RefTreeAnalyser (Free demo!!!)".
  • Unpack the zip file to any folder you like.
  • Open the file called "RefTreeAnalyserXL.xlam" (Excel 2003 users should open RefTreeAnalyser.xla).
  • Follow instructions to install.

Start the tool from the ribbon of Excel 2007 and up, you will find a group on the Formulas tab or on its own tab:

RefTreeAnalyser shown on the Formulas tab
The ribbon tab of RefTreeAnalyser

Or use the cell right click menu:

RefTreeAnalyser right-click cell menu


Buy RefTreeAnalyser now!


All comments about this page:

Comment by: Elliot Seligman (11-9-2009 07:23:18) deeplink to this comment

RefTreeAnalyser --

What is your upgrade / update policy as new versions are released -- are they Free Forever? Minor Upgrades only?


Comment by: Jan Karel Pieterse (11-9-2009 07:55:02) deeplink to this comment

Hi Elliot,

Updates are free until I decide to publish a major new release. I plan to have an upgrade version for a rebate then.

Comment by: Mike Budd (30-4-2010 05:10:10) deeplink to this comment

Just trying out the demo version, which is a great tool!

One small problem, the analyser does not seem to fully deal with named formulae. (Situations where a formula has been typed into the refers to field of a named range). It does sensibly list all the usages of named functions in the same column so I can look at them myself, but it does not select just those that are related to the cell under analysis. Any way round this?

In addition, it gives numeric cell references in the references display, not the names of named ranges. Any way I can toggle the names?

Many thanks again


Comment by: Jan Karel Pieterse (30-4-2010 06:51:02) deeplink to this comment

Hi Mike,

Could you perhaps send me your file (see email address at bottom of this page)?
If so, please include some information on which cell you are analysing and what you get and what you expect to get?

Comment by: Jean-Pierre Degroote (9-12-2012 18:33:05) deeplink to this comment

Dag Jan Karel,

Ik kocht onlangs dit tooltje en heb er reeds goed gebruik van gemaakt maar deze morgen bij het opstarten van Excel krijg ik de melding dat Excel een ernstig probleem heeft met dit tooltje. De Excel Ref Tool tab is nu niet meer beschikbaar. Heb reeds een uninstall en install gedaan maar nog steeds niet. Ik heb Excel 2010 op Windows 7. Wat nu?
Alvast bedankt voor je aandacht.



Comment by: Jan Karel Pieterse (9-12-2012 18:34:05) deeplink to this comment

Hallo Jean Pierre,

Kijk eens onder Excel opties, Add-ins tabje en kies in de dropdown lijst "UItgeschakelde items". Waarschijnlijk staat reftreeanalyser daar abusievelijk tussen.

Comment by: Mario (1-12-2014 01:19:01) deeplink to this comment


Just installed your excellent add-in in my Excel 2013.

I've noticed the backstage is now gone upon firing up Excel.

How could I bring it back, thanks.



Comment by: Jan Karel Pieterse (1-12-2014 11:30:27) deeplink to this comment

Hi Mario,

That is very odd, as RTA does not change backstage view at all. I jus tested on a system with Excel 2013 and it shows up properly on that. Do ou have any other add-ins loaded perhaps?

Comment by: Mario (2-12-2014 01:32:44) deeplink to this comment

Yes, I do, though none of them had exhibited such behaviour before.

Here they go:

ABBYY FineReader 12 MSExcel Add-In
Excel Reader Add-In
Microsoft Office PowerPivot for Excel 2013
Microsoft Power Map for Excel
Microsoft Power Query for Excel
NatSpeak Excel Add-In
Power View
Team Foundation Add-In

All of these are COM add-ins.

I installed your product on a couple of my other machines, same behaviour exhibited (no Backstage View), though these machines also have all the other add-ins installed.



Comment by: Jan Karel Pieterse (2-12-2014 13:46:40) deeplink to this comment

Hi Mario,

What if you disable all other add-ins?
You are the first to report this problem.

The ribbon customisation code of my tool does not contain anything regarding the backstage view, so I'm afraid this is either a problem with your system or some sort of funny interaction with another add-in.

Comment by: Mario (2-12-2014 16:33:17) deeplink to this comment

To be more accurate, the Backstage doesn't appear at startup, it is otherwise available once a file is opened.

I tried switching it on and off from the Options, to see it it'll "shake it up", to no avail.

I will test disabling the other add-ins, but it'll have to wait a few days, unfortunately.

Out of curiosity, do you have your own "Show Backstage at start up" turned on or off?

Comment by: Jan Karel Pieterse (3-12-2014 06:23:09) deeplink to this comment

Hi Mario,

Aha! Now I understand. I have an idea what might be causing this (though it should not happen IMO). Will see if it can be fixed.

Comment by: Mario (3-12-2014 07:53:55) deeplink to this comment

You gotta love those "Aha!" moments :^)

Comment by: Jan Karel Pieterse (4-12-2014 10:51:39) deeplink to this comment

Hi Mario,

I have fixed the issue (at least on my system it now retains the startup screen of Excel 2013).

Comment by: Mario (5-12-2014 00:13:37) deeplink to this comment

Thanks, Jan Karel, that seems to have sorted the problem :^)



Comment by: Mario (5-1-2015 06:34:36) deeplink to this comment

Hi Jan Karel,

I have lost the backstage at startup again. I presume it has to do with some update to Excel, as I haven't changed anything in the meantime.

Could you kindly try to see what's going on, thanks.



Comment by: Jan Karel Pieterse (5-1-2015 10:22:41) deeplink to this comment

Hi Mario,

Looks like Microsoft broke the feature I used to avoid hiding the startup screen. I have reported this to them.

In the mean time, I have updated the tool so it does not use this feature anymore. You'll be prompted to update within the next 7 days.

Comment by: Mario (5-1-2015 12:15:21) deeplink to this comment

Thanks, Jan Karel.

Microsoft should be spanked for being so naughty :^)



Comment by: Mario (4-2-2015 00:36:32) deeplink to this comment

Hi Jan Karel,

I think Microsoft might've broken your start-up sequence again. Shortly after providing the version that fixed the backstage not appearing at startup, it went back to not displaying it again. I kind of got used to it, just wanted to report it to you.

Secondly, does anyone have any experience with the add-ons folder being on OneDrive? I am experimenting with it (so my multiple machines have a synced version -- though any of them would update independently anyway), but I think something is confusing the OneDrive update mechanism as it seems to be "running in circles" and not updating to the latest RefTreeAnalyzer version. Not a biggie, just something you might want to check/investigate, as I can only presume many others might go down this path.



Comment by: Jan Karel Pieterse (4-2-2015 09:43:38) deeplink to this comment

Hi Mario,

This start screen stuff is very annoying. I'll see what I can do :-)

I would not advise using the OneDrive folder for this, as you said, it behaves odd sometimes. As the tool auto-updates anyway there is no need. It does not even save any diskspace as the file will be on each computer connected to OneDrive anyway.

Comment by: Mario (4-2-2015 22:59:22) deeplink to this comment

Hi Jan Karel,

Thank you for the prompt response.

I am personally not fussed about the backstage issue, more important that I can use the tool than worry about who moved my cheese.

As for using OneDrive, absolutely no advantage (you don't save space, downloads, etc., aside that the tool's size is relatively insignificant in today's terms) and you get a lot of OneDrive annoyances.



Comment by: Stanley Silverman (30-3-2016 16:15:12) deeplink to this comment

Hi, Jan Karel -
I recently had to reinstall your excellent product, but made the mistake of doing this from within the zipped folder. I reinstalled by following the instructions, but I still get the various files not found error messages when Excel starts up (Excel 2010, Windows 7). How do I fix the problem?
Thanks for your help!
Stanley Silverman

Comment by: Jan Karel Pieterse (30-3-2016 17:04:37) deeplink to this comment

Hi Stanley,

Open Excel, go to the add-ins dialog and click on each one. Excel will prompt to remove the ones it cannot locate anymore.

Comment by: Stanley Silverman (30-3-2016 18:37:54) deeplink to this comment

Hi, Jan Karel -
Thanks so much for your quick response - it worked!
Thanks again,
Stanley Silverman

Comment by: Stanley Silverman (30-3-2016 23:05:09) deeplink to this comment

Hi, Jan Karel -
The solution only seems to work during the current session.
When I quit out of Excel and then re-launch the program, the same error messages come up again. Somewhere there's a setting or a link that's not being fully updated when I tell Excel to remove the bogus item.
Thanks again,
Stanley Silverman

Comment by: Jan Karel Pieterse (31-3-2016 07:03:05) deeplink to this comment

Hi Stanley,

That is unusual. Perhaps you need to run Excel as an admin for this (though that should not be needed)?

Comment by: C Lee (27-8-2016 17:39:44) deeplink to this comment

Hi, Jan Karel,

I am currently using Office 365 in Win 10, and it seems that I can't open the file by double clicking the file. Please kindly help, thanks!

Regards, C Lee

Comment by: Jan Karel Pieterse (27-8-2016 19:20:34) deeplink to this comment


Probably the file is blocked due to a recent security update. See:

Comment by: Apostolos (5-9-2018 22:47:03) deeplink to this comment

Hi Jan Karel,

I get the following message when RefTreeAnalyser tries to update, either automatically or manually:
"Checking for updates has failed: Unable to fetch latest build number from".

Can you please advise how to resolve?

Many thanks in advance!

Comment by: Jan Karel Pieterse (6-9-2018 09:15:35) deeplink to this comment

Hi Apostolos,

Please download the version from the site and replace your RefTreeAnalyser files on your disk with the ones in the download. I have changes my site from http to https, which causes the update process to fail on the older versions.

Comment by: Apostolos (7-9-2018 17:39:58) deeplink to this comment

Thank you Jan Karel,

Worked like a charm!

Best Regards,

Comment by: Apostolos (11-9-2018 18:17:04) deeplink to this comment

Hi Jan Karel,

I updated RefTreeAnalyser successfully to v2.0 129. While the "Report Formulas" tool worked marvelously in the past, when I am running it now, Column D of the report (headed "Used in"), which includes the address of the ranges where each formula is applied, now shows many "#VALUE!" errors. Further, there are no hyperlinks to the respective ranges, which was an extremely useful functionality of the tool. Do I need to change some kind of setting? I am using the Professional Plus 2016 version.

Best Regards,

Comment by: Jan Karel Pieterse (11-9-2018 18:23:20) deeplink to this comment

Hi Apostolos,

The "UsedIn" column now contains a formula which points to the cells using the formula.

The trick is to use RTA itself to traverse those references, simply by using trace precedents (control+shift+[ short-cut)

Comment by: Wiens (18-4-2020 16:35:00) deeplink to this comment


In the RefTreeAnalyserXL Build 151 there is a typo in the file where the following question is asked during opening of an Excel file after installation:

'This is an unregistered version, updates are diabled. Would you like to purchase a license?'

'diabled' should be 'disabled' in the text.

Comment by: Jan Karel Pieterse (20-4-2020 11:17:00) deeplink to this comment

Hi Wiens,

Thank you!

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:

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