RefTreeAnalyser versions

Pages in this article

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

Current Version

Buy RefTreeAnalyser now!

The most recent version is available for download here, which features the following additions/changes:

  • Now also analyses objects on worksheets such as pivot tables, charts, form controls and etcetera.
  • Fully 64 bit compliant
  • Excel add-in, no setup or administrator rights needed to install
  • Lots of added functionality

RefTreeAnalyser works in all Windows Excel versions as of Excel 2007 (both 32 and 64 bit is supported). For Excel 2003 an older version is available.

Update Information

1.0.14

Included Help file and a Help button on both the find references and the find circular references screens

Added an entry on the cell right click menu to access the tool.

1.0.16

Hotkeys to start the tool

Improved reporting

1.0.17

Fixed a minor issue regarding double operators such as >=

1.0.18

Fixed a small screen resizing problem

1.0.21

RefTreeAnalyser now handles structured tables references

1.0.23

Prevent error message when workbook has protected worksheets

1.0.25

Show message if no precedents/dependents have been found

1.0.26

RefTreeAnalyser does not error out when started whilst editing a cell

2.0 011

64 bit version

Improved circular reference algorithm

Error tracing added

Time range calculations

Check range for inconsistent formulas

Sheet statistics added

2.0, 012

It is now possible to change the hotkeys.

I have also made sure that when an update is available, the help file is automatically updated.

2.0, 013

Fixed a problem related to setting hotkeys.

Made sure form position is remembered (and can be reset).

2.0, 014

Fixed a compile error that only Excel 2003 exhibits.

2.0, 015

Fixed that the About screen does not show proper registration status

2.0, 017

Fixed some problems related to Excel 2003.

2.0, 018

Fixed some problems related to Excel 2003.

2.0, 019

Fixed that the about button in Excel 2003 sometimes opened the About screen of another add-in.

2.0, 020

Added a separate add-in file (.xla) for Excel 2003 and before.

2.0, 021

Fixed an Excel 2007 issue

2.0, 022

Improved returning to previous selection after closing the tool

2.0, 023

Minor bug fix

2.0, 024

Prevented unnecessary scrolling

2.0, 025

Fixed bug with absolute cell references to multiple columns.

2.0, 026

Fixed bug regarding files opened in protected mode

2.0, 027

Minor bug fix

2.0, 028

Fixed a compile error in Excel 2003

2.0, 029

Fixed a bug for Excel 2013, prevents messages being covered by the application's screens

2.0, 030

Fixed bug where RefTreeAnalyser removed the License registration information when unchecking the add-in

2.0, 031

Fixed a bug for Excel 2013, prevented another message from being covered by the application's screens

2.0, 032

Improved the error trace tool and the functioning of the stop button.

2.0, 033

Important update: fixed some bugs in the multi-level precedents searching

2.0, 034

Minor bug fix regarding Excel 2013 windows.

2.0, 035

Improved tracing errors function

2.0, 036

Improved reporting

2.0, 037

Added a new option to the tool: Visualize precedents. The precedents of a cell are visualized directly on the worksheet, a vast improvement on the built-in show precedents option.

2.0, 039

Fixed a bug pertaining to Excel 2003 and 2007

2.0, 040

Visualize now also available for Excel 2003

2.0, 041

Added an option to change the visualization colors

2.0, 042

Visualization: Improved placement of pictures of cells which are out of view

2.0, 043

Fixed an issue with opening empty workbooks in 64 bit Excel 2013

Fixed issue with leaving an entry in the find dialog

2.0, 044

Added new Object References option

2.0, 045

Objects are now also analyzed when tracing dependents and precedents.

2.0, 046

Fixed a bug regarding Visualization

2.0, 047

Beta version

2.0, 048

Made object search optional in find precedents/dependents

2.0, 049

Improved progress bar for searching objects, prevented blank workbook when opening files from network shares

2.0, 050

Improved reporting by adding hyperlinks; fixed some bugs

2.0, 051

Added hotkey support for Objects and Check Formulas.

2.0, 052

Added double-click functionality to the tree views

2.0, 053

Added ability to disable hotkeys

2.0, 055

Added highlight formula blocks on "Check formulas" dialog, Improved performance of Check formulas.

