Names tutorial B: Defining names

Names Tutorial B: Defining names

This page in 2007
Top Previous Next

Associating names with cells using Excel's Define name command

1	To get started with this lesson, load the example spreadsheet Names Tutorial B Defining Names.xls.

Define names example spreadsheet 2007

2	Click on cell B1, which contains 200.

3	Use Excel's Insert \| Name \| Define command. A dialog will appear.

Define name dialog defining name Sales 2007

Excel has already filled in the several elements of the dialog.

We are on cell B1, so it has prepopulated the lower, Refers to field with that cell address.

It has noticed that immediately next to the select cell is another cell which has a piece of text in it. It has prepopulated the upper, Name field with that text, on the basis that there is a reasonable chance that it might serve as a suitable name to give the selected cell.

The word Sales is shaded, showing that it is selected, so that if we disagree with Excel's guess that this might be a suitable thing to call the cell we have selected, we can enter something we prefer, and the guess will be overtyped immediately.

Here, however, we think that Sales is a good description for the 200 quantity; that is why we put the label there. So we can accept Excel's proposals by pressing Enter, or clicking on the OK button, to dismiss the dialog box.

4	Now try press F5, which we learned in Lesson A was the shortcut for Goto.

When we used the Goto key in Lesson A, on a blank worksheet, there were no entries in this dialog. But now there is an entry: one for Sales, the name we defined a moment ago.

Goto dialog showing entry for name Sales

5	Now select cell B2, the one that contains 115. Try Insert \| Name \| Define again. As before, Excel prepopulates the dialog:

•	the address of the selected cell, B2, is at the bottom, in the Refers to field

•	Costs, the contents of a neighboring cell that appeared to offer a plausible candidate for a name, is at the top in the Name field, preselected in case we want to type over it with something we prefer.

Define name dialog defining name Costs 2007

6	Again, we can accept Excel's suggestions by clicking OK, or pressing Enter, to dismiss the dialog.

7	This time, the F5 key shows two names, listed alphabetically.

Goto dialog showing entry for names Sales and Costs

8	Try choosing either of the entries, and clicking OK or pressing Enter to see what happens.

9	Try scrolling far away from the top left corner, then pressing F5 to bring back the Goto dialog, and selecting Sales or Costs and clicking on OK. Excel will speedily select the chosen cell.