Range Names In Excel

How To Define Range Names

There is a number of ways you can name a range of cells.

The first one is by using the Name box to the immediate left of the formula bar (see fig. 1). When the current selected range is already named, the name box will show its name. Otherwise you'll see the address of the active cell in this box. Select the range you want to name and then click in the Name box and type the name. Press Enter to confirm. There is one drawback to this method. When a name already exists, Excel will not apply the name to the selection. Instead Excel will select the range belonging to the existing definition of the name you typed.

So the Name box can also be used to navigate to an existing name. Just select it from the dropdown list or type it in the box and hit enter.

Fig. 1: Name Box Showing Cell Address

A very fast way to create range names can be used when the headings of a table already represent the names you would like to assign to its rows and/or columns. See the table shown in fig. 2:

Fig. 2: Range of cells with headings

Select the both the tables content and its headers. Then choose Insert, Name, Create from the menu.

You'll see the dialog screen of fig. 3 popup. By selecting the options shown in that screenshot, seven names are defined in one go:

Fig. 3: Dialog screen Create Names

Name	Refers to:	Or
Apples	=Sheet1!$B$2:$B$4	The three cells below Apples
Pears	=Sheet1!$C$2:$C$4	The three cells below Pears
Bananas	=Sheet1!$D$2:$D$4	The three cells below Bananas
Europe	=Sheet1!$B$2:$D$2	The three cells to the right of Europe
Asia	=Sheet1!$B$3:$D$3	The three cells to the right of Asia
America	=Sheet1!$B$4:$D$4	The three cells to the right of America
TurnOver	=Sheet1!$B$2:$D$4	All cells in the table, except the headers

The third way to define a name through the user interface is by selecting Insert, Name, Define from the Menu. See fig. 4. This method will be used throughout the rest of this article.

Fig. 4: Dialog screen Define Name