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.


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

Third party tools

Speed up your file

The best tool to optimise your Excel model!

What has changed?

PerfectXL Compare, for Spreadsheet Comparison
This Excel compare tool is fast, reliable, and easy to use. Compare two Excel files now and see for yourself!
Home > English site > Products > RefTreeAnalyser
Deze pagina in het Nederlands

RefTreeAnalyser: The ultimate Excel Formula Auditing tool

For Microsoft® Excel®

Excel formula auditing taken to the next level!



A single user license for RefTreeAnalyser costs € 30 (excluding local VAT).

A license includes:

Buy RefTreeAnalyser now!


  Price per license
  € 30.00
  € 23.00

Contact me if you'd like to order more than 50 licenses

Free Download

Use the links below to download the tool.

The tool will work without a license code but with (only slightly) limited functionality. After purchasing a license, you can simply enter the license code into the tool's settings dialog. The limitations will be removed instantly.

Current Excel versions (Excel 2007 and up)

Download RefTreeAnalyser for Excel 2007 to 2016 (Free demo!!!) V2.0 (Build 154, Updated Nov 19, 2020, downloaded: 16.394 times)

Excel 2003

Please note that the 2003 version will no longer be updated!

Download RefTreeAnalyser for Excel 2003 (Free demo!!!) V2.0 (Build 082, Updated Dec 15, 2015, downloaded: 2.689 times)

Money back guarantee

If you are in any way dissatisfied with RefTreeAnalyser, contact me within 30 days after your purchase and I'll issue a refund.

Buy RefTreeAnalyser now!

What is RefTreeAnalyser?

Ever had to work out the logic of other people's Excel files? Ever had to untie the spaghetti-knots of a large Excel workbook's formulas? Then you know what a nightmare this can be!

Now there is the RefTreeAnalyser, the must-have expansion of your Formula tab of the Ribbon!

Screenshot of RefTreeAnalyser Visualize option of RefTreeAnalyser

With this tool, finding out how a cell in a workbook derives its results and what other cells depend on the cell is a breeze. RefTreeAnalyser makes tracing precedents and dependents a lot easier than Excel's built-in tools. Read more about RefTreeAnalyser.

Supported Excel versions

RefTreeAnalyser is designed to work with Excel 2000 and up, including all 64 bit Excel versions.

More information

Buy RefTreeAnalyser now!



All comments about this page:

Comment by: Doug Glancy (17-12-2007 13:49:47)

Jan Karel,

Today I had need for such a tool so I downloaded the demo. I clicked on a cell and
chose "dependents". It started going through every row of another sheet which has
about 1,300 rows. It was taking quite a while, so I hit stop and after about a
minute it did. I wonder if Application.ScreenUpdating would speed things up?

I then ran the uninstall from Add/Remove progams. It reported success, but the
2-icon toolbar was still in Excel. When I clicked on the left-hand button, I got the
same message I'd get if I chose Tools>Formula Auditing>Trace Dependents. I deleted
the toolbar in Tools>Customize and it's gone for good.

I have 2 versions of XL on this computer 03 and 07. The toolbar is only present in
03 - I'm not sure if it was ever installed in 07.

I thought you might like to know this.



Comment by: Jan Karel Pieterse (17-12-2007 22:24:07)

Hi Doug,

Well, some food for thought there obviously. Screenupdating IS turned off as far as
I know, but I'll double check. I also know about the toolbar problem and this is
scheduled to be fixed in a later version.
In 2007 you should look on the Formulas tab, no toolbar there.


Comment by: Kristi (25-2-2008 15:26:25)

It would be helpful to see a screenshot of the report...


Comment by: Kevin H. Stecyk (7-4-2008 06:24:23)

I really enjoy using your program. I've been using it steadily, and it certainly
helps reduce the burden of understanding someone else's mess, I mean spreadsheet.


Comment by: Jan Karel Pieterse (8-4-2008 00:53:21)

Hi Kevin,

Thanks!! Much appreciated.


Comment by: John Russell (4-7-2008 20:34:07)

Hi, I have just installed your demo version of RefTreeAnalyzer and on opening Excel
I get the following error:

Oops, An error has occurred in RefTreeAnalyzer
Error 13: Type mismatch in modMenu.CreateMenu
Abort - Retry - Ignore

Retry doesn't help so I have gone for Ignore and it appears to work, although it
displays my RC references as A1 refs which is confusing.

I am running Excel 2003 SP3, XP SP2.

Would appreciate any suggestions you can offer.


Comment by: Jeff K (22-9-2008 16:47:46)


This is a really great tool. I just started using the paid version, and it is
incredibly helpful for me to debug spreadsheets.

I believe that there is a defect whereby a sheet name containing a single quote
(e.g., FTE's_Single Site) is not 'auditable' (i.e., when a cell from this sheet
shows up in Dependants, clicking on it does not take me to the cell). When I remove
the single quote from sheet name, all works fine. Please fix, if possible.



Comment by: Jan Karel Pieterse (23-9-2008 01:25:00)

Hi Jeff,

Thanks for the kudo's.
I'll look into the quote problem, definitely a bug.


Comment by: Norm (26-5-2010 07:46:38)

I purchased your reftree analyser Feb 19 2009.
Even with it's quirks, it's a handy add-in to have around.

Is there anyway I can determine the license no. of the installed program.

I'm transitioning to a new computer and want to re-install reftree.


Comment by: Jan Karel Pieterse (26-5-2010 08:09:52)

HI Norm,

You should have an email from 'webmaster at' which contains your registration code.
If you misplaced the message, send me an email (see address below) and I'll re-send it to you.


Comment by: Sue (26-7-2010 09:21:21)

I bought reftreeanalyser last year and find it very useful.

Occanionally Excel crashes and the menu has dissapeared. However I but cannot get it to appear anymore though there is no error meesage when I download it and enter the licence key. And Reftreelauncher is listed under available Excel addins.

I am using a new windows 7 machine now, and office 2007.

Grateful for any suggestions here,



Comment by: Jan Karel Pieterse (16-8-2010 03:10:05)

Hi Sue,

Maybe the addin has been disabled by Excel. Check:

Office button, Excel Options, Addins tab, in the dropdown, select "Disabled Items" and click the Go button. See if the reftreeanalyser is in that list. If so, select and click enable.


Comment by: Nikhil Mehta (23-8-2010 00:45:35)

I am using your tool for MS Excel spreadsheet in Windows. Do you have the same tool for MS Excel for MAC.


Comment by: Jan Karel Pieterse (23-8-2010 03:53:06)

Hi Hikhil,

I'm sorry, this addin is only available for the Windows version of Office.


Comment by: William McNair (19-11-2010 21:36:44)

Any word on Excel 2010 64bit version. I started using this add-in on Excel 2007 32bit and now can't live without it. If needed, I will pay for an upgrade!


Comment by: Jan Karel Pieterse (21-11-2010 22:21:44)

Hi William,

I'm working on a 64 bit version, but it is not ready yet. It will take at least a couple of months to reach beta stage.


