Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

File crashes Excel!!

Save my workbook!
The best tool for salvaging problematic workbooks.

Trainings

Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > English site > Articles > Defined Names > Define
Deze pagina in het Nederlands

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 click the Formulas tab and click "Create from Selection".

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 "Name Manager" from the Formulas tab (keyboard short-cut: control+F3) and clicking the New button. See fig. 4. This method will be used throughout the rest of this article.


Fig. 4: Dialog screen Define Name