Creating and Using Named Ranges in Excel

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.

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

Screenshot showing the New Name Dialog Box.

4. Using the New Name 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 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.

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 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 Formulas (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 window should appear.

Screenshot showing the Name Managers Dialog Box.

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.

Screenshot showing the New Name window 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 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)

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

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

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.


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.

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

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.

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

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

3. In cell C3 type the following formula


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.

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.


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!

About Ben Richardson

Ben is a director of Acuity Training which he has been running for over 10 years.

He is a Natural Sciences graduate from the University of Cambridge and a qualified accountant with the ICAEW.

He previously worked as a venture capitalist and banker and so had extensive experience with Excel from building financial models before moving to learn SQL, Microsoft Power BI and other technologies more recently.