Comment by: Andrew Hooks (18-1-2011 02:43:35)

Is there any way to get your RefAnalyser tool to work on a locked PC? The Setup.exe is blocked but I thought I might be able to copy the files manually.



Comment by: Jan Karel Pieterse (18-1-2011 03:45:29)

Hi Andrew,

Well, I could send you the dll, but you would still need administrator privilidges to register the dll on your system.
And then you would also have to put the proper files in place (there is a helper Excel add-in involved to register some shortcut keys which is also needed and a Help file).

This is what the installer takes care of.


Comment by: CHANDRU (3-5-2011 17:25:08)

Hi, I want to know how to nest different conditions in single cell. Eg., Row A1=X, Row B1=Y, Row C1=Z the row D1 = Autopopulated. If Rows A1, B1 and C1 are dropdown lists, how to nest and autopopulate in row D1 for possiblities in rows A1, B1 & C1... currently, all conditions are in different rows with formula =IF(AND(A1="A",B1="R",C1="T"), "Template Name","Error")

I would like to know if your tool can help me out on this function.



Comment by: Jan Karel Pieterse (4-5-2011 00:00:28)


My tool is built to help you analyse from where a cell derives its results and which cells depend on the current cell. It does not help you create a function.
From your message I suspect you are asking how to create a cell formula that achieves a certain goal, but I am not sure I understand what the formula has to do exactly.


Comment by: Fabio (28-10-2011 11:36:12)

Hi, When do you think the 64 bit version would be available?

I just installed Office 2010 and I am desperate!!

Thanks, Fabio


Comment by: Jan Karel Pieterse (31-10-2011 01:09:13)

Hi Fabio,

Not any time soon I'm afraid.
Why have you installed 64 bit Office if I may ask?
Unless you are working with huge (and I mean huge) Excel files, there is not much benefit in using 64 bit Office.


Comment by: Bernard Lebelle (4-11-2011 02:33:11)

Does it work on Macintosh ?


Comment by: Jan Karel Pieterse (4-11-2011 05:05:53)

Hi Bernard,

Unfortunately not!


Comment by: yves labidoire (31-1-2012 00:13:10)

Here is what i need and I think your tool could help but not 100 sure.
from one cell i need to know the entire fully exhaustive dependency tree. why it seems is what this tool does. But then i would like to recalculate the entire tree in excel. for this i need acess in text flie to all the data so that i can ask excel to recalculate all cells.


Comment by: Jan Karel Pieterse (31-1-2012 06:06:47)

Hi Yves,

The tool gives you the precedents/dependents of the active cell in a number of levels you can select.
The max number of levels is set to 5 however, as otherwise you may end up waiting forever for the tool to finish. Also, the dependency tree becomes very difficult to understand with so many levels.
I'm not sure I understand why you need the text file?


Comment by: Robert (9-2-2012 18:26:59)


This is a great tool! I'm currently using the demo version but am thinking of upgrading to the full version. One question - when using the tool in tile mode, is it possible to return focus to the spreadsheet window using the keyboard and without closing the tool window? I have tried "ALT + TAB" but I can't seem to go back to the spreadsheet window after activiating the tool. I ask as I use keyboard shortcuts alot and it would be good to keep the tool window up while auditing formula to avoid the windows resizing every time the tool is activate.




Comment by: Jan Karel Pieterse (10-2-2012 00:57:23)

Hi Robert,

Unfortunately there is no shortcut for this yet. Good suggestion though, I'll put it on the list of improvements.


Comment by: Dan C (17-4-2012 13:40:27)

Can you give an update for when this will be available for the 64 bit version of Excel? I've read about this product and think it could help, but I just switched to the 64 bit 2010 version of Office.

Weeks/months/years if you were to guess?


Comment by: Jan Karel Pieterse (17-4-2012 21:20:33)

Hi Dan,

Good question! So far, work-for-hire has been getting in the way time and again for the past year or two, so it is probably years rather than months.

I could (just for you) port the solution back to an Excel add-in, making it 64 bit compatible. But you'd pay the hours I would need.


Comment by: Dan C (27-4-2012 10:36:16)

My IT dept confirmed that they wouldn't be able to switch me to 32 bit, so I am out of luck I'm afraid... If you had to guess, how many hours/$ per hour would that endeavor take?


Comment by: Jason (26-2-2013 09:18:27)

Is there a 64 bit version that will be made available?


Comment by: Jan Karel Pieterse (26-2-2013 11:14:38)

Hi Jason,

Not yet, but I am working on one.


Comment by: Jason (3-5-2013 01:57:15)

This is awesome Jan. Love the update for 64 bit Excel. Is there a way to customize the keyboard shortcut key that launches the tool? Thanks


Comment by: Jan Karel Pieterse (3-5-2013 11:29:23)

Hi Jason,

Thanks for the compliments, much appreciated.

I'll add this one to the update wish-list.


Comment by: Jason (4-5-2013 01:39:04)

Another point is that when I call the reftree, the box comes up in the middle of the window. I tried resizing and changing the location to the right of the window. However, every time i call the reftreeanalyzer, it comes back up in the middle, where it blocks the spreadsheet. Just a thought


Comment by: Jan Karel Pieterse (4-5-2013 10:45:32)

Hi Jason,

Thanks. I guess you want the tool to remember its window position. It used to do that, but I had to make changes related to Excel 2013 to make certain things work properly. I'll revisit that code some day.

If you click the update button on RefTreeAnalyser, does it say there is a new versions?


Comment by: Jason (5-5-2013 22:20:21)

Jan -

I just updated my version and saw that I can now customize the shortcut keys, which is great. The one issue I am having is that when I press [ Ctrl Shift - ], it brings up the reference tool even though its not set to that shortcut. Any thoughts?

Yes, if the tool could remember the window position, that would be pretty handy, given the window can actually block the dependent cells I am referencing to.


Comment by: Jason (6-5-2013 08:35:26)

Yes I updated it but I still have a problem. When I press Ctrl Shift - it opens the reftree analyzer window and I can't change that.

As I begin to use the tool more (I had previously used it in prior versions of Excel), the window position is pretty important I think as the window is constantly blocking the cells that I am trying to audit.

Thanks for the quick response


Comment by: Jan Karel Pieterse (6-5-2013 09:49:11)

Hi Jason,

Does it register your new shortcut keys properly next time you start Excel perhaps?

What if the tool would remember the "Tile" checkbox value on the form, would that help?


Comment by: Jason (7-5-2013 22:07:30)

I just updated the plugin and the window now remembers its location which is FANTASTIC. this is really great stuff Jan.

the only minor issue I still have is [ Ctrl Shift - ] still brings up reftree even though that shortcut isn't registered.



Comment by: Jan Karel Pieterse (8-5-2013 10:28:17)

Hi Jason,

This is really odd, as it does not do this for me at all!
Is it possible that you still have an add-in called RefTreeLauncher installed (a remnant from V1)?


Comment by: Craig (27-5-2013 11:32:25)

