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 the facility 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
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’.