2.0, 056

Fixed bug regarding local range names

2.0, 057

Small optimizations

2.0, 058

Changed registration behavior, registered copies no longer access the Internet to check for registration

2.0, 059

Fixed a compile error

2.0, 060

Added the "Formula Report" functionality

2.0, 061

Fixed bug regarding startup screen not appearing in Excel 2013

2.0, 062

Fixed a bug regarding registration of hotkeys

2.0, 063

Fixed bug regarding startup screen not appearing in Excel 2013 (caused by an Office 2013 Update)

2.0, 064

Fixed a window resizing bug

2.0, 065

Improved calculation timer

2.0, 066

Improved grouping in the Objects dialog, fixed screen updating bug

2.0, 067

Avoid trying to remove arrows when no arrows have been added

2.0, 068

Added Alt key support for hotkeys

2.0, 069

Fixed bug (tree view not responding)

2.0, 070

Fixed a small bug

2.0, 071

Fixed small issue with formula report and worksheet names that resemble dates

2.0, 072

Fixed window handling for Excel 2013 and up

2.0, 073

Fixed small bug regarding editing the formula in the dialog

2.0, 075

Added unprotecting/protecting of workbook and worksheets, Fixed issue with removal of arrows

2.0, 076

Fixed error message when quitting Excel without open workbooks.

2.0, 077

Fixed asking for passwords when saving files

2.0, 078

Fixed hang when using check formulas

2.0, 079

Fixed a number of windowing problems related to Excel 2013 and 2016, fixed a bug in the Find Object references

2.0, 080

Improved performance finding dependents, improved how Objects are listed

2.0, 081

Improved error tracing, fixed bug regarding sheet names with a pipe character

2.0, 082

Fixed a bug regarding whole-column references

2.0, 083

Fixed a bug in the sheet stats module

2.0, 084

Fixed objects bug and added automatic update of precedents/dependents tracking

2.0, 085

Fixed bug regarding setting error tracing hotkey

2.0, 087

VBA code signed with new certificate

2.0, 088

Added an off-sheet references report which visualizes the inter-sheet formula links of your workbook

2.0, 091

Fixed a number of bugs

2.0, 092

Fixed a bug related to Excel 2013 and 2016; Improved Off-sheet references report to include checks for Tables and range names

2.0, 093

Fixed a bug pertaining to unhiding worksheets

2.0, 094

Fixed a bug causing crashes of Excel 2013 and 2016 when other certain add-ins are loaded

2.0, 096