I've tried installing the trial version of RefTreeAnalyser into Excel 2007, but on adding it I get the error message "Run time error 424 - Object Required", and the add in fails to be applied.

Any ideas how I can solve this?



Comment by: Jan Karel Pieterse (28-5-2013 08:51:19)

Hi Craig,

Can you please try build 019?


Comment by: Rene (3-6-2013 12:32:42)

I am getting the same run time error 424 as above all of a sudden (after the latest updated).
In the past I could use it after clicking away this message but now (after re-installing the above version) I cannot even use the tool anymore with the error coming up again.

Any progress / ideas on this?


Comment by: Jan Karel Pieterse (3-6-2013 16:06:25)

Hi Rene,

Please download the latest build, it should resolve the error.


Comment by: Rene (3-6-2013 16:47:28)

No more probs.

Cheers, man.


Comment by: David (12-8-2013 23:51:07)

I updated to the latest build, but it set my Excel formula setting from autorecalc to manual. Really ignoring.


Comment by: Jan Karel Pieterse (13-8-2013 07:34:50)

Hi David,

Apologies, bug fixed.


Comment by: Dave Hockin (29-10-2013 13:04:29)

Jan, I 've tried on 3 occasion to get you to reply for the new key for the 2.0 upgrade.On Apr 24-13 you sent the email and I've replied on Apr 25, Apr 30, and may 24th with no response. Can I get the upgrade and the key ?

In my email I've supplied you with the licence key.
What is the process that I should follow?


Comment by: Jan Karel Pieterse (29-10-2013 16:23:40)

Hi Dave,

Apologies for not responding to your messages, perhaps they were eaten by my spam filter, I don't know, because I cannot find any of them!

Just fill out the order form above. As a V1 user you are entitled to a 50 percent discount on this version.


Comment by: Wouter Mense (4-11-2013 11:42:55)

Hello Jan,

I was evaluating the trial version of your tool with one of my workbooks. So far everything looks great! However, when tracing precedents for some cells, I am getting a

Run-time error '-2147221503 (80040001)':
Duplicate key: 'Precedents034¶'[calc.xls]Sheet 03'!O49¶ROOT'

Looks like a custom error message from the VBA code, so maybe you would be willing to help me out with it even though I am not a paid user?


Wouter Mense


Comment by: Jan Karel Pieterse (4-11-2013 12:37:10)

Hi Wouter,

Thanks for letting me know, I'll look into this.


Comment by: Dave Hockin (7-11-2013 20:26:15)

Ver 2.042
Help file will not display.(Had same issue for build 041)
Chm file in the same folder as reftreeanalyserxl.alam
also in the folder is the xxx.chw file (all the files from the zip in same folder)

When I click on help, the menu comes up, the topic list is clickable but explorer reports back msg of "Navigation to the webpage was canceled"
Using Explorer 8.0.7601.17514CO


Comment by: Jan Karel Pieterse (8-11-2013 19:39:44)

Hi Dave,

This is a windows issue, you have to rightclick the chm file and enable it somehow.


Comment by: Kanwaljit (21-11-2013 11:05:52)

Hi Jan,

I tried to fill the form but I am not able to specify any quantity in No of Licenses drop down as it does not contain anything.



Comment by: Jan Karel Pieterse (21-11-2013 13:53:56)

Hi Kanwaljit,

I have fixed the issue with the number of licenses, thanks for bringing this to my attention!


Comment by: Eileen (4-12-2013 19:06:10)

We have noticed that when using Edit/Replace a window box for Replace has a default to find "42" and that "Match entire cell contents" box is checked. This started to happen when we updated RefTreeAnalyser to Version 2. This seems to happen to different versions of Office (2010 and 2013; 32- and 64-bit).

Is this a bug with a future fix?


Comment by: Jan Karel Pieterse (5-12-2013 07:48:16)

Hi Eileen,

Thank you for letting me know. I am aware of the issue and a fix is already in beta stage. You should be receiving an update within a couple of weeks


Comment by: Charlie (9-1-2014 19:15:37)

I noticed in your latest update notice that you mention the tool works with all excel objects including data validations - I tried it on a dropdown list for a data validation and it did not indicate the cells that were using the dropdown list as dependents - what am I doing wrong?

Love the tool - thanks



Comment by: Jan Karel Pieterse (10-1-2014 11:14:26)

Hi Charlie,

Thanks for your feedback!

Can you please show the data validation formula (which points to the list)?
Also: which Excel version are you using?


Comment by: Dave Hockin (10-4-2014 01:40:09)

Bug: Ver 2.0 Release 052, Excel 2010 version 14.0.6129.500 32 bit

Cannot disable default hot key. Specifically Objects is firing using Ctrl + Shift + O which I have mapped to one of my own macros.I have unchecked the box in Settting, and tried full Excel shut down and restart. No effect

All other Hotkeys seem to be editable


Comment by: Jan Karel Pieterse (10-4-2014 09:54:08)

Hi Dave,

What if you change it again and then restart Excel?


Comment by: Dave Hockin (10-4-2014 14:22:56)

As noted the issue is for Object key only.

I found a sequence that is a workaround
-assign a new letter to eg X or leave it blank
-close excel and restart

when assigning X, it is now the key that is locked to Objects and my existing Shift + Ctrl + H can be run

However as with H, X is now stuck with or without the checkbox on the setting tab. Only way to release X is to have another letter assigned to it or leave it blank.

Overall it seems that the checkbox is NOT deactivating the Hotkey for this case. I myself have found
Application.MacroOptions buggy at times so perhaps this is a clue if you are using this.


Comment by: Jan Karel Pieterse (10-4-2014 15:20:57)

Hi Dave,

Ah, now I get it. The checkboxes are not meant to turn on or off a hotkey, but whether or not you need to use the control key in combination with the hotkey.


Comment by: Dave Hockin (10-4-2014 18:11:41)

Hi Jan,

Surprized at your logic. With checkbox linked to Ctrl key it effectively means that user cannot type any of those letters and symbols unless they have box checked.

Eg deselecting checkbox for Check Formulas means I can't type Capital H in my spreadsheet without refTree launching into formula checking. ie can't type Total in a cell.Surely that is not what you had intended.

I can type into formula bar but not in the cell itself


Comment by: Jan Karel Pieterse (11-4-2014 11:45:08)

Hi Dave,

Hmm, hadn't even considered that. Makes perfect sense now that you mention it :-)


Comment by: Jan Karel Pieterse (14-4-2014 06:47:06)

Hi Dave,

On second thought, I do have a good reason for the control checkbox: you can also assign function keys (F1-F12) as s hort-cut keys and for those is makes sense not necessarily to have the control key pressed.


Comment by: Steven Katirai (5-5-2014 13:23:08)

I have a model 70Mb big with over 500 sheets, will your package work with a file this size/


Comment by: Rene (8-5-2014 06:50:06)

Hi, Jan Karel,

since one of the major updates (already few months ago), I am having quite some problems especially with some hot keys.

