Creating and Using Named Ranges in Excel

A named range is a descriptive name given to a single cell or range of cells.

You can use an Excel named range in a formula, to create a drop-down list, or in your conditional formatting.

Working with named ranges, is covered extensively in our intermediate and advanced Excel training.

 

Reasons to Use Named Ranges

The advantages of using named ranges include:

  • You don’t have to physically select the cell range each time you want to use it.

  • You don’t have to remember the cell reference.

  • Named ranges can be managed easily by using Name Manager.

  • Navigation to your named range is much easier through the Name Box feature.

 

The 5 Rules For Naming Ranges In Excel

    1. A name cannot be more than 255 characters long.

    2. You cannot use spaces in your names.

    3. The first letter can only be a letter, an underscore, or a backslash.

    4. You cannot use actual cell addresses as named ranges. For example, you cannot call your named range “A7”.

    5. You can use symbols such as forward slashes, underscores and dots in names, but you cannot use other symbols.

Tip: Try to give your range a name that describes the data it contains.

 

Four Different Ways To Create A Named Range

 

1. Create a Named Range Using Define Name

Let’s see how to name a range in Excel. In the example below, we have a data range in Excel that shows common beverages.

 

Screenshot showing the cell range B1:B5 highlighted which contains the names of the beverages.

 

1. Select the cell range B1:B5, which contains the data.

 

Screenshot showing cell range B1:B5 selected.

 

2. With the range selected, go to the Formulas Tab on the ribbon (Step 1 in the image) and on the Defined Names group, choose Define Name (Step 2 in the image).

 

Screenshot showing the Define Name option in the Defined Names group on the Formulas Tab.

 

3. The New Name Dialog Box will appear.

 

Screenshot showing the New Name Dialog Box.

 

4. Using the New Name Dialog Box:

    • Type the name which you’d like your range to be. In this case “Beverages”.

 

Screenshot showing the name Beverages typed into the Name: section of the New Name Dialog Box.

 

    • The Scope refers to where the named range can be applied. The default setting is the workbook which means that you can use the named range anywhere in the workbook. In this case we will leave the default option.

If the scope is set to worksheet, then the name will only be recognised in the worksheet.

 

Screenshot showing the scope options which are either Workbook or Sheet1. In this case the default is selected.

    • You can add a comment to provide more information about the range.

    • In the Refers to: section check that the range of interest is selected. By default, Excel creates an absolute reference.

If you’d like to learn about the differences between absolute, relative and mixed references, read this post.

 

5. Click OK.

 

2. Create A Named Range By Using The Name Box

You can create named ranges in Excel by using the Name Box.

In the example below, we have cell B2, that will contain the age of the user.

 

Screenshot showing two cells. Cell B2 will be named.

 

1. Select cell B2.

 

Screenshot showing Cell B2 selected.

 

2. Using the Name Box, type Age and press Enter.

 

Screenshot showing Age being typed into the Name Box.

 

3. If you click on the Name Box drop-down arrow, you will see the named range.

 

Screenshot showing the newly created named range Age.

 

4. If you click on the named range then cell B2 will be selected.

 

3. Create A Named Range By Using Create From Selection

You can use Excel to name a range by using the Create from Selection option. This method works well when you have row or column labels.

In the example, we have some of the typical departments in an organisation shown.

 

Screenshot showing the typical departments in an organisation.

 

1. Select range D4:D9. Ensure that you include the label. Go to the Formulas Tab (Step 1 in the image) on the ribbon and on the Defined Names group, choose Create from Selection (Step 2 in the image).

 

Screenshot showing the Create from Selection option in the Defined Names group on the Formulas Tab.

 

2. The Create Names from Selection Dialog Box will appear. Ensure that the Top row option is checked since our heading is in the top row.

 

Screenshot showing the Create Names from Selection Dialog Box with the Top row checked.

 

3. Click OK.

 

4. This automatically creates a name for the range using the heading in cell D4. Now if you click on Departments in the Name Box, cells D5:D9 will be selected.

 

Screenshot showing Departments in the Name Box.

 

Screenshot showing the range D5:D9 selected.

 

4. Create A Named Range Using Name Manager

You can use the Name Manager to create named ranges in Excel.

In the example below, we have a list showing an item of furniture and its corresponding price. We would like to name the range containing the prices.

 

Screenshot showing the range that we would like to name.

 

1. Go to the Formulas Tab (Step 1 in the image) on the ribbon and on the Defined Names group, choose Name Manager (Step 2 in the image).

 

Screenshot showing the Name Manager option in the Defined Names group in the Formulas Tab.

 

2. The Name Manager Dialog Box should appear.

 

Screenshot showing the Name Manager Dialog Box.

 

3. Click on the New button…

 

Screenshot showing the New... Button in the Names Manager Dialog Box.

 

The New Name Dialog Box should appear.

 

4. Enter a name for the range which in this case is Prices. Select the range E4:E7 in the Refers to: section.

 

Screenshot showing the New Name Dialog Box with the name Prices entered and the cell range E4:E7 selected.

 

5. Click OK.

 

6. You should now have a named range called Prices.

 

Screenshot showing the Named Range Prices in the Name Manager.

 

7. Click Close.

 

