Excel named range - how to define and use names in Excel

What does name mean in Excel?

In everyday life names are widely used to refer to people, objects and geographical locations. For example, instead of saying "the city lying at latitude 40.7128° N and longitude 74.0059° W, you simply say "New York City".

Similarly, in Microsoft Excel, you can give a human-readable name to a single cell or a range of cells, and refer to those cells by name rather than by reference.

For instance, to find the total of sales (B2:B10) for a specific item (E1), you can use the following formula:

=SUMIF($A$2:$A$10, $E$1, $B$2:$B$10)

Or, you can give meaningful names to the ranges and individual cells and supply those names to the formula:

=SUMIF(items_list, item, sales)

Looking at the screenshot below, which of the two formulas are easier for you to understand?

Excel name types

In Microsoft Excel, you can create and use two types of names:

Defined name - a name that refers to a single cell, range of cells, constant value, or formula. For example, when you define a name for a range of cells, it's called a named range, or defined range. These names are subject of today's tutorial.

Table name - a name of an Excel table that is created automatically when you insert a table in a worksheet (Ctrl + T). For more information about Excel tables, please see How to make and use a table in Excel.

How to create an Excel named range

Overall, there are 3 ways to define a name in Excel: Name Box, Define Name button, and Excel Name Manager.

Type a name in the Name Box

The Name Box in Excel is fastest way to create a named range:

1. Select a cell or a range of cells that you want to name.

2. Type a name into the Name Box.

3. Press the Enter key.

Voila, a new Excel named range is created!

Create a name by using the Define Name option

Another way to make a named range in Excel is this:

1. Select the cell(s).

2. On the Formulas tab, in the Define Names group, click the Define Name button.

3. In the New Name dialog box, specify three things:

                ->In the Name box, type the range name.

                ->In the Scope dropdown, set the name scope (Workbook by default).

                ->In the Refers to box, check the reference and correct it if needed.

4. Click OK to save the changes and close the dialog box.

Note: By default, Excel creates a name with absolute references. If you'd rather have a relative named range, remove the $ sign from the reference (before you do this, make sure you fully understand how relative names behave in worksheets).

Compared to the previous method, using Define Name in Excel takes a few extra clicks, but it also provides a couple more options such as setting the name's scope and adding a comment that explains something about the name. Additionally, Excel's Define Name feature allows you to create a name for a constant or formula.

Make a named range by using Excel Name Manager

Usually, the Name Manager in Excel is used to work with existing names. However, it can help you build a new name too. Here's how:

1. Go to the Formulas tab > Defined Names group, and click the Name Manager Or, just press Ctrl + F3 (my preferred way).

2. In the top left hand corner of the Name Manager dialog window, click the New… button:

3. This will open the New Name dialog box where you configure a name as demonstrated in the previous section.

Tip. To quickly test the newly created name, select it in the Name Box dropdown list. As soon as you release the mouse, the range on the worksheet will be selected.

Excel dynamic named range

In all previous examples, we have been dealing with static named ranges that always refer to the same cells, meaning you would have to update the range reference manually whenever you want to add new data to the named range.

If you are working with expandable data sets, it stands to reason to create a dynamic named range that accommodates newly added data automatically.

Excel naming rules

When creating a name in Excel, there are a few rules to remember:

1. An Excel name should be under 255 characters long.

2. Excel names cannot contain spaces and most punctuation characters.

3. A name must begin with a letter, underscore (_), or backslash (\). If a name begins with anything else, Excel will throw an error.

4. Excel names are case-insensitive. For example, "Apples", "apples" and "APPLES" will be treated as the same name.

5. You cannot name ranges like cell references. That is, you can't give the name "A1" or "AA1" to a range.

6. You can use a single letter to name a range like "a", "b", "D", etc. except for the letters "r" "R", "c", and "C" (these characters are used as shortcuts for selecting a row or column for the currently selected cell when you type them in the NameBox).

Excel name scope

In terms of Excel names, scope is the location, or level, within which the name is recognized. It can be either:

