There are many small details that we left out of this tutorial, in order to avoid distracting from the main purpose, which is to explain how to use Excel's naming features as concisely as possible.
Here are some of those details.
LESSON A: GOTO and Lesson B: Defining names
Goto (F5) is not the only way to Goto a particular cell and Formulas | Defined names | Define name is not the only way to define a name. Microsoft's literature illustrates another method, which is to use the area next to the formula bar. Microsoft calls this space the name box.
The Name box acts as a combination of the Goto and the Define names commands.
| • | Type a cell address into it, and Excel will make that the selection, just like the Goto command. |
| • | Select an area, and then type a name into the name box, and that name will be associated with the area, just as it would have been through the Formulas | Defined names | Define name command. |
| • | Click on the arrow to the right of the name box, and a list will drop down, filled with names that have been defined in the workbook or worksheet. Choose one, and you will be taken to the area associated with the name, just as you would have been if you had pressed F5 and selected the name from the Goto dialog. |
| • | Rather than selecting a name from the list, you can type it into the Name box and hit Enter, and Excel will select the relevant area. (But be careful. If you mistype the name, Excel will add a new name, defining the mistyped name as referring to the current selection.) |
We find this last point in brackets very unattractive
| • | in principle: Is Excel reading from the list of names, or writing to it? The decision, is context sensitive, depending on what you type, and what has been typed in the past. |
| • | in practice: Excel permits you to mess up the list of carefully defined names, without warning you about it. |
There are several other reasons why we don't recommend using the name box.
| • | There is no way to reach the name box from the keyboard. It is activated by clicking in it, which involves removing one's hands from the keyboard. While not everyone favors the keyboard over the mouse, we don't like actions that don't offer the option. |
| • | In admittedly very old versions of Excel, the drop down box didn't exist at all. |
| • | In some versions of Excel, the name box drop-down list only shows the first few dozen of the names that have been defined. If you want to interact with a larger number of names, you are going to have to use the Goto and Formulas | Defined names | Define name commands anyway. You might as well get into the habit from the outset and use those basic commands all the time. |
| • | In versions of Excel before 2007, the width of the Names box is fixed, and not very large. Longer names are truncated. Similar names are indistinguishable. Excel 2007 fixes this by making the Names box resizable. |
We favor the Goto and Formulas | Defined names | Define name commands as they have been present in a consistent way in every version of Excel from the first, and don't suffer any of these unattractive qualities.
In our example, the cells that we wanted to name happened to have labels nearby that said Sales and Costs. Excel offered them as suggested candidates for names when we used the the Insert Name Define Command.
Where a label consists of more than one word, as in Capital expenditure, Excel would propose Capital_expenditure as the suggested name. The reason is that names can be made up of letters, digits, the underscore, and a full stop (period). Other characters are not allowed, so Excel replaces the space, which is not allowed, with an underscore, which looks similar and is allowed.
Operis's own style is not to accept Excel's suggestions when they involve underscores. Underscores are hard to see next to the spreadsheet's grid lines, and can be mistaken for spaces or, less commonly, minus signs.
They also subconsciously disrupt the flow of the formula when reading it. =Capital_Expenditure+Income_Tax reads like Expenditure+Income embedded in the middle of some other material.
Instead, Operis recommends using a mixture of upper and lower case letters to indicate the word boundaries, as in CapitalExpenditure-IncomeTax. Some people call this mixture of letters camel case, or bumpy case.
The way name definitions are adjusted automatically when the cells they refer to change location is consistent with the way coordinate references in a formula are adjusted when the cells they refer to move.
However, the effect of moving a formula expressed in names is frequently not the same as the effect of moving a formula expressed in coordinates.
| • | Cut =$C$7 from C10 and paste it into D10: It will still refer to $C$7. |
| • | Cut =Costs from C10 and paste it into D10: Assuming Costs is defined as =$C$7:$M$7, the formula will change from referring to C7 to referring to D7. |
The formula is unchanged, but its interpretation in a new location will be different due to column matching.
Some see this difference in behaviour between formulas expressed in names and formulas expressed in coordinates as confusing. Others consider it valuable; formulas expressed in names will always refer consistently to columns, no matter how much mutilation they suffer with cutting and pasting.
The Apply names command obeys the One Cell Rule. That's why we included the direction in Step 2 "Select the cell which calculates profit using coordinate references...". It's partly because that cells contains a formula, and we want to see how it is affected when we start using the Apply names command. But it's also so that the relevant cell will in fact be acted upon by the command.
The Apply names command described in this lesson is built into Excel. OAK has a command, also called Apply Names, which looks almost identical and performs the same action, but is more convenient and more powerful.
Lesson I: Row and Column Matching
A non-obvious point is that row and column matching is connected with names only indirectly. Expressions such as =Costs are resolved to =D7:M7 (or whatever the name definition indicates). That formula cites several cells and will typically provoke column matching.
The column matching would equally happen if the formula had been written in coordinate notation. To this extent, it is not a property of the names directly, but a fundamental mechanism of the way Excel interprets a formula that is indirectly activated when names are used.
LESSON K: Controlling aggregation
The Apply names command obeys the One Cell Rule. That's why we included the direction in Step 2 "Select any one cell."
Opinion is divided on the technique taught here. Operis finds it valuable, uses it and teaches it. Others feel that it demands a finely detailed knowledge of how Excel works that is not universal, and therefore not suitable for use in spreadsheets that are to be widely accessible. Common ground can be found in the advice that wherever there is ambiguity, the clearest possible formulation should be used.