A QUICK WAY TO CREATE LOTS OF NAMES
So far in this tutorial, each time we have have wanted to associate a name with some cells, we have used the Formulas | Defined names | Define name command. We have needed to invoke that command once for every name we have added.
The spreadsheet that we use as our example in this lesson has have 5 lines of numbers. We could associate useful names with each line by issuing the Formulas | Defined names | Define name command five times, once for each line. That works perfectly well,but we are going to learn a quicker way to do it.
| 1 | To get started with this lesson, load the example spreadsheet Names tutorial G Create names.xls. |
| • |  |
| 2 | Once the spreadsheet has loaded into Excel, press the F5 key. As we learned in Lesson A, that is the shortcut for Goto, and brings up a dialog. |
Satisfy yourself that the dialog has no entries for names defined in this workbook.
| 3 | Select the cells A1:F11. |
Use Excel's Formulas | Defined Names | Create from selection command.
A dialog will appear.
Excel may have preselected the option Left column. If it has not, do so your self.
| 4 | Accept the settings displayed in the dialog by clicking OK or pressing Enter. |
| 5 | Press the F5 key again. See that Excel has now added names corresponding to each of the lines. |
| 6 | Try choosing one of the names and pressing OK. You will find that Excel will select the numbers next to the label coinciding with the chosen name. |
Some things to understand
Both the Formulas | Defined names | Define name and Formulas | Defined Names | Create from selection commands add names to the workbook, but their action is slightly different.
| • | Formulas | Defined names | Define name is the more general command. Anything that can be done to a name, it can do, including deleting unwanted names, and advanced actions that we haven't learned about yet. But it can only perform these actions on one name at a time. That's why it is Define Name, in the singular. |
| • | Formulas | Defined Names | Create from selection is much more limited. It can only make names that there are one row high or one column wide. But it has one big advantage, as we have just seen: it can make lots of names in one action. That's why it is Create Names, in the plural. |
| • | When we made names in earlier lessons, we used Formulas | Defined names | Define name. The names that resulted were exactly the shape of the area we selected immediately before invoking the commands. |
| • | When we made names in this lesson, we used Formulas | Defined Names | Create from selection. The names that resulted were not the same shape as the area we had selected. They are each only one row high, though we selected nine rows. And though we included columns A:F in our selection, the names only cover columns B:F. |
| • | When the Formulas | Defined names | Define name command is invoked, Excel displays a dialog which it populates with its suggestion for a name, taken from a cell near to the selection that has plausible looking contents. But if you don't like the suggestion, you can overtype it. The Formulas | Defined Names | Create from selection command takes names from the cells specified (here, the left column of our selection, column A), and offers you no option to change them. |
If you choose to use many names in your models, Formulas | Defined Names | Create from selection's ability to define a lot of them in one action will save time compared with Formulas | Defined names | Define name's ability to define only one at a time.
See also