how to use names in formulas.
We have seen that when you press F5, Excel displays its Goto dialog. You can identify a cell you would like to visit by
| • | either, typing a cell reference, such as B2 |
| • | or, typing or selecting a name that you have defined already, such as Costs. |
What we will learn now is that the same is true when writing formulas. You can specify the cells that participate in the calculation by
| • | either, typing a cell reference, such as B2 |
| • | or, typing or selecting a name that you have defined already, such as Costs. |
Assuming you have completed the exercise in lesson B, your active worksheet will contain a cell named Sales and a cell named Costs. You will also have a cell calculating Profit.
| • | The profit cell is the one showing the value 85 |
| • | Its formula is =B1-B2. |
In a blank cell, type a new formula. Type =Sales-Costs. You will find that you get the value 85 again.
In interpreting this formula, Excel has
| • | seen that we have used the name Sales |
| • | checked its definition |
| • | seen that it refers to cell $B$1 |
| • | taken us to mean =$B$1-Costs. |
It has then done a similar substitution for Costs, to get a formula =$B$1-$B$2 which, with the exception of four dollar signs, is identical to the original version of the Profit formula.
Tip: The presence or absence of dollar signs makes no difference to how a formula is evaluated. It only makes a difference to Excel's action when Excel the formula is copied into other cells.
The names we are using here are easy to type, being only five letters long. Sometimes, names may be longer and more convoluted. Rather than typing them ourselves, and perhaps spelling them incorrectly, we can get Excel to type them for us.
In another blank cell
| • | start to type a new formula by typing = |
| • | press the F3 key |
| • | from the list in the dialog that appears, choose Sales, then click on OK or press Enter |
| • | type - (the minus key, because we wish to perform a subtraction) |
| • | press the F3 key |
| • | from the list in the dialog that appears, choose Costs, then click on OK or press Enter |
| • | press Enter to finalize entry of the formula. |
Again, the result will be 85.
F3 is the short cut for the Excel command Formula | Defined names | Use in formula | Paste names. It is similar to the Goto command which we've already practiced, in that it presents a dialog displaying list of names that have defined already. However, the circumstances when it is useful, and the action when it is used, are rather different.
Short cut |
F5 |
F3 |
Command |
Home | Editing | Find & Select | Goto |
Formula | Defined names | Use in formula | Paste names. |
Normally pressed |
When not in edit mode |
When in edit mode, to enter a formula |
Action |
Selects the cells that are associated with the name. |
Pastes the name selected by the user into the formula being constructed. |
Dialog |
|
|
See also