Doing some work on an Excel spreadsheet this morning reminded me that there are some great products that have been developed that enormously enhance the ease of use, flexibility, and general usefulness of Excel. Whilst this blog is not really about promoting Excel products, there is one product that I believe stands head and shoulders above any other out there. I am not a great fan of installing Excel addins, they usually have 200 functions of which I only want 1 or 2, but I have installed this addin and I don’t believe there is ever a day that I do not use it. Because of this, and because the price is spot on (it is free), I am going to shout the praises of Jan Karel Pieterse’s NameManager addin.
This tool has been around for a number of years, and has been indispensable if you use Excel names extensively (which I do). There is a debate to be had about whether we should use names, some swear by them, some swear at them, but that is for another day.
Using the names dialog in pre 2007 versions of Excel was painful. I am of course referring to the Insert>Name>Define… dialog which threw up this incredibly helpful beast
There were a few other concessions to usability, Debra Dalgleish is highlighting thefacility on her blog today, but generally it was hard work. That is, until JKPs addin came along. Suddenly, it was possible to see all of your names in a sensibly structured dialog, there were filtering options, you could evaluate names, see if they were being used, and much more. Compare this dialog
to that previous dialog, look at the richness of facilities, the options, but most of all the sensible presentation. When managing names, it is imperative in my view to see as much information as possible, limited by my choice, not the limitations of the tool.
Of course, MS have revamped Excel, and in Excel 2007, they introduced their own version of Name Manager. With the experience of running the old dialog for many years; the example of better version to draw upon (JKPs addin); and the fact that they can tap into the heart of Excel, MS were bound to produce the definitive Name Manager. Right? Well, not quite. This is an example of the dialog
It is undoubtedly better than MS’ previous attempt. Seeing the names in a resizable dialog, with the Refersto Value and the scope is good, but it still falls far short of JKPs NameManager. It is cleaner than JKPs NameManager, but that is because it lacks so much. There is no option to evaluate a name, not all names resolve to a single value, which is incredibly useful; no option to highlight where names are used; no capability to redefine the scope of a name (if you try in the Edit dialog, it tells you that the scope cannot be changed – why?); changing a name’s name does not interact with VBA as NameManager does; but worst of all, it seems totally oblivious to hidden names. (BTW, you can add comments to names in Excel 2007. I cannot see where they appear, so fail to see their usefulness. Does anyone think this is a good addition that they will use?).
All in all, the 2007 Name Manger is a big disappointment to me, and JKP’s NameManger cannot be retired just yet. If you use names a lot, do yourself a favour, rush out and buy a copy of JKPs NameManger today. You CAN afford it, it is available here.
Perhaps JKP should rename it to ‘The Real NameManager’.