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

How would your data look if it had clear written descriptions instead of general titles?

It would be easier to present, and for others to work with.

Named ranges are one of the most unutilised features of Excel.

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

Many people on our Excel courses even say they have never heard of named ranges!

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 references.
Named ranges can be managed easily by using Name Manager.
Navigation to named ranges is much easier through the Name Box feature.

3 Ways To Create Named Ranges

1. Define Name

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 Formulas 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 box will appear.

4. Using the New Name box:

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

The Scope refers to where the named ranges 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 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 Formulas (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 window should appear.

3. Click on the New button…

The New Name window 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 ranges.

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 or cell references of interest.

Then enter the equals sign and LARGE. After opening the parenthesis go to Formulas (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 Excel Formulas & Functions

Editing A Named Range

You can edit or change named ranges 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 named ranges do the following:

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

2. The Name Manage window 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 window 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 the window.

Deleting A Named Range

You can delete named ranges 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 Window 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 Excel Formulas: VLookup Vs Index & Match article.

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.

Useful Shortcuts

Ctrl+F3 opens the Name Manager’s Window.

Ctrl+Shift+F3 opens the Create Name from Selection Window.

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!