I am using Version 2.0 Build 54 (Excel 2007 12.0.6683,5002 SP3 MSO).

- Help File in Excel for the tool does not work for me. I can call it up and see the index but cannot enter any of the actual entries. Any ideas?
- Hot Keys: For me Shift+H seems to be a hot key selecting and checking some column (calls up a window "Found formulas in a range" if I select a non-empty cell). There seems to be no short key that can be disabled in the setting. And using Shift+H for a key would be rather unusual as I cannot type anything in Excel starting with a capital H unless I turn off the add-in.

Happy if you can have a look (and imagine) what I may be missing.



Comment by: Jan Karel Pieterse (8-5-2014 07:10:41)

Hi Rene,

I suspect you need to check the checkbox directly after "Check Formulas" (Settings screen). If you want to have no hotkey for an option, leave the textbox next to the checkbox empty, don't clear the checkbox as that is intended to indicate whether or not to use control plus the hotkey in the textbox.


Comment by: Jan Karel Pieterse (8-5-2014 07:21:25)

Hi Steven,

It will. Some options in it will take a considerable amount of time though! Especially the Objects part, certainly if your sheets contain lots of objects, conditional formatting and Validation.


Comment by: John (5-6-2014 10:04:33)

Morning Jan,

I have a quick question about licensing- Can I move licenses between machines? I'm interested in purchasing this but I know I'll be changing jobs soon so I'm unsure if the license can be changed at that time


Comment by: Jan Karel Pieterse (5-6-2014 10:23:57)

Hi John,

Sure, you can use your license on any machine you like!


Comment by: RG (28-7-2014 15:27:31)


I still struggle with the shortcuts.

It seems to block a number of common key / shortcuts or other ones that I have already defined which I find quite inconvenient and I cannot seem to disable it?

1) Whether I check or uncheck the Ctrl+# shortcut in the settings, either the #-key or the Ctrl+#-key (for standard date formatting) is blocked.

2) Whether I check or uncheck the Ctrl+O shortcut in the settings, I cannot use the cominbation of Ctrl+Shift+O which seems counterintuitive. I have a shortcut assigned to that combination.

Can you take a look at this and advice a way around this?

On 2) I could assign a different shortcut of course (which I would avoid from pure habit).
On 1) I see currently no workaround at all.



Comment by: Jan Karel Pieterse (12-8-2014 10:53:30)

Hi RG,

The checkboxes are a bit confusing: They only control whether or not the hotkey includes holding down the control key. To disable a hotkey, remove it textbox. So to make RefTreeAnalyser stop using the #, remove the # character.


Comment by: Marko (15-8-2014 14:53:31)

Hi, Thanks for the lovely formula auditing tool! I'm evaluating version 2.0 Build 055, unfortunately I run very often to a VBA error message when I try to trace precedents:

Run-time error '-2147221503 (80040001)': Duplicate key: 'Precendents'Sheet1'!1681b'[myWorkbook.xlsm]Sheet2'U314bRoot'

I haven't discovered any clear pattern when this error message appears, but it always happens with same cells and about half of the formulas in my workbook. I have Excel 2013.

Do you know what could cause this? Thanks!


Comment by: Jan Karel Pieterse (15-8-2014 15:59:52)

Hi Marco,

That error is likely caused by you probably having "Break in class modules" set in your VBA editor's settings (Alt+F11 to the VBE, tools, options, General tab).

The default setting is "Break on unhandled errors".


Comment by: Bill McNair (22-8-2014 00:49:33)

A great, great formula diagnostic tool. Thank you!

One issue, I am used to having RefTree menu choices come up in the Excel right click mouse menu (from older versions of RefTree). I have noticed that the current version sometimes has the Reftree menu choices in the Excel right click menu and sometimes not. This strange behavior happens on my Win764bit/Excel201332bit and my Win8.164bit/Excel201332bit computers.

Could you add the ability to control the RefTree menu add to the Excel right click menu in the RefTree setting menu and force a reset of the Excel right click menu tree so that RefTree is included if so set? Thanks.


Comment by: Jan Karel Pieterse (22-8-2014 08:41:09)

Hi Bill,

Thanks for the compliments!

I expect the instances where you don't see the RefTree entry in your right-click menu your worksheet is in a different view. This is because there are more than one type of cell right-click menus in Excel, depending on the worksheet view (Normal and page layout view share one right-click menu and Page break preview has a different one).

I'll note this as something to fix in the next build.


Comment by: online excel training (7-10-2014 14:00:06)

This is great. Thanks for putting this together for an easier understanding.


Comment by: John (8-10-2014 07:14:44)

Hi Jan.
Discount not operating at present. Or am I not seeing something.
Many thanks.


Comment by: Jan Karel Pieterse (8-10-2014 10:25:39)

Hi John,

Did you enter the coupon code? MVP2014
Also, I think the discount is processed when you go to the next step of the ordering process (so after entering amount and coupon code).


Comment by: John (8-10-2014 11:59:32)

Thanks Jan.
All good.
All worked fine.
Many thanks.


Comment by: Mark (11-10-2014 16:41:33)


I was wondering if there is a way to trace the exact precendent cell, across multiple levels, ideally with one click, and which will work for a vlookup formula.

e.g. I have 3 sheets- Sheet3 links to Sheet 2 via vlookup, which links to sheet1.

I downloaded your program and tried tracing back from Sheet 3. It will only trace back to Sheet2, and not directly to the cell but rather, the entire vlookup table.




Comment by: Jesse (12-10-2014 23:56:46)

Tried it in OSX (both files) and got:

"Compile error in hidden module: UFReferences"


Comment by: Jan Karel Pieterse (13-10-2014 10:33:02)


No, there is no such option in the tool. I like the idea, but it is quite a difficult one to accomplish!

@Jesse: I'm sorry, the tool only works on WIndows Excel.


Comment by: Joe (18-10-2014 01:02:14)

Is 50% discount still available?


Comment by: Jan Karel Pieterse (18-10-2014 11:42:48)

Hi Joe,

I'm afraid not!


Comment by: Phil (5-11-2014 01:46:41)

I see others have mentioned the issue with table lookup formulas (most commonly vlookup or hlookup).

Currently, a dependent is listed if it refers to a cell within the Table_Array parameter of a vlookup or hlookup formula. The dependent is shown even if the Row_Index or Column_Index number points to some other cell within the Table_Array. Presumably, it would be difficult to parse the formula to determine if the lookup would hit the active cell. As a work-around, I would be happy with an option to drop any dependents for which the cell formula is a table lookup.



Comment by: Jan Karel Pieterse (10-11-2014 11:41:56)

Hi Phil,

The thing is, RefTreeAnalyser parses the formulas to extract cell references. There is no intelligence there to do things which are specific to one type of function, such as lookup functions and OFFSET or INDIRECT. This is also the case for Excels built-in formula tracing tools.

If you need to know which part of a table is "used" by a VLOOKUP, perhaps it is a better idea to use INDEX and MATCH instead. Combining these two functions enable you to point directly at the column from which the item is to be retrieved, rather than the convoluted column index number VLOOKUP requires you to use (and is much harder to audit in my opinion).


