The Complete Guide To Named Ranges In Excel [Clear & Simple] (2023)

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 courses.

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. 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.

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

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).

3. The New Name Dialog Box will appear.

4. Using the New Name Dialog Box:

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

• • 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.

• • 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.

1. Select cell B2.

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

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

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.

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).

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.

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.

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.

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).

2. The Name Manager Dialog Box should appear.

3. Click on the New button…

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.

5. Click OK.

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

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.

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)

2. Press Enter.

3. The second most expensive value is returned.

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.

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

See this article for more details on

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.

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

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.

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.

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.

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.

3. In cell C3 type the following formula

=B3*(1-Discount_For_Weekend_Sale)

and press Enter.

4. Drag the formula 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.

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.

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 .

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!