1. Specific worksheet - the local worksheet level

2. Workbook - the global workbook level

Worksheet level names

A worksheet-level name is recognized within the worksheet where it is located. For example, if you create a named range and set its scope to Sheet1, it will be recognized only in Sheet1.

To be able to use a worksheet-level name in another worksheet, you must prefix the worksheet's name followed by the exclamation point (!), like this:

Sheet1!items_list

To reference a worksheet-level name in another workbook, you should also include the workbook name enclosed in square brackets:

[Sales.xlsx]Sheet1!items_list

If either the sheet name or workbook name contains spaces, they should be enclosed in single quotation marks:

'[Sales 2017.xlsx]Sheet1'!items_list

Workbook level names

A workbook-level name is recognized within the entire workbook, and you can refer to it simply by name from any sheet in the same workbook.

A use to a workbook-level name in another workbook, precede the name with the workbook name (including the extension) followed by the exclamation point:

Book1.xlsx!items_list

Scope precedence

A defined name must be unique within its scope. You can use the same name in different scopes, but this might create a name conflict. To prevent this from happening, by default, the worksheet level takes precedence over the workbook level.

If there are a few identically named ranges with different scopes, and you want to use the workbook level name, prefix the name with the workbook name as if you were referring to a name in another workbook, e.g.: Book1.xlsx!data. This way, the name conflict can be overridden for all worksheets except for the first sheet, which always uses the local worksheet level name.

Excel Name Manager - quick way to edit, delete and filter names

As its name suggests, the Excel Name Manager is specially designed to manage names: change, filter, or delete existing names as well as create new ones.

There are way to get to the Name Manager in Excel:

On the Formulas tab, in the Define Names group, click the Name Manager

How to edit named range in Excel

To change an existing Excel name, open the Name Manager, select the name, and click the Edit… button. This will open the Edit Name dialog box where you can change the name and reference. The scope of the name cannot be changed.

Tip. To edit a name reference, you do not need to open the Edit Name dialog box. Just select the name of interest in the Excel Name Manager, and type a new reference directly in the Refers to box, or click the button at the right and select the desired range on the sheet. After you click the Close button, Excel will ask if you want to save the changes, and you click Yes.

How to filter names in Excel

If you have a lot of names in a certain workbook, click the Filter button at the top right corner of the Excel Name Manager window to view only the names relevant at a given time. The following filters are available:

->Names scoped to worksheet or workbook

->Names with or without errors

->Defined names or table names

How to delete named range in Excel

To delete a named range, select it in the Name Manager and click the Delete button at the top.

To delete several names, click the first name, then press the Ctrl key and hold it while clicking other names you want to remove. Then click the Delete button, and all selected names will be deleted in one go.

To delete all defined names in a workbook, select the first name in the list, press and hold the Shift key, and then click the last name. Release the Shift key and click Delete.

Benefits of using names in Excel

So far in this tutorial, we have been focusing mostly on how-to things that cover different aspects of creating and using named ranges in Excel. But you may be curious to know what is so special about Excel names that makes them worth the effort? The top five advantages of using defined names in Excel follow below.

1. Excel names make formulas easier to make and read

You don't have to type complex references or go back and forth selecting ranges on the sheet. Just start typing the name you want to use in the formula, and Excel will show a list of matching names for you to choose from. Double click the desired name, and Excel will insert it in the formula straight away:

2. Excel names allow creating expandable formulas

By using dynamic named ranges, you can create a "dynamic" formula that automatically includes new data in calculations without you having to update every reference manually.

Excel named range - tips and tricks

Now that you know the basics of creating and using names in Excel, let me share a few more tips that may prove helpful in your work.

How to get a list of all names in the workbook

To get a more tangible list of all names in a current workbook, do the following:

Select the topmost cell of the range where you want the names to appear.

Go to the Formulas tab > Define Names group, click Use in Formulas, and then click Paste Names… Or, simply press the F3 key.

In the Paste Names dialog box, click Paste List

This will insert all Excel names along with their references in the current worksheet, beginning in the selected cell.