Using Named Ranges in Your Formulas

An Excel range name is very useful in a formula. To illustrate this, let’s use our example above, where we named our Prices range.

We want to find the price of the second most expensive furniture item in cell E10.

Screenshot showing Prices range and cell E10.

 

 

To do this we are going to use the LARGE Function in combination with our named range.

 

1. Select cell E10 and type the following formula using our named range Prices. As you type Prices, Excel will show a drop down displaying the matched name.

=LARGE(Prices, 2)

 

Screenshot showing Excel displaying the Named Range Prices as the LARGE formula is being typed.

 

2. Press Enter.

3. The second most expensive value is returned.

 

Screenshot showing the formula which contains the Named Range Prices returning the second most expensive item.

 

Tip: Alternatively, you can also select the cell of interest.

Then enter the equals sign and LARGE. After opening the parenthesis go to the Formulas Tab  (Step  1 in the image) on the ribbon and on the Defined Names group, click on Use In Formula (Step 2 in the image) and select the name of the range.

 

Screenshot showing the Use in Formula option in the Defined Names group in the Formulas Tab.

 

Complete the rest of the formula and press Enter to get the second most expensive item.

See this article for more details on Excel Formulas & Functions

 

Editing A Named Range

You can edit or change a named range in Excel by using the Name Manager.

We would like to edit the Prices named range, which we created in the example above.

To edit an Excel named range do the following:

1. Go to the Formulas Tab on the ribbon and on the Defined Names group, choose Name Manager.

2. The Name Manager Dialog Box will appear which should show you all the named ranges you have created and any tables that you may have in the workbook.

 

Screenshot showing the Name Manager Dialog Box and all the named ranges that have been created.

 

3. Select the Prices named range and click on the Edit… button.

 

Screenshot showing the Prices Named Range selected and the Edit... button highlighted.

 

4. The Edit Name Dialog Box should appear. We would like to change the name of our range to Prices_of_Items, so type this into the Name section.

 

Screenshot showing the Edit Name Dialog Box with the new name entered.

 

5. Click Ok.

6. Close Name Manager.

 

Deleting A Named Range

You can delete a named range by using Name Manager.

1. Go to the Formulas Tab on the ribbon and on the Defined Names group, choose Name Manager.

2. Select the named range that you would like to delete, in this case it is Departments and click on the Delete button.

 

Screenshot showing Name Manager with Departments selected and the Delete button highlighted.

 

3. Click Ok to confirm and this named range should now be deleted.

 

Creating A Named Constant

Let’s say we have a list of products and their corresponding prices as shown in the example below.

Screenshot showing our source data range.

 

There is a half-price sale happening over the weekend and all the prices should be discounted by half.

So, we would like to have a calculated column that contains the discounted price of each product.

1. Since we are applying the same discount which is 0.5. We can use this as a named constant.

2. Press Ctrl-F3 to open Name Manager. Click New…

The New Name Dialog Box should appear.

Type the name of the constant which in this case is Discount_For_Weekend_Sale.

In the Refers to: section type =0.5 as shown below. Click Ok and then Close.

 

Screenshot showing how to name a constant using the New Name Dialog Box.

 

3. In cell C3 type the following formula

=B3*(1-Discount_For_Weekend_Sale)

and press Enter.

Screenshot showing the formula which is using the named constant.

 

4. Drag the formula down the column.

 

Screenshot showing the formula being dragged down the column.

 

Creating a Dynamic Named Range

A dynamic range refers to a range that updates automatically as new values are added to the source data.

Let’s say we have a worksheet containing the names of potential contestants in column A. We want to create a range, and have it update automatically as we receive applications.

The source data is shown below.

 

Screenshot showing the source data.

 

1. To create a dynamic named range. Press Ctrl-F3 to open the Name Manager.

2. Click the New button…

3. Name the range Potential_Contestants.

In the Refers to: section enter the following formula:

=$A$1:INDEX($A:$A, COUNTA($A:$A) )

4. Click Ok and Close.

 

Screenshot showing the INDEX function being used to create the dynamic range in the Refers to: section of the New Name Dialog Box.

 

The INDEX Function is being used to return the reference of the last cell that has data in column A. Since the starting point was given as $A$1 the entire formula will return the range starting from A1 to the last used cell.

To learn about how to use the INDEX Function with other lookup examples, please read our Excel Formulas: VLookup Vs Index & Match article.

 

Useful Shortcuts

Ctrl+F3 opens the Name Manager Dialog Box.

Ctrl+Shift+F3 opens the Create Name from Selection Dialog Box.

 

To learn more about other useful shortcuts read our post on Microsoft Office Shortcuts.

Conclusion

You can see the benefits of using an Excel named range in your workbooks. You can define a range in Excel in multiple ways.

Named ranges in Excel make your formulas easier to understand. You can name Excel lists to simplify your workbooks.

If you want more top tips for Excel, read our Ultimate Guide to Highlighting Duplicates in Excel.

Special thank you to Taryn Nefdt for collaborating on this article!

Special thank you to Taryn Nefdt for collaborating on this article!

About Ben Richardson

Ben is a director of Acuity Training. He writes about SQL, Power BI and Excel on a number of industry sites including SQLCentral, SQLshack and codingsight.