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!

What has changed?

PerfectXL Compare, for Spreadsheet Comparison
This Excel compare tool is fast, reliable, and easy to use. Compare two Excel files now and see for yourself!
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