Excel Addin: Sheet-Scoped Name Generator[ ]
I’ve been crafting Excel formulas for fifteen years now, and recently I’ve been seeing the advantages of using the built-in ‘defined names’ functionality. For example, if I have a worksheet that contains a number of physical constants that I’m going to be referring to on a regular basis, it’s a huge time- and brain-saver to define a name for each cell. Case in point, this (now outdated!) periodic table I put together a few years back:
Each of the molecular weight values shown here is in a cell with an assigned name of the format
Xx is the atomic symbol of the element of interest. So, to calculate the formula weight of calcium sulfate , all I have to do is:
Names can be defined on single cells as well as multi-cell ranges, though most of the time I’ve found naming single cells to be most valuable. Names can be created singly via the Name Manager, accessed by pressing Ctrl+F3 or via
Formulas > Defined Names > Name Manager in the Ribbon:
A key thing to note: when adding new names via the Name Manager, it’s possible to choose whether the defined name is created at the scope of the entire workbook, or of a specific worksheet:
For global constants like atomic weights, the workbook-level scope makes sense. However, much of the time I’m working with multiple ~identical datasets on different tabs, each of which benefits from its own independent set of named cells. Here, the worksheet-scoped names make far more sense.
Excel comes with a built-in tool for quickly defining a large number of names at the workbook scope based upon the values in neighboring cells, accessed either via Ctrl+Shift+F3 or by
Formulas > Defined Names > Create from Selection in the Ribbon. Take my periodic table example; say I highlight one of the elements and its atomic weight like this:
Create from Selection shows the following dialog:
Excel has automatically selected
Top row, which is indeed what I would want to use here: clicking
OK would create a workbook-scoped name of
Ni assigned to the cell containing
58.693. (Note that I didn’t actually use
Create from Selection to make this periodic table, since my names have that
w_Xx syntax, but this gets the idea across.)
So, this is handy – what’s the problem? Well, the problem is that, to the best of my knowledge, there’s no way to do this sort of bulk, automatic name definition for worksheet-scoped names.
Download the most recent
SheetScopedName.xlamfrom the Releases page.
Follow the instructions here to install and activate the add-in (looking for
Sheet-Scoped Name Generatorinstead of
CSV Exporterin the add-ins list).
To create worksheet-scoped names, start by highlighting a block of cells that you want to be named based on the contents of the cells to the left, e.g.:
Then, press Ctrl+Shift+N and the names will be applied to each cell:
The Issues page has a few known bugs to be fixed, and some potential enhancements to be implemented.