We identify each cell in the spreadsheet by a combination of its column letter and row number. A range of cells is defined using the upper Left cell and the lower Right cell. We call this A1 notation. But writing a complex formula using A1 notation is a bit difficult to understand and maintain. You can replace this A1 notation with Excel named range to make it easier to read and maintain.
In this post, I will show you can use the named range in Microsoft Excel.
Note: The examples used here are created with Microsoft Excel 365.
Table of Contents
What is named range in simple terms?
It is a name that you assigned to a cell or range of cells in Spreadsheets.
We can use them inside formulas whenever we want to refer to that cell or range of cells.
For example,
Without named range
=SUM(A2:A10)
With named range
=SUM(LineTotal)
In this example, we have given the name LineTotal to the range A2:A10.
The formula that uses the named range is easier to read and understand. This is more useful when you are writing complex formulas and when you need to refer to the same cell or range repeatedly.
Benefits of using named range in Excel
- Formula written with a named range is easy to understand and maintain.
- Names are easy to memorize than cell references.
- No need to go back and select the cell or cell range every time you need to refer them. Just type the name inside the formula.
- Easy absolute reference with a named range
- When you type the first few letters of the name, the matching names are listed in the suggested formula dropdown list below the cell. So, you don’t need to memorize the exact name.
- Another person who read the formula can easily understand what it does
How to create a named range in Excel
Mainly there are three methods to create named ranges in Microsoft Excel.
There are a few rules that you should follow when defining names.
Rules for creating names
- The name should start with a letter or underscore ( _ ).
- Names cannot contain space
- Names can be up to 255 characters in length
- You cannot use cell references as names. For example, you cannot use “A1” or “AB1” as names.
- Names are case insensitive. For example, “LineTotal” and “line total” are treated as the same names.
Scope
The scope property lets you decide whether the “name” available throughout the workbook or within a specific sheet.
Workbook-level scope
By default, Excel applies workbook level scope to named ranges. With a workbook-level scope, you can use the “name” in any worksheet.
Sheet-level scope
The named range set to a particular sheet’s scope can be used within that specific sheet only. So you can define a named range with the same name in multiple sheets with sheet-level scope.
Note: Prefix the sheet name as below to access the named range with sheet-level scope from another sheet,
=Sheet1!LineTotalMethod 1: Use the name box to create named ranges
The use of the name box is the easiest method to create a named range in Excel.
- Select the Cell or the Range of Cells to be named
- Type the range name in the Name Box
- Press Enter
Note: The named range created from the selection has a workbook-level scope. You cannot edit it. You can delete and create another with the name manager if you want to change the scope.
Method 2: Using the “Define name” option to create a named range
This method has a few additional options for creating named ranges. You can choose the scope of the name and add comments describing what it represents.
- Select the cell or range of cells.
- Go to the Formulas tab > Defined Names group and click the Define Name button. This will open the New Name dialog box.
- In the New Name dialog box.
- Name: Type the name of your range
- Scope: Select the scope of your range (Entire workbook or specific sheet)
- Comment (optional): You can add any comment to describe the named range if required.
- Refers to: This includes the cell reference. It is automatically generated based on your selection in step 1. You can adjust the cell reference if necessary.
- Click OK to save the new name.
Method 3: Use the “Create from selection” option to automatically generate named ranges
This method lets you define names based on the selected cell range. It generates names to the ranges based on the row/ column you selected as range labels. When you have to name multiple cells or ranges, you can use this method to save time.
Note: The named range created from the selection has a workbook-level scope.
- Select the range of cells you need to name, including the row or column labels.
- Go to Formulas > Create from Selection. (you can also use the shortcut Ctrl+Shift+F3)
- In the Create Names from Selection dialog box, choose the location that contains the labels by selecting the Top row, Left column, Bottom row, or Right column check boxes.
- Click OK
Method 4: Use “Name Manager” to create named ranges in Excel
The Name Manager dialog box consists of all the defined names in a workbook. You can add, edit, delete, sort, and filter named ranges in the Name Manager dialog box.
To define a new named range using Name Manager,
- Go to Formulas > Name Manager ( in the Defined Names group) or click Ctrl+F3
- In the Name Manager dialog box, click the New… button
- Use the New Name dialog box as explained in Method 2 to define named ranges.
Manage names in Excel workbook
You can view the named ranges defined previously in Name Manager. With name manager, you can add, edit, delete, sort, and filter named ranges. It also includes the names of the tables you created within the workbook.
How to edit Excel Named Ranges?
- Go to Formulas > Name Manager
- Find and select the name you want to edit
- Click Edit button
- It will open the edit name dialog box with the following options
- Name: Name of the range
- Scope: Scope of the range. This option is disabled. Excel does not allow you to change the scope of the names you already defined.
- Comment: You can add some comments to describe the named range.
- Refers to: Here you can update the range of cells that refers to the named range.
- Click OK to apply your changes.
How to delete Named Range in Excel?
To delete named range in Excel,
- Go to Formulas > Name Manager
- Select the name you want to delete. (
- If you want to delete multiple names, select the names while pressing the Ctrl key.
- If you want to delete all the names, select the first one then select the last one while pressing the Shift key.
- Click delete button
- Click OK.
How to sort and filter Named Ranges in Excel?
You can sort and filter named ranges in the name manager dialog box (Formulas > Name Manager).
Click on the table headings inside the Name Manager dialog box to sort named ranges. You can sort them by Name, Value, Refers to, Scope, and Comment.
Use the filter button at the top left to filter the named ranges. You can filter names based on scope, errors, table or defined names.
Excel Named Range Keyboard Shortcuts
The following keyboard shortcuts let you save some time when working with Excel named range.
Ctrl + F3 : Open the Name Manager dialog box.
Ctrl + Shift + F3 : Open Create Names from the Selection dialog box
How to find named range location in Excel
Sometimes you might need to locate the range of cells represented by the named range. You can use the following two methods to find the named range location in an Excel workbook.
Use the named box to find and select Excel named ranges
- Click the dropdown arrow of the named box
- Click on the named range you want to locate/ find. Then it will activate the relevant sheet, and select the range of cells that are represented by the name, and bring it to the viewport.
Use the “Go-To” option to find and select Excel named ranges
The “Go-To” option lets you jump right to a specific page, line, footnote, comment, or other places in your document. The “Go-To” dialog box lists all the named ranges of your workbook. To find the reference location of a specific named range,
- Go to Home > Find & Select > Go To… (Alternatively, you can use the keyboard shortcut Ctrl+G to save time)
- In the Go To dialog box, double-click on the name of the named range you want to locate. Then, it will jump to the relevant range and shows up in the viewport.
Excel dynamic named range
The method discussed above is about creating static named ranges in Excel. That means the values you add outside of the defined range do not include in the named range. You have to manually update the cell reference to include the newly added data.
However, manual update is not effective when you update the range regularly.
In that case, you can create a dynamic named range in Excel as described below.
Create Dynamic Named Range using OFFSET() function
Here we are going to use the OFFSET function instead of direct cell references for the named range.
The OFFSET function returns a reference to a range that is a specified number of rows and columns from a cell or the range of cells. The function has five arguments as below.
OFFSET(reference, rows, cols, [height], [width])We can use the first three arguments to define the initial range in our case. Then the other two optional arguments ([height] and [width]) can be used to provide dynamic input to the function.
Example:
Consider the following table. Let’s say you continue to add more products to the end of the table, and you need to create a dynamic named range for the Amount column to calculate the total amount.
To create the dynamic named range,
- Go to Formulas > Define Name
- For the “Name” field, type a name for your named range. Here, I type “Amount”
- Replace the content in the “Refers to” field with the following formula. Then click OK.
=OFFSET(Sheet1!$F$6,0,0,COUNTA(Sheet1!$F$6:$F$100),1)In the above formula,
- The first argument
Sheet1!$F$6is the first cell of the range. - The second and third arguments are set to zero,
- Fourth argument, COUNTA(Sheet1!$F$6:$F$100) counts the non-blank cells adjacent to the first cell. “Sheet1” is the name of the sheet which named range defined. It counts cells up to the 100th row, and you may change it according to your requirement.
- The fifth argument is set to 1
In the above formula, argument 4, the COUNTA function changes its value dynamically as you add or remove values in the “Amount” column. Accordingly, the OFFSET function expands the range it returns. As a result, the named range also expanded dynamically.
How to use Excel named ranges in the formula
To use the named range in the formula,
- Select a cell and start writing the formula (E.g. =SUM…)
- Place the cursor where you want to insert the name and start typing the first few letters of the named range.
- Then, the list of names and functions that match the letters you type will appear in a dropdown.
- Select the name and hit the Tab key (you do not need to type the entire name, use the Tab key to enter the selected name or function).
You can also go to Formulas > Us in Formula and select the name while typing the formula to enter the named range.
Wrapping Up
In Microsoft Excel, you can give a name to a cell or a range of cells. You can call this name whenever you want to use this range in a formula. Formulas written with named ranges are easy to read and maintain.
In this post, we discussed several benefits of using Excel named ranges and the number of methods that you can create named ranges in Excel. The named ranges can be defined to refer to a static range or a dynamic range. Dynamic ranges are useful when you have to update the data range regularly.
