How-To

How to Name a Range in Excel

By Adam Davidson

Published June 30, 2023

If you’re constantly referring to a range of cells in Excel, it can save time to give it a name. Learn how to name a range in Excel.

When you’re working on an Excel spreadsheet, do you find yourself constantly referring to a specific set of cells in your formulas? If so, using named ranges may help. Named ranges allow you to refer to specific sets of cells by a name rather than by the locations of those cells.

It can save you time and also make it easier to make sense of your formulas for complex spreadsheets. If you’re ready to start putting names to your cells, then learn how to name a range in Excel below.

What is a Named Range in Excel?

A named range in Excel is a label applied to a single cell or group of cells. For example, if you have a list of sales figures in cells B1:B22, you can rename that specific range as ‘Sales’.

You can create as many named ranges as you wish, as long as each one has a unique name. You would not be able to create two different ranges with the name ‘Sales’ for example.

Why Name a Range in Excel?

There are a few reasons why naming ranges in Excel can be a good idea. Firstly, if your ranges contain large amounts of data, selecting the entire range can become time-consuming. Named ranges allow you to use the name in place of the cell references, making it quick and easy to refer to specific cells in formulas.

If you create dynamic ranges, as explained below, it also means you don’t need to update your formulas when you add additional data to a range. By referring to the range by name, it will always include all of the data in the range, even if you add more.

Named ranges can also be useful for helping you to quickly navigate around large spreadsheets; you can select the named range in your spreadsheet to be taken directly to those cells.

How to Name a Range in Excel

There are several ways that you can name a range in Excel. The method that you choose will depend on whether you’re just naming a single range, or there are multiple ranges that you want to name at once.

How to Name a Range Using the Name Box

One of the quickest and easiest ways to name a range in Excel is to use the Name box. This is because this box is always visible in your spreadsheet and can be accessed without needing to change to a different ribbon.

It’s also a useful way to quickly navigate to specific ranges within your spreadsheet.

To name a range using the Name box:

1. Highlight all of the cells that you want to include in your range.
   
2. Click in the Name box in the top-left corner of your spreadsheet.
   
3. Enter the name you want to use for your range.
   
4. Press Enter and your range is given the name that you entered.
5. To quickly navigate to those cells at any time, click the Arrow at the end of the Name Box.
   
6. Select the name of your range from the list.
   
7. The cells that the name refers to are instantly highlighted. If the named range was not currently in view, you will be taken to the section of the spreadsheet where your named range is found.
   

How to Name a Range Using the Define Name Tool

You can also name a range using the Define Name tool. You can access this tool via a context menu or by using the Formulas ribbon.

To name a range using the Define Name tool:

1. Highlight all of the cells that you want to include in your range.
   
2. Right-click anywhere within your selection.
3. Select Define Name.
   
4. This will open the New Name dialog box.
5. You can also access the same dialog box by clicking on the Formulas menu.
   
6. Click Define Name in the ribbon.
   
7. In the New Name dialog box, you should see your selected cells in the Refers To field.
   
8. Click in the Name field.
   
9. Enter the name that you want for your range.
   
10. Click OK.
    
11. Your range is now named.

How to Name a Range From a Selection

This method is particularly useful if you have a lot of ranges that you want to name. It allows you to name multiple ranges by using their column headers to define their names.

To name a range from a selection:

1. Highlight all of the cells that you want to create named ranges for, including their headers. This method will only work if all of the cells have headers either above the columns or at the start of each row that contain the names you want to use for each range.
   
2. Click the Formulas menu.
   
3. Click the Create From Selection button.
   
4. Check the box that describes where your headers are located. In this example, they are in the top row, so we can check Top Row.
   
5. Click OK.
   
6. You can check that the ranges have been named correctly by clicking the Arrow in the name box.
   
7. You should see your newly created named ranges.
   

How to Create Dynamic Named Ranges

One issue that can be caused when using ranges is that they refer to a set range of cells. If you add any additional data to your spreadsheet, these new values won’t be included in your range, since they’re outside the array of cells you originally selected.

It’s possible to create a dynamic named range, however, which will always include any additional data that you add to a column.

To create a dynamic named range in Excel:

1. Highlight the cells that you want to include.
2. Right-click anywhere within your selection.
3. Select Define Name.
   
4. If your data includes a header, in the Refers To box, enter the following formula, replacing $A$2 with the first cell of your range and the A in $A:$A with the letter of the relevant column.

   =OFFSET($A$2,0,0,COUNTA($A:$A)-1,1)

5. If your data doesn’t include a header, use the following formula instead:

   =OFFSET($A$1,0,0,COUNTA($A:$A),1)

6. Enter a name for your range and click OK.
   
7. The formula you have entered sets your range to all of the cells in the column that contain data. If you add new data to the column, the range will automatically extend to include these cells.

How to Edit Range Names in Excel

If you decide that you want to change the name for a range or delete one entirely, you can do so using the Name Manager.

To edit range names in Name Manager:

1. Click the Formulas menu.
   
2. Click the Name Manager icon.
   
3. Select the named range you want to edit and click Edit.
   
4. Enter a new Name and click OK.
   
5. To delete a name, select the relevant name and click Delete.
   

Saving Time in Microsoft Excel

Knowing how to name a range in Excel can save you a lot of time by allowing you to refer to ranges by name rather than having to reference their cells. It’s also a useful way to be able to navigate around a large spreadsheet.

There are plenty of other ways you can use Excel features to save time when working on your spreadsheets. You can learn how to convert a zero to a dash in Excel to make it easy to spot cells that don’t contain data. You can learn how to find merged cells in Excel if you need to remove them.

If you want to save time by adding the same cells to different sheets, you’ll need to know how to group sheets in Excel, too.