Using names to refer to cells and ranges improves transparency. Good choice of names is a way of self-documenting your project. Name Manager also has a comments field to elaborate the genealogy of each name. Names can also define custom formulas.
Rules
There are few rules for names. The most obvious is that a name cannot be a cell address. Everything from A1 to XFD1048576 refers to a cell and cannot also be a name. An attempt to define such a name will be rejected with a message that is not entirely helpful. It lists three things to verify: the name starts with a letter or underscore, doesn’t have a space or disallowed character, and doesn’t conflict with an existing name. If it’s the first name in the workbook, that might make you wonder; it just means that you’ve entered a cell address.
The message is also not entirely accurate, the name can also start with a backslash. And how is “don’t use a character that isn’t allowed” help, if you don’t tell me what’s allowed? Below is a summary of what is allowed.
A name can be as long as 256 characters! That’s crazy – a name is meant to be a shorthand reference. Try to develop a scheme for names that is both unmistakable and concise. Names retain case, but are not case-sensitive. Thus you can create easy-to-read names like AvgScore or NetSales, but you can’t have both NetSales and netsales.
You might think of short clever names that use 3-letter abbreviations or acronyms, such as APR or VAR or GDP. That’s fine, but if you have multiple versions and try to number them like VAR1 or GDP2017, you’ll get the error. To get around it you can add underscore or backslash or period, avoid ending with numbers, or use four or more letters. You could use three letters from XFE to ZZZ, but there are probably not many useful acronyms in that range.
If you use the “Create from Selection” method (Shift+Ctrl+F3) with an invalid name like VAR1, Excel will oblige by giving you VAR1_. Keep this in mind whenever you need to number similar names. Try to keep names concise, but use whole words if you think it will enhance clarity.
When you begin or continue typing a formula and enter a few letters, the look-ahead picklist includes functions and names. After the first few characters, use down arrow to highlight and tab to select. This is a reason to design names well to be easy for you to remember and select this way. Avoid using the name of a function to prevent confusion.
Scope
Name scope can be either workbook (global) or worksheet (local). The default is global, which is good for most cases. Global names are easy to use in formulas anywhere, and do not require sheet reference since they are unique within the workbook. However, locally-scoped names can play a role in elegant design, especially the more sheets and names that you have.
Global names are unique and visible from all sheets. Local names can duplicate names that are either global or local to other sheets, and are only visible from their own sheet. They can be seen in Name Manager, but not in any other sheet. Even though not seen, local names from other sheets can still be used by explicit reference including the sheet name, as shown in the table above.
The lesson for names is to think about the design ahead of time. It will surely evolve as the project develops. If you’re prepared and keeping up with changes as you go, you’ll avoid rework. If you create names after writing formulas, there are ways to apply names, to replace common cell references in existing formulas. If you change your mind on name spelling, simply edit in Name Manager and all formulas where the name is used will be updated. If the name is referenced in VBA, however, it must be manually edited.
Fixing type
Fixing type is absolute versus relative referencing. Fixing means locking in position, with absolute reference to the column, the row, or neither, or both. All four fixing types can be useful. By building them into names, formulas will be clean and intuitive.
An absolute range name is the simplest and default name type, often used with a single input cell. For example, you might have a cell labeled “enter phone number”, name it Phone, and use Phone to look up the customer name, account, etc. If you have controls such as option buttons or checkboxes, you would want to pass the control’s value to formulas by a name with absolute reference to the linked cell. You might also use a multi-cell absolute name to store a pick list for Data Validation or a List Box control. An absolute name can be defined in Name Manager or simply by typing the name into the Name Box at the left end of the Formula Bar.
Hybrid fixing is when one dimension is absolute and the other is relative. Most often the column is absolute because you have columns of input data and columns of output formulas. With relative row reference, every output formula within a column is identical. This is a common and powerful use of hybrid names, so let’s look at an example.
Let’s say you have two columns, C with inventory and D with unit cost, and you want to calculate the inventory value per item. If the data begin in row 2, you start in E2, enter =D2*C2. If you use arrow keys to enter the formula, the sequence would be: = ← * ← ← Enter; and you would get the default relative fixing type. Then you copy it down the column, and get E3 = D3*C3, etc. and all is good.
Now, with the cursor in E2, create the name “Quantity” to refer to $C2, absolute column, relative row. To enter the Name, follow these steps:
1. Ctrl+F3 to open Name Manager
2. Alt+N to open the New Name dialog
3. Enter “Quantity” in the Name box
4. Tab three times to the Refers to box
5. Hit ← twice to select column C
6. Hit F4 twice to fix the column and unfix the row
7. Hit Enter to save the Name
8. Hit Esc to exit Name Manager
Repeat the steps to create the name “Cost” to refer to $D2. Then in E2, enter the formula =Cost*Quantity, and copy it down. You get the same result, but now your formulas are not only readable, but are the same in every row.
If you are using the input column (Cost) in only one output column (Value), technically it doesn’t matter whether the column in the name is absolute or relative. Cost = D2 gives the same result as Cost = $D2. However, by naming it Cost, you are committing to the meaning of the name, which is only valid in column D. As a principle, fix what should be fixed to ensure it cannot be misinterpreted. If you ever need to add another column using Cost, it’s ready.
Column fixing is common and relates directly to table reference style. If the example above is a table, the names come from the column headings, and the Value formula will be =[@Cost]*[@Quantity].
Row fixing is handy for matrix calculations. For example, a multiplication table can be created by entering operands down column A and across row 1, and writing a formula in B2 =$A2 * B$1. Copy B2 to the inside region of the matrix, and as you select different matrix cells notice how the formula changes. To use hybrid names, again with B2 as the active cell, create names Operand1 =$A2 and Operand2 =B$1. The matrix formula is then =Operand1 * Operand2, the same for every cell. Another option is to define a formula name Product =$A2 * B$1, and enter =Product in the matrix cells. Formula names are discussed in the next section. The Startup file Functions sheet has an example calculation matrix.
Finally, there’s a use for names with both column and row relative. For example, I often need to refer to the cell above in formulas or Conditional Formats. If the active cell is D9, create the name Above = D8. Don’t use it in row 1, and beware of the scope. Even though you can refer to a global name from any sheet, the reference is to the sheet on which the name was created. If you create global name Above on Sheet1 and use it on Sheet2, you think you’re getting the cell above, but you are actually getting the cell above on Sheet1. If you want to use a relative name like Above on multiple sheets, create it in each sheet, scoped to the sheet.
When writing hybrid or relative reference names, pay attention to the cursor position. You must be aware of the active cell address to ensure proper reference. Remember what cell you have selected, or with Name Manager open, check the name box on the sheet behind it. If the active cell has an absolute name, the name box displays it, otherwise it displays the cell address.
Named Formulas
A less known but powerful use of names is the formula name. By working in the Name Manager you can create logical names for formulas. You can use any standard or custom function, table references, and other named formulas. Use the comments field to document the purpose and logic of each name.
One way to use this is to build component formulas that are easy to understand and document, then snap them together for the actual output function. You can thus have numerous intermediate formulas that don’t take up any sheet space but are quietly working behind the scenes.
For example, the Team Calendar project has date tables that need the date type – is it a workday, weekend or holiday. This requires multiple lookups that would make the table formulas unnecessarily complex. By using Name Manager to define a DateType formula, the lookups are done invisibly and passed to the table as an index.
This can help you from a documentation and maintenance perspective, and for troubleshooting. To see all calculation steps, select a cell with the output formula and go to Formulas à Evaluate Formula, Alt-M,V, then use the Evaluate and Step In buttons and watch as it unfolds. If needed, insert some temporary columns to fill with the internal formulas so you can see what each one returns.
The caveat for building formulas in Name Manager is that they can only be used where they are relevant. In the Team Calendar example, there are three date tables that each have their own version of the DateType named formula. If they were table formulas that would be obvious, but by setting them apart in Name Manager it takes a conscious awareness and careful naming. Use name and description purposefully to keep it clear, and avoid getting carried away with complex name chains. If you have several sheets, organize names by scoping them to the sheet where they are used.
If the formula uses a table reference it must include the table name. When you reference another column from within the table you just get [@Field]. Name Manager won’t accept that. You either need to type the table name in front of the field, or create the formula outside the table. Select a cell two columns away from the table on any row, and use the mouse or keyboard to select the target table column in the same row. This way you’ll get the Table[@Field] reference format. While still in cell edit mode, copy the formula for paste into Name Manager.
This in effect creates an alias name to reference the table. Table referencing is not accepted in Conditional Formatting or Data Validation. The options then are to discard the structured reference, or to create an alias in Name Manager. Using aliases this way requires the diligence to apply them only where they are valid (i.e. within the table). The benefits are to make formulas easier to read, and the aliases keep structured references.
Behavior
There are aspects of the Name Manager and other formula bars that behave a little differently than you’d expect. They are subtle enough to miss, and pesky enough to cause frustration. It’s worthy to call them out so you can adjust to the context and work like a pro.
First let’s review how the worksheet formula bar works. When you type = to start a formula, and use an arrow key to reference another cell, the initial reference is relative column and row, A1 style. Depending on the context, you accept the default, or to change it, hit F4 one or more times to cycle through the fixing style options, A1 → $A$1 → A$1 → $A1 and → back to A1. This F4 cycle works the same way in all formula bars, but some start the cycle at a different point.
Also, within the sheet, if you click in the formula bar, or double-click the cell, or hit F2, you are in Edit mode. In Edit mode you can navigate within the cell contents by the keys – Home, End, ←, →, Ctrl to jump, Shift to extend, etc. Then if you’re at a point in the formula to insert a reference, you can hit F2 again and select the cell or range on the sheet. This is called Point mode.
The other formula bars toggle between Edit mode and Point mode as well, but start in the opposite mode. You want to step into the formula with arrow keys, but instead you get cell references you don’t want. This can be aggravating if you don’t know about the F2 toggle, so it’s a good point to practice.
To practice all cases, create a sample sheet with one of each: formula cell, named cell, named formula, Conditional Format formula, Data Validation formula. In each case, open the respective formula bar and hit ←, while watching the bottom left mode indicator. Only in the formula cell does “edit” start you off in Edit mode, and does ← move the cursor one character into the formula.
In Name Manager, Conditional Formatting Rules Manager and Data Validation, “edit” starts in Enter or Point mode, and becomes Point mode as soon as you hit an arrow key. Try it again, hitting F2 before ←, and note the difference. Make a mental note that to edit a reference in Name Manager, F2 (or Tab) to select the formula bar, F2 again to switch to Edit mode. Practice until it becomes natural.
Three of the four formula bars are expandable. Data Validation is not, and only holds about 48 characters before it starts to scroll and become difficult to manage. This is one reason to create a formula name in Name Manager. Another is simply to manage complexity in one place.
The table below summarizes the behavior in these four formula entry bars. Once these behaviors and the means to tame them are understood you will easily master them all.
When you copy sheets within the file, names on the sheet are copied as local names on the new sheet. When you copy sheets between files, names can be created with link to the source file. This can cause the file to prompt you to update links the next time you open it. You might wonder where the links came from, and wonder why you can’t find them anywhere on the sheet. Check Name Manager, and see if any references include a file path. Edit or delete these names as needed.
Name Manager as a strategy centralizes complexity where it can be modularized and enhanced with comments. For long calculation chains it is indispensable. It’s also good for creating logical references, shortening entries elsewhere and improving transparency.
Navigation
Name Manager does not have a maximize button, but can be maximized by double-click on the header. Columns are sortable and column widths are changeable. The filter button has six options, two each for scope (workbook, worksheet), errors (with, without) and type (defined, table). Note, the worksheet scope filter is not for the current sheet, it applies for any name scoped at the sheet level.
To change a name that refers to a cell range, click Edit to change the name itself or the comment, or F2 or Tab into the reference bar to change the reference. Since you start in Point mode, navigate to the new selection, or hit F2 again to type it in. To change a named formula, be sure to hit F2 for Edit mode.
Tables have a different icon than defined names, and the reference box is grayed out when selected. Table size can only be changed on the sheet. However, in Name Manager, table name and comment can be changed. If you use a lot of tables, use the filter to show and manage them all.
As you learn to use Name Manager regularly, you will find the methods that make it routine. For example, if you have several sheets and many names, you might sort by Scope, or Refers to. You can use Comments to sort as well as to document names.
Listing Names
If you get into using a lot of names, you might need to list them to auditand document the project. There is an easy way to list all names and what they refer to in a sheet, but watch out – it asks neither permission nor forgiveness for overwriting data! To be safe, insert a sheet. It will consume two columns, and as many rows as you have names. To paste the names list, Alt-M,S,P, Alt+L. Remember that relative names will refer to a cell relative to the cell where the name is pasted.
Another way to list names is with VBA. If you have invested in writing Name Manager comments, you’d want the list to include them, and If you’ve scoped names to sheets, you’d want that too, and in that case you can’t use the Paste List method unless you do it sheet by sheet. The Startup project includes the ListNames macro to create a table of all names with scope and comment, and allows comments to be uploaded from the sheet to Name Manager.
Comments