Comment by: Phil Guerney (10-11-2014 12:54:03)

Thanks Jan,

I agree. Ever since learning about INDEX-MATCH, I have used it for my own spreadsheets. Unfortunately, I have to audit other peoples' work and the use of the table lookup functions does make this difficult.

The one I have in front of me today has formulas like this:


which refers to a block of data 31 cols x 744 rows and I have to scroll to find the 455th row (in this case) to see the precedent value because RefTreeAnalyser only refers to $D$2:$AH$745 as the precedent.

If I select cell D456 and ask for dependents, I get a list of 28 cells or ranges which "depend" on this cell, although only 7 really do so.

Anyway, thanks for a great tool which does save me a huge amount of time for a lot of the tedious work involved in my work.



Comment by: Robert Ed (6-3-2015 12:03:45)

Hi Phil,
Thanks for the great work.

Is there a possibility to highlight all the precedents which are identified at all levels?

This would be very useful, for example for cleaning workbooks of unused data, or for finding which are the critical input data.



Comment by: Jan Karel Pieterse (6-3-2015 13:21:13)

Hi Robert,

No, currently there is no such method. But I certainly like the idea!


Comment by: Dmitri (18-3-2015 22:14:33)

Will it work with Excel for Mac?


Comment by: Jan Karel Pieterse (19-3-2015 11:54:21)

Hi Dmitri,

I'm afraid not!


Comment by: Roel (28-4-2015 23:21:53)

Hi Jan,

Have you considered adding a function that would be called something like "Report Cell Names" and that would simply create an alphabetical index of all cell names, and their respective tabs and locations? It would love to be able to include such an index for my clients, when I finish & deliver a spreadsheet to them.


Comment by: Jan Karel Pieterse (29-4-2015 07:37:22)

Hi Roel,

Try my Name Manager, which has such a list button:


Comment by: Ken (28-5-2015 08:04:55)

Hi - thank you for developing this amazing addin.

One quick question - how do the hotkeys work once I am tracing dependents. eg "Report", "Edit Audit Cell" "Do Active Cell" all have letters underlined which implies that there is a hotkey but the obvious guess of pressing the letter does not work for me? Cheers, Ken


Comment by: Jan Karel Pieterse (28-5-2015 09:23:49)

Hi Tom,

Like in any Windows program, these require the alt key to be held down.


Comment by: Roy (23-6-2015 22:17:56)


I'm impressed, however suddenly some Excel sheets are crashing now. I want to try to reinstall the tool but first need to uninstall it.
However, there's is no uninstall option, not in the start menu nor in the configuration/software section.

how do I uninstall???


Comment by: Jan Karel Pieterse (24-6-2015 11:27:29)

Hi Roy,

Sorry to hear you are experiencing problems. What crash do you get precisely?

Uninstall is easy, just go to File, Excel options, Add-ins tab and click the Go button and uncheck RTA in the list.

Tip: alt+t followed by alt+i gets you there immediately.


Comment by: Roy (24-6-2015 15:05:48)

I already found the problem.
My File is on my NAS and it seems that your addin cannot work with that.
When copying the file to my local PC it seems to work fine again.


Comment by: Jan Karel Pieterse (24-6-2015 15:15:20)

Hi Roy,

OK, thank you for letting me know.


Comment by: Roy (24-6-2015 18:05:57)

Hoi Jan Karel,

Ik zie dat je ook uit Nederland komt. ik zag je naam ook op een ander forum. Daar had ik een probleem gepost maar niemand schijnt daar de oplossing voor te weten :)


Comment by: Vikesh Jain (29-6-2015 21:35:13)


If I exit the trace precedent window by pressing Esc key then Excel is not in focus - i have to activate excel again (by either clicking into it or using alt-tab).

can you please fix it.


Comment by: Jan Karel Pieterse (30-6-2015 11:18:37)

Hi Vikesh,

Which Excel version are you using and how many Excel files were open at that moment?


Comment by: Nassim (2-12-2015 09:24:22)


This tool save me a lot of time.
One question, what's the shortcut for Azerty keyboard ?


Comment by: Jan Karel Pieterse (2-12-2015 11:45:42)

Hi Nassim,

Since I do not own such a keyboard this is hard to asnwer :-) But you can easily change the short-cut keys in the settings dialog.


Comment by: Soichi (15-12-2015 00:36:49)

Hi, I wanted to use this software but had a problem when I tried to install it.

The error message says "Cannot run the macro 'rxreftreecustomUI_onLoad'. The macro may not be available in this workbook or all macros may be disabled."

Could you help me? I use Windows 10 and Excel 2016.

Thank you.


Comment by: Jan Karel Pieterse (15-12-2015 08:22:37)

Hi Soichi,

Odd, this should not happen. Perhaps your Office needs a repair?


Comment by: Matt Thompson (18-3-2016 18:03:34)

how do I uninstall? Thanks!


Comment by: Jan Karel Pieterse (18-3-2016 19:26:38)

Hi Matt,

Uninstall is simple:

- File, Options, Addins tab, click "Go...", uncheck RefTreeAnalyser.


Comment by: thelemontree10 (22-5-2016 08:44:53)


When I open your RefTreeAnalyserXL.xlam for first time, a message shows "Excel found unreadable content in 'RefTreeAnalyserXL.xlam'. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes.". I click Yes and another message popup says "Cannot run the macro 'rxreftreecustomUI_onLoad'. The macro may not be available in this workbook or all macros may be disabled.". I have turn on macro. So I think the file ZIP have some problems.

Please fix it soon.

Best Regards,


Comment by: Jan Karel Pieterse (23-5-2016 10:22:04)

Hi thelemontree10,

Can you please try again? It works just fine for me. WHat Excel version do you use?


Comment by: thelemontree10 (23-5-2016 15:07:23)