Added back double-click functionality to the tree view (same action as clicking Do ActiveCell

2.0, 097

Improved scrolling within a table

2.0, 098

Added a new function: Display formula as a mathematical equation

2.0, 099

Fixed small bug in report formulas

2.0, 100

Fixed bug regarding visualizing merged cells

2.0, 101

Added workaround for Excel bug affecting the "Display Equation" function in non-English Excel versions

2.0, 102

Fixed bug changing objects hotkey; added work-around for people having issues with the Visualize option; fixed an issue with selecting objects from the tree view.

2.0, 103

Fixed not selecting off-sheet references from the precedents/dependents trees (bug introduced in build 102)

2.0, 104

Improved scrolling the selected cells into the viewable area of the screen

2.0, 105

Fixed bug, Pivottables were not listed when pointing to a table name

2.0, 106

Fixed issue with short-cut keys not responding immediately after opening Excel

2.0, 107

Fixed bug where range names were no longer listed (introduced with build 105)

2.0, 108

Added jump to pivotsource when you trace precedents when in a pivottable

2.0, 109

Fixed repositioning of screen when re-activating dialog from Excel

2.0, 110

Build 109 caused 64 bit versions of Excel to crash, this is fixed in this build.

2.0, 111

Improved performance for Excel 2013 and 2016

2.0, 112

Fixed progress bar problems (Excel 2013, 2016) by enabling user to use taskbar instead

2.0, 113

Finally fixed an intermittent issue with the Visualize functionality on Excel 2016

2.0, 114

Improved performance on analyzing conditional formatting formulas

2.0, 115

Fixed a small bug only occurring when you have a chart sheet selected

2.0, 116

Enabled Cyrillic (and other non-western) character set for table names

2.0, 117

Improved working of Tile option placing the dialog next to the Excel window, Improved reporting of formulas

2.0, 118

Added an option to settings to enable or disable automatic unhiding of worksheets

2.0, 119

Added Report Function Count, which reports a count of all Excel functions used in your model

2.0, 120

Fixed a small bug

2.0, 121

Improved performance of Dependents search

2.0, 122

Fixed a bug with finding Dependents introduced with build 121

2.0, 124

Updated "phone home" for registration

2.0, 125

Included Array formulas in circular reference checks

2.0, 126

Fixed runtime error due to third-party add-ins when checking if tool is installed

2.0, 127

Adapted site addresses to use https

2.0, 128

Added code to improve updating experience

2.0, 129

Improved performance of formula checking and reporting significantly

2.0, 130

Not published

2.0, 131

Improved display of Object dependencies, improved tiling windows next to Excel.

2.0, 132

Updated the tool so it works with the new Excel Data Types (Geography and Stock) and with the new Dynamic Array references

2.0, 133

Not published

2.0, 134

Improved finding, displaying and reporting of cell references used by Objects

2.0, 135

Fixed an issue with finding references in chart SERIES formula

2.0, 137

Fixed a visualization issue

2.0, 138

Fixed a registration issue

2.0, 139

Changed check for registration

2.0, 140

Enabled selecting pivot table belonging to pivot chart

2.0, 141

Couple of bugs fixed

2.0, 142

Fixed a bug regarding handling of string literals in a formula

2.0, 143

Improved the Check Formulas interface

2.0, 144

VBA Project is now signed with a trusted code signing certificate

2.0, 145

Fixed problem with not finding reference to total row of tables in the format Table1[[#Totals],[May-2019]]

2.0, 146

Fixed problem with not finding reference due to an alt+enter character in the formula

2.0, 147

Improved the layout of the Object references dialog

2.0, 148

Fixed the wrong calculation setting (use 1 core only) of the add-in

2.0, 149

You can now choose whether or not to display references in your formula more than once

2.0, 150

Added a Reset button to the settings screen to remove registration details

2.0, 151

Added PowerQuery M code to Object search. Fixed a bug regarding finding cell references in Objects

2.0, 152

Improved performance and added an option to prevent the tool from offering to unprotect worksheets

2.0, 153

Internal version

2.0, 154

Fixed a bug relating to 64 bit Office. This is a recommended update if you are using 64 Office, which nowadays is the default version installed with a Microsoft 365 license

2.0, 155

Added two new options: 1. Generate a Table of Contents and 2. Added a feedback button which takes you to a small survey so you can tell me what you like and what you do not like.

2.0, 156

Small fix for Excel 64 bit.

2.0, 157

Fixed bug in Search Objects feature: chart titles with formulas are now properly listed with their charts.

2.0, 158

RefTreeAnalyser now recognizes external references in charts and no longer ignores them.

2.0, 159

The display of objects has been improved.

2.0, 160

Small bug fix to ensure backward compatibility with Excel 2013 and older.

2.0, 161

Workaround for rare error regarding SAP add-in.

2.0, 162

Bug fix for 64 bit Office.

2.0, 163

Ensured obscure reference containing both a table total row and a cell outside the table works.

2.0, 164

Added Form and ActiveX controls to the table of content option

2.0, 165

Added Tables to the table of content option

2.0, 166

Two updates: 1. The link back to the ToC is now independent of the name of the file. 2. If you press Precedents when a chart is selected, you get all references pertaining to that chart.

2.0, 167

Fixed a bug which I introduced in build 166 :-(

2.0, 168

Worked around a bug which Microsoft introduced

2.0, 169

Fixed an issue with the Analyze off-sheet references tool

2.0, 170

Improved sorting of chart series nodes on Objects dialog

2.0, 171

Not published

2.0, 172

Fixed a bug in "Off-sheet references"

2.0, 173

Added warning message if you ask to find too many cells with dependents

2.0, 174

Improved 'Select cells with dependents'; Added counting UDF and LAMBDA functions.

3.0, 175

New version number! The new feature to count all udfs and LAMBDAs in your file has been vastly improved. Now uses top-left cell of a spill range to find formula precedents.

3.0, 176

Fixed a bug pertaining to older Excel versions (2007-2016)

3.0, 177

RefTreeAnalyser now adds External links to the Table of Content.

3.0, 178

Now also finds references in ColosScales Conditional formats

3.0, 179

Added a Report button to the Check formulas feature. Changed ToC behavior: don't insert row for sheets already containing a hyperlink to the ToC.

3.0, 180

Fixed that the VBA Editor Control toolbox opens up when tracing dependents.

3.0, 181

Fixed bug in Circular reference feature

3.0, 182

New feature: highlight cells with the same formula

3.0, 183

Improved cell highlighting feature

3.1, 185

Improved highlighting feature by reducing how often it recalculates. Added more levels to the dependents/precedents dialog

3.1, 186

Improved highlighting feature: limit how often it causes scrolling

3.1, 187

Improved highlighting feature: changed highlighting method

3.1, 188

Improved display of object references

3.1, 189

If the formula returns a range reference (like the INDIRECT, INDEX and XLOOKUP functions do), this is shown in the dependents tree

3.1, 190

Fixed a bug that caused run-time errors when using the Check Formulas option. Made the dialogs compatible with screen reader software for the visually impaired. First build that is Mac compatible!

3.1, 191

Fixed bug regarding highlighting named ranges containing . Characters. Added option to hide the display of cell values in the reference tree

3.1, 192

Fixed a display issue in Analyze Objects.

3.1, 193

Fixed an issue in Off-sheet references.

3.1, 194

ToC now shows if a table is part of the Data Model and if a pivot table uses the Data Model

3.1, 195

Enabled perpetual licenses (available on request)

3.1, 196

Cater for EnableCalculation setting of worksheets, improved Sheet stats report, improved Formula report, unprotect sheets before all sheet stats.

3.1, 197

Fixed some issues regarding the size of the dialogs. Fixed Mac registration bug

3.1, 198

Asks to re-use existing report sheets. Cancel the unprotect workbook or worksheet dialog and it will not reappear for other worksheets. You can now filter the Objects list.

3.1, 199

Fixed wording of report sheet dialog. Fixed report sheet causing display of gridlines to change. Fixed bug in insert ToC for Excel in different languages.

3.1, 200

RefTreeAnalyser tries to prevent windows from appearing off-screen when you change from a large to a smaller display

3.1, 201

Fixed form positioning for Mac

3.1, 202

Improved sheet interaction report. Added option to settings to keep pre-existing report sheets or overwrite them

3.1, 203

Added search in conditional formatting: databars, color scales and icon sets, Added Python support, Fixed bug in highlighting reference in formula, Fixed bug in Visualize, Fixed bug in insert link to ToC, Fixed bug in Formula report, now ignores text values starting with =

3.1, 204

Updated functions list; Fixed bug pertaining to perpetual licenses; Fixed bug dependents error; Fixed bug circular references OneDrive files

3.1, 205

Fixed an issue with protected sheets when analysing sheet links

3.1, 206

New menu item: Select cells with hard-coded numbers

Buy RefTreeAnalyser now!



Comments

All comments about this page:


Comment by: Alex (30-6-2010 03:06:24) deeplink to this comment

I bought the recent version one month ago - how can I assign a shortkut key to start the tool (using Excel 2007)? Thanks


Comment by: Jan Karel Pieterse (30-6-2010 03:42:39) deeplink to this comment

Hi Alex,

After installing the tool, the shortcut keys control+shift+[ and ] should launch the tool.
This is handled by a small Excel addin that should be checked to load at start of Excel. Check your list of add-ins (shortcut key is alt+t,i). There should be one called Reftreelauncher. If it is checked, uncheck it, close the dialog, open the dialog again and check that addin. Otherwise just check the addin.


Comment by: Apostolis (16-5-2012 09:15:33) deeplink to this comment

Hi Jan,

I have been using the ReftreeAnalyser utility for quite long; congratulations, it's a great app! I just wanted to point out that the utility works only if the user has administrator's rights. The menu is not even visible in the ribbon in a user account with no administrators' rights. Can the utility be fully functional in Excel for such users, assuming the appication is deployed from a system administrator?


Comment by: Jan Karel Pieterse (16-5-2012 10:08:43) deeplink to this comment

Hi Apostolis,

That is odd, it should work with "normal" rights as far as I know.

Can you start it with the shortcut keys control+shift+[ and control+shift+] and control+shift+| ?


Comment by: Dave Hockin (7-11-2013 18:35:49) deeplink to this comment

Bug v2.041 ?
Using excel 2010, Windows 7
Was working fine for a couple of weeks, then today created an issue for excel where the clipboard was emptied on sheet deactivate. Could copy on one sheet, but clipboard empty for paste when on different sheet or between books. I disabled the addin and paste issue was resolved. Reactivate--same problem, Deactivate all is good again, then on reactivate got the New Version (42) avail msg. I accepted it and now working.
Trail of notes for you just so you are aware if it occurs for others.


Comment by: Jan Karel Pieterse (7-11-2013 19:55:15) deeplink to this comment

Hi Dave,

Thanks. That was one of the reasons for publishing build 42 indeed.


Comment by: VJ (26-9-2014 02:51:25) deeplink to this comment

Is there a way to show the exact cells being added through a Sumif/ Sumifs formula. Currently the Find precedent command is showing the range being evaluated but not the exact cells being added


Comment by: Jan Karel Pieterse (26-9-2014 11:48:10) deeplink to this comment

Hi VJ,

I'm afraid that is not in the current scope of the tool. I do like the idea, but it would require quite a lot of logic, since one would want to make this as generic as possible (so not just for SUMIFS).


Comment by: Jon Spain (17-4-2015 16:38:15) deeplink to this comment

Hi, I saw RefTreeA on NrExcel, I know your name and I've just placed an order for my own laptop. Is a separate license for my own PC needed, please?

Jon


Comment by: Jan Karel Pieterse (17-4-2015 22:12:00) deeplink to this comment

Hi Jon,

The license is personal so you can use it on any system as long as it isn't at the same time :-)


Comment by: Brian (1-3-2017 20:43:37) deeplink to this comment

How can I eliminate the utility? When I have an Excel spreadsheet linked to an AutoCAD drawing I get an error message, but both programs seem to be working right. The exception is that the AutoCAD file size increases by 10X anytime I open a drawing file once the Excel file has been linked to it. So I need to get rid of it. Can you help me with this?


Comment by: Jan Karel Pieterse (2-3-2017 10:23:26) deeplink to this comment

Hi Brian,

Go to File, Options, Add-ins tab. Click the Go button and uncheck the add-in.
Odd though, the utility is not supposed to make any changes to your files so I am surprised it somehow does?


Comment by: Wiens (26-9-2019 11:29:00) deeplink to this comment

In the changelogs of the RefTreeAnalyser on this page the text is in Dutch instead of English:


2.0, 069

Bug gefixt (treeview reageert niet)


Comment by: Jan Karel Pieterse (26-9-2019 12:05:00) deeplink to this comment

Hi Wiens,

Thanks! I've fixed that now.


Comment by: Wiens (27-9-2019 15:18:00) deeplink to this comment

Hello Jan Karel,

Thanks for giving me the link to Stafford Quaid.
I have already contacted with him. I'll hope the Office issues will get fixed soon ;).

There is an issue on this page with the changelogs list of RefTreeAnalyser. The text aren't correctly orriented in the table and also the last changelog is added on the right side of the screen.


Comment by: Jan Karel Pieterse (27-9-2019 19:51:00) deeplink to this comment

Hi Wiens,

So I see, thanks, fixed the layout.


Comment by: ggroh (27-12-2022 08:29:00) deeplink to this comment

"Tracing Cell dependents and precedents" window move different position, when click cells everytime. It's unconvenient..


Comment by: Jan Karel Pieterse (30-12-2022 14:14:00) deeplink to this comment

Hi,

When you say "move different position", when exactly does this happen? It doesn't for me.


Comment by: Wiens (19-3-2023 03:43:00) deeplink to this comment

The change log for V3.1 (Build 195, Updated Mar 17, 2023) is missing in the list above.


Comment by: Jan Karel Pieterse (20-3-2023 13:42:00) deeplink to this comment

Hi Wiens,

Some builds are never published, this is one of them :-)


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.




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