|
Excel's Apply names command.
| 1 | If you have followed the lessons so far, you will have a spreadsheet containing |
| • | a cell which you have named Sales |
| • | a cell which you have named Costs |
| • | three cells which calculate profit. One does so using traditional coordinate references, and the others do so using names, =Sales-Costs |
| 2 | Select the cell which calculates profit using coordinate references. (It was at cell B3, but is likely somewhere else now as we moved things around in Lesson D.) |
| 3 | Now invoke Excel's Formulas | Defined names | Defined name | Apply Names command. Like the F5 (Goto) and F3 (Formula | Defined names | Use in formula | Paste names ) commands, this one offers a dialog that presents a list of the names defined in the workbook. |
| 4 | Choose the entry for Sales and click OK or press Enter to dismiss the dialog. |
| 5 | Now examine the coordinate formula. A moment ago it said =B1-B2 (or similar; the actual references may be different as we moved the cells around). Now it will says =Sales-B2 (or similar) |
| 6 | Repeat the process, this time applying the name Costs. The formula will be transformed to =Sales-Costs. |
By this means we have discovered that the action of the Apply names command is to go through a portion of a spreadsheet looking for opportunities to replace coordinate references in formulas with names.
The Apply names command has two uses
The obvious use for Apply names is that it leaves us free to enter formulas, and define names, in arbitrary orders. We don't have to think of all the names before we start a spreadsheet and define them in order for them to be available for us to use. We can write a spreadsheet using traditional coordinate formulas, decide to define a few names, and then have Excel do the hard work of adapting the formulas that are already in place to use the just-specified names.
The less obvious use is that writing a formula in coordinate notation, defining some names, and applying those names is great for learning. Sometimes Excel will replace the coordinates in a formula with names and sometimes it won't. Working out why it behaves as it does is much the best way of understanding how Excel interprets names.
Tip: The golden rule is that Apply names never makes a substitution that would cause the meaning of a formula to change. Every time that you expect Excel to substitute a name for some coordinates in your formula, and it doesn't, is an opportunity to revisit your understanding of what the formula would have meant had the substitution happened, and why it is not the same as what you have written in coordinate form.
See also
Names tutorial F
Names tutorial fine print
|