I use excel 2013 sp1. I have try so many times but nothing changed. So sad :(


Comment by: Jan Karel Pieterse (23-5-2016 20:36:36)

Hi thelemontree10,

Odd, it works just fine for me on that version. What does "About Excel" on the account tab display as the exact version number? Perhaps a repair of your Office installation will help?


Comment by: Jean-Pierre Degroote (31-7-2016 16:10:31)

Hi Jan Karel,

Using build 98.

In a new workbook, type in A1: =B1+1

Report formulas. => No formulas on sheet.
Next drag A1 to A2 & report formulas.

You get: Sheet1 =B1+1 A1:A2


Comment by: Douglas Hirsch (1-8-2016 17:51:56)

Is the current build fully operational with Excel 2016? I have an issue that every time I start Excel, the add-in doesn't automatically load and I need to un-check and re-check it in the add-in bar. Can you please advise?



Comment by: Jan Karel Pieterse (1-8-2016 20:56:15)

Hi Doug,

This is caused by a recent Office update I'm afraid. MSFT is working on a solution.


Comment by: Jan Karel Pieterse (9-8-2016 09:23:56)

Hi Jean-Pierre,

Thank you, I will look into this.


Comment by: Douglas Hirsch (12-8-2016 17:32:30)

Each time I use the hot keys to launch the trace precedents function, the settings menu opens up and I have to close it. Is this because I am using the trial version of the add-in?


Comment by: Jan Karel Pieterse (14-8-2016 12:29:16)

Hi Douglas,

Yes that is precisely what happens. Registering the tool will remove that behaviour.


Comment by: Sam (23-8-2016 19:55:19)

Is the license a one-time fee or does it need to be renewed annually?


Comment by: Jan Karel Pieterse (23-8-2016 23:18:45)

Hi Sam,

It is a one-time fee. It entitles you to free updates until I release a new version (which is not planned for another year or so)


Comment by: Gary Clarke (1-9-2016 14:57:06)

Is 64 bit Excel 2016 supported?
Seems strange that only 2010 & 2013 64 bit supported


Comment by: Jan Karel Pieterse (1-9-2016 15:00:47)

Hi Gary,

Yes, 64 bit Excel 2016 is supported. I'll update the content of this page.

Thank you.


Comment by: Gary Clarke (1-9-2016 15:39:27)

Thanks for quick response.
One more question.
Does your tool trace dependencies between tables on different sheets (in same workbook).
I can't seem to get standard Excel formula auditor (the one with millions of horrible blue arrows) to work with tables, my workbooks are huge and 90% tables.


Comment by: Jan Karel Pieterse (1-9-2016 16:43:28)

Hi Gary,

Precedents to tables are properly traced at level 1 as at that level my tool parses the formula.

Unfortunately, for the Dependents analysis I depend on Excel's built-in tools so those are not detected unfortunately.


Comment by: Gary Clarke (1-9-2016 17:10:13)

OK Thanks for your honesty.
Shame, tracing dependents is much more useful to me than precedents so I can check whether ranges are safe to delete or not.
Seems to be a tough problem,I can't find a way to do this with with VBA either.
If you figure out a solution, please mail me and I will definitely buy
Only 1 level is fine


Comment by: Jan Karel Pieterse (1-9-2016 18:07:59)

Hi Gary,

Tracking down dependents to tables is very complicated (and probably therefore slow). I'm looking at it, but can't promise anything.
I'd suggest purchasing, if I succeed in cracking that nut it'll be a free (and automatic) update for license holders.


Comment by: George (8-11-2016 06:49:15)

I just downloaded the demo version and it has been working great all day for tracing precedents across multiple sheets in a model. When I opened up the same workbook this evening it does not appear to be working. I will select a cell and hit "precedents". First the pop-up screen shows up asking for license info. Then I hit "close" and it runs through the tracing precendents process, but then either does not display anything, or displays the excel built in precedent tracing arrows. I am using excel 2010. Any ideas?


Comment by: George (8-11-2016 06:52:51)


I just wrote. I think I fixed it byu hitting the "Reset Forms" button in the settings page. Thanks!



Comment by: Dave (8-11-2016 22:59:29)

Moving RefAnalyser to new PC. IT dept tells me I am due for replacement. I am a licenced user. Can I install on new PC with same licence number?



Comment by: Jan Karel Pieterse (14-11-2016 09:16:42)

Hi Drew,

Sure, go ahead!


Comment by: Shaun (1-12-2016 19:19:58)

Does this product work on Excel for Mac?


Comment by: Jan Karel Pieterse (2-12-2016 10:44:36)

Hi Shaun,

I'm afraid not.


Comment by: raffaele (15-12-2016 15:20:14)

Hi, your addin is really good. The only missing feature, that would make it excellent is if it could handle links to ranges on other xls files.


Comment by: Jan Karel Pieterse (15-12-2016 16:03:32)

Hi raffaele,

If the linked file is open in Excel the tool will use it.


Comment by: Sky (27-2-2017 16:18:14)


The newest update seems to have introduced a bug for me where the userform is hidden when tracing precedents/dependent, (the functionality still works in terms of locating the cells). Using excel 2016 on windows 10.


Comment by: Jan Karel Pieterse (27-2-2017 17:20:08)

Hi Sky,

Apologies that you are experiencing a problem.
Can you please provide me with a screenshot?


Comment by: Sky (27-2-2017 17:24:07)

Hi Jan,

There's nothing to screenshot, the userform flashes briefly on the screen and disappears. Arrow keys still allow me to navigate, alt+l still 'closes' and alt+a still changes the active cell, but at no point is the UI displayed other than if I select 'settings' from the ribbon.


Comment by: Jan Karel Pieterse (27-2-2017 17:30:53)

Hi Sky,

This may be due to a change of your display setup, for example if last time you were using an external monitor or a larger display resolution. To reset the form position, open the settings screen and click the reset forms button.


Comment by: Sky (27-2-2017 17:32:36)

Hi Jan,

That's cracked it! Many thanks!


Comment by: Raffaele (30-3-2017 18:37:34)

Hi, many compliments for the addin I'd find it very useful in the Precedents feature, to see the value of the precedents not as ".value" but as ".text". So for example, if a precedent has a value of 1253514,254, but is shown (through formatting) as 12,5 (millions), it would be much better to see it in the precedents window as 12,5 than 253514,254 (as it is today). Thanks!


Comment by: Jan Karel Pieterse (31-3-2017 20:19:10)

Hi Raffaele,

Nice suggestion, thanks.


Comment by: John Hackwood (23-7-2017 06:08:22)

Hey Jan Karel

I bought your RefTreeAnalyser and its a great tool and I use it when I have a doozy of a formula.

Lately I have been getting the msg upon opening Excel when it loads Add-ins that checking for updates as failed. I guess I could unclick the check for updates automatically but has the support for this add-in now stopped and that's the reason?

I could send you a screen shot if you want.



Comment by: Jan Karel Pieterse (23-7-2017 10:44:33)

Hi John,

RefTreeAnalyser is still fully supported indeed and I'm working (slowly) on version 3.

So I guess the problem is on your end. Perhaps a setting in your firewall?


Comment by: John (23-7-2017 13:32:15)

Hi again Jan Karel, OK will try that.

Sorry I didn't know this comment was going to be public and gave my license no. in the above comment, so please delete/redact it. thanks John


Comment by: Jan Karel Pieterse (23-7-2017 20:21:31)

Hi John,



Comment by: Den (6-8-2017 00:14:54)

Hi Jan,
there is an error in tracing when i use named table with name in cyrillic characters. If I use the named column with сyrillic, there is no error.

Some suggestions for next release:
1)In the interface, select the structure of the formula (not just each individual element) and display the calculated value for each block. If the formula uses a logical expression to calculate the result, display how the elements of the compared block match the condition (select suitable ones). If you use the array formula in the condition, display the matching array elements.
2) If the formula uses ranges / arrays of the same dimensions, display the array elements under each other. That is, it is necessary to align to the widest value of the element of the array / range group.
3) Make it possible to correct the formula via the add-in interface.
4) Connect the add-in to all Excel windows in which you entering the formulas:
a) Conditional formatting formula;
b) Data validation formula;
c) Formula for the named range in Name manager.
To check the operation of the formulas in these blocks, it is necessary to select the cell with which to calculate the formula. It is especially important when the formula uses an offset.

Thanks for the excellent add-on and further success!


Comment by: Jan Karel Pieterse (13-8-2017 17:14:35)

Hi Den,

Thanks for leting me know!


Comment by: Alessio (24-8-2017 18:53:16)

='\\Solvency II\2017\Quarterly\Q2 2017\Lite Model\Inputs Q117\Asset Inputs\[Convert_BalSheet_Items_to_ICM_Inputs_Q117HC_RCAP_V1.xlsm]1.RISK_DRIVERS'!E73

That's one of my link.
Your program stops at "Q2" because there is a space.
I need to open the workbook to fix this.
any other way around it?



Comment by: Jan Karel Pieterse (25-8-2017 13:15:37)

Hi Alessio,



Comment by: Alessio (31-8-2017 13:23:25)

I know that sound cheeky but if you can send me an e-mail with a temporary licence, I can see if there are any bugs in the locked-up icons.

This is a very good VBA app.


Comment by: Jan Karel Pieterse (31-8-2017 14:30:30)

Hi Alessio,

Erm, not sure what you mean by locked up icons?


Comment by: Kirran (19-9-2017 12:43:19)

Hi Jan,

It would be great if you could include an option in settings not to unhide all hidden sheets. Sometimes the addin doesn't re-hide them and it's a bit of a pain when you know they aren't going to be used in the ref tree.

Many thanks,



Comment by: Jan Karel Pieterse (20-9-2017 16:33:27)

Hi Kirran,

Good suggestion!


Comment by: Marvin Rivas (15-12-2017 20:15:04)

I recently upgraded to using excel 2016 and now that I try to relaunch the reftreelauncher it does not install. Can you help as how I can fix this problem?

Thank you,


Comment by: Jan Karel Pieterse (18-12-2017 10:01:04)

Hi Marvin,

It seems some recent updates are preventing RTA from working. I am looking at how I can resolve these issues.


Comment by: Lois (15-1-2018 03:34:26)


Can I ask what's an alternative for Spreadsheet Studio for excel?



Comment by: Jan Karel Pieterse (15-1-2018 07:58:18)

Hi Lois,

Spreadsheet studio is a great tool and in some ways competes with my RefTreeAnalyser. Some RTA benefits:

- Easier navigation of formula dependents and precedents
- Visualisation of dependents
- Only tool available which is capable of showing al circular references in a file
- The only tool that also includes object references such as pivot table sources, chart source data, conditional formatting, data validation, form controls, etcetera.


Comment by: Ruben (16-1-2018 17:16:30)

Love the add-in. Since I only use my keyboard with excel, I wonder if there is a (or if I could make one myself) for the close button in the tracing precedents menu.


Comment by: Jan Karel Pieterse (16-1-2018 17:29:17)

Hi Ruben,

Doesn't pressing Escape work for you?


Comment by: Ruben (16-1-2018 17:32:30)

Hello Jan Karel,

Thank you for the quick reply. Escape works, but then I jump to the original cell, instead of the cell that is currently being selected. This is sometimes a hassle for me, since these might be on entirely different sheets.


Comment by: Jan Karel Pieterse (16-1-2018 17:52:30)

Hi Ruben,

I get it. The short-cut key for the close button is alt+l (small L)


Comment by: Ruben (16-1-2018 19:17:07)

Thx! Appreciate it!


Comment by: Pierre Maitre (4-2-2018 03:50:14)


I just purchased and installed your addin. The reason for buying your software is that I have created some circular in my spreadsheet and I cannot find them.

I am using Excel 2016.

How does your addin work with circular reference? do you scan the spreadsheet? do you capture the excel error message ?

Right now, nothing happens. I am getting the Excel circular message just as I did before.

Please, help. I am not an excel expert.

Thanks, Pierre


Comment by: Jan Karel Pieterse (4-2-2018 12:12:27)

Hi Pierre,

In the ribbon tab called RefTreeAnalyser there is a dedicated button to search for the circular chains of cells. The next screen you get is there to explain that RTA will make tempoary changestonyour file, so it gives you an opportunity to choose whether it should save a backup copy first. RTA then lists all chains it finds so you can decide what to do with them.
If you need help, send me an email (address at bottom of this page).


Comment by: Dave (2-3-2018 21:26:25)

Hi Jan, Long time user, just found what might be a bug or a miss on the precedents trace.
Version 2.0 Build 124 Registered user

Sample formula : =CCA!J314+SUM(CapEx!$J$702:CapEx!J702)
Formula is on my "Taxes" sheet.
Precedent reported for CCA cell but not for the sum range on CapEx sheet.

Excel's formula precedent will report this as 3 precedents and RTA reports 1 precedent. Is this a limitation of your ability in RTA to trace or is there a fixable bug ?


Comment by: Jan Karel Pieterse (5-3-2018 10:18:03)

Hi Dave,

That is indeed a bug, but your syntax is rather unusual. If you change the formula to this RTA will pick it up as expected:


Comment by: Kirran Sky (9-3-2018 15:24:34)

Hi Jan,

Any chance you can update to pick up structured references?

e.g. =Table_Name[[#Totals],[Jan-18]] throws out an error.


Comment by: Jan Karel Pieterse (9-3-2018 15:57:52)

Hi Kirran,

It should do so, yours appears an exception :-(


Comment by: Rene (13-5-2018 17:04:23)

Hi Jan,

I all of a sudden started to receive a "Run-time error '13': Type mismatch.

The RefTreeAnalyser still appears on the Ribbon but I cannot use its functionality anymore.

Help button or "Time Selected Cells" still works but most other buttons result in the above error message and upon clicking Debug Excel crashes.
I already tried deinstalling and virtually all security, macros settings etc. are set to lowest level in Excel.



Comment by: Jan Karel Pieterse (14-5-2018 07:25:32)

Hi Rene,

Which version are you using?


Comment by: Rene (14-5-2018 10:53:45)

Hi Jan,

RefTreeAnalyzer Version 2.0 Build 125, Release 2018-05-02.
I am using Office 2010.

The add-in works fine on different computers that I tried, so I might have messed something up in Excel that I cannot see yet.

E.g. I switched recently the language pack but switching back didn't change anything either.

I think it is somehow related to the Ribbon. When I tried to disable all other add-ins, the Add-In Ribbon pane with some add-ins in it remained and I couldn't delete them. They were neither found in the Add-In folder (the one through the Excel / Tools/ Options / Browse function) nor could I get rid of them by deleting my entire Excel customization, i.e. by deleting in the registry the "Options" folder under: HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel.
Deleting the entire 'Excel' folder didn't help either.

I then deleted the entries on the Add-In Ribbon pane manually with a right, but still no success.

So I've run out of options now what to try to get it back to work (other than completely resetting / repairing Office).
(The add-in actually did work on my computer a few days ago.)
Any suggestions welcome.



Comment by: Jan Karel Pieterse (14-5-2018 11:03:36)

Hi Rene,

Download the latest version, I expect the issue will be resolved.


Comment by: Shilpa (13-6-2018 11:37:58)

Hello All,
Can you please confirm the RefTreeAnalyser license is an subscription or perpetual license.


Comment by: Jan Karel Pieterse (13-6-2018 11:40:26)

Hi Shilpa,

It is perpetual, but it entitles you to one year of updates. If your year has expired the tool will work but no longer receive updates.


Comment by: Thomas Plagwitz (18-6-2018 23:46:17)


I have been a happy customer of RTA for a while, but now need to automate running it over a large number of Excel files.

However, I found that whatever I dim the control parameter as:

Dim oControl as Object ' Variant ' iRibboncontrol

the following line will crash my Excel (2010) :

Application.Run "'reftreeanalyserxl.xlam'!rxreftreeFormulaCheckAllSheets", oControl

Any pointers?



Comment by: Thomas Plagwitz (19-6-2018 06:23:27)

Some additional testing information for Application.running RTA methods:

Excel 2016 won't crash, but returns "Unexpected error (40182)", no matter what datatype oControl is declared as.

Also tests the same with another RTA procedure:

Application.Run "'reftreeanalyserxl.xlam'!rxreftreeFormulaCheckAllSheets", oControl

I am trying to follow Ron de Bruin's Application.Run methodology here. Not using oControl just gives 449 "argument not optional", but at least my base syntax seems correct.


Comment by: Jan Karel Pieterse (19-6-2018 10:29:57)

Hi Thomas,

You can invoke that report using:
application.Run "'reftreeanalyserxl.xlam'!jkpRefTreeGenerateFormulaReport"


Comment by: Thomas Plagwitz (25-6-2018 04:59:51)

It works great, thanks! Using this, I managed to analyse formulas for about a 1000 workbooks from a pipeline.

Is it possible, however, to turn off the alert "Timing formula calculation seems to take long, skip for worksheet", either default to yes or even skip timing for all worksheets? I have not found a setting in the PDF documentation, nor another way to suppress or OK this dialog.

Also, could you share the internal equivalents for the 2 ribbon commands rxreftreeListAllSheetStats and rxrtFormulaCheckSheetsRefs.

Thanks again, tom


Comment by: Gareth Morgan (16-10-2018 18:47:15)

If my updates year has expired do I have to buy the whole thing again?


Comment by: Jan Karel Pieterse (17-10-2018 09:54:33)

Hi Gareth,

Well, yes and no.

No: your license stays valid, it is just the updating which expires.

Yes: If you want another year of updates you need a new license.


Comment by: John Busbee (20-10-2018 16:44:18)

Does this work with Excel 2016 for mac? It gave me a visual Basic compile error when I clicked on the add-in file and said something about compatible versions.


Comment by: Jan Karel Pieterse (22-10-2018 10:10:57)

Hi John,

I'm afraid there is no Mac supported version.


Comment by: Teodor (13-12-2018 13:58:05)

Hello Jan,

Is the newest plug in compatible with Office 2019.
After installing 2019 on one of my client's machines the plug in didn't want to run.
Thank you very much for your help.

Kind Regards,

Teodor Rangelov


Comment by: Jan Karel Pieterse (13-12-2018 14:21:49)

Hi Teodoor,

Yes it should work. Have you unlocked the add-in as shown on this page:


Comment by: Jamiejes (6-1-2019 23:32:28)

Hey. I sent a screenshot. Did you get it?


Comment by: Jan Karel Pieterse (2-2-2019 16:25:57)

Hi Jamie,

I don't think I have.


Comment by: Allie (1-5-2019 21:25:00)

Hi there - I have a laptop that I plug into a monitor to use both screens. However, when I am only on the laptop and I initiate the Ref Tree Analyser, I can't see the dialogue box on my laptop. Is it searching for the other screen? Seems weird since all other applications know I'm not plugged in.

Secondly, every time I launch the Ref Tree Analyser it unhides all my hidden tabs. This is getting annoying as I need to re-hide the tabs once I'm done. The version I had before this didn't have that problem.

Please let me know how to fix both these issues. Thanks!


Comment by: Jan Karel Pieterse (2-5-2019 10:49:00)

Hi Allie,

That indeed is the case. You can reset the position by opening the settings screen and pressing the "Reset Forms" button.

I agree this needs some work, but I haven't yet found a reliable way to detect whether the screen the dialog is shown on is actually attached to the system :-(


Comment by: Allie (2-5-2019 18:06:00)

Thanks so much for the quick response! I'll hit the Reset Forms next time.

Do you know how to fix it so that the application does not unhide hidden tabs? Thanks so much! This tool is amazing - I wouldn't be as efficient without it.


Comment by: Jan Karel Pieterse (3-5-2019 11:31:00)

Hi Allie,

Thanks, glad you like it.

Does unchecking the "Unhide sheets" checkbox on the settings dialog work for you?


Comment by: Allie (3-5-2019 18:25:00)

So simple - I should have seen that dialog box. Thanks!!


Comment by: David (9-10-2019 03:25:00)

Can one license key be assigned to multiple licenses?

If we purchase additional licenses, can the same license key be used for those new licenses?

Can we allocate/deallocate users to licenses from your web site?


Comment by: Jan Karel Pieterse (9-10-2019 10:50:00)

Hi David,

Yes, you can use a single key for multiple licenses. There is no means to deallocate registrations, but as long as the number of registrations using the license key stays within reason there is no need to do so.

I do prefer that you use the new license code if you purchase additional licenses.


Comment by: john (4-1-2020 10:42:00)

The addin is perfect but can you add evaluate formula option(like in excel) in this addin


Comment by: Jan Karel Pieterse (6-1-2020 11:49:00)

Hi John,

Thank you for the suggestion!


Comment by: Alejandro (14-7-2020 22:14:00)

Hi, this license this sale in chile?, exist one representative in chile for companies?


Comment by: Jan Karel Pieterse (18-7-2020 19:10:00)

Hi Alejandro,

You can buy the license directly from this website.


Comment by: Gearoid (1-10-2020 16:01:00)

Hi there,

Does this work with Excel 365. Can I trial it before I buy it?



Comment by: Jan Karel Pieterse (2-10-2020 16:28:00)

Hi Gearoid,

Yes indeed it works with any Excel version (for Windows!) as of Excel 2007 up to and including Excel 365.
You can download the tool and try it as it automatically works in demo mode with limited functionality.

Once you're happy with the tool you can go back to this page, purchase a license (sent to you by email).

You can enter the license code into the settings dialog of the tool and it will unlock into the full version automatically.


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:

Please enter your name (required):

Your e-mail address (optional, will only be used to inform you when your comment is published or to respond to your question directly):

Your request or comment (max 2000 characters):

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

I give permission to process this data and display my name and my comment on this website accoring to our Privacy Policy.

Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.