How to Create Named Range in Excel: 4 Suitable Methods

Method 1 – Create Named Range Using Name Box

Create a name for the Annual Salary column using the Name Box.

Select the cell range F6:F15 >> go to Name Box >> type “Annual_Salary” and press Enter.

Resize Name Box:

In Newer Excel versions, you can resize the name box if it seems small. In the previous version of Excel, this option was not available. Name boxes used to be only of a definite size.

The cursor to the three vertical dots beside the Name box.
The mouse pointer will change to a two-sided arrow.
Drag it to the right or left to increase or decrease the size of the Name Box.

Method 2 – Using Define Name Option

Select the cell range E6:E15 >> go to the Formulas tab >> from the Defined Names group, select Define Name.

A dialog box named New Name will appear.

Type “Designation” in the Name box >> keep the Scope as Workbook.
Add any comment about the name created in the Comment box. We did not add any comments, so we kept it blank.
In the Refers to box, you can see the reference to the selected cell range >> press OK.

Note

After creating a named range, the Scope of the named range cannot be changed. If you want to change the scope, you have to delete the named range and change the scope while defining the name.

How to Change Scope of Named in Excel

Scope determines where named ranges can be used. There are two levels of scope in Excel.

Workbook scope: any sheet of any workbook.

Sheet scope: only within that sheet.

We will change the Scope named range “Designation” using the Define Name option.

But You Can’t Change Scope After Creating a Named Range.

We will show you that:

Select Name Manager from the Formulas tab.

Select “Designation” named range >> click the Edit option.

You can see that the Scope box is grayed out in the Edit Name box. You cannot avail of the option to change the scope.

Since we cannot change the scope after defining the name, we will delete this defined name and define a new named range with a different scope.

Open the Name Manager >> select the “Designation” name, and click the Delete option >> press OK.

Define a name with a different scope,

Select the cell ranges E6:E15 and click Formulas >> Define Name.
In the New Name box, type “Designation”.
Click on the drop-down of the Scope box, and select the Sheet Name from the list.
In the Refers to box, you can see the selected cell range; recheck that and press OK.

Method 3 – Using Name Manager

Click the Formulas tab >> Name Manager option.

In the Name Manager dialog box, click New.

Type a name in the Name box. We typed Department.
Click the upside arrow beside Refers to box.

Select the cell range. We selected D6:D15, the cell range of the department column.
Click on the downward arrow in the New Name- Refers to dialog box.

In the New Name dialog box, you can see the cell reference of the selected range. Press OK.

The Name Manager box will show up, viewing the created named range “Department”. Press Close there.

Method 4 – Using Create Selection Option

Select the range C5:C15, where C5 contains the column header (Full Name).
Go to the Formulas tab.
Click on the Create from Selection option from the Defined Names group.

A dialog box named Create Names from Selection will appear.
Select the Top row from the options and press OK.

The named range will be created. Since we selected the Top Row option, the range’s name will be Full Name according to the dataset. You can check it in the Name Manager.

The created named range has a workbook-level scope. The named range you create in this way is valid for all other named ranges in the workbook.

How to Create Dynamic Named Range in Excel: 2 Useful Methods

Method 1 – Use Excel Tables to Create a Dynamic Named Range

Select the cell range B5:F15.
Click the Insert tab >> Table option from the Tables group.

A box named Create Table will appear, where you will see the reference of the selected cell.
Check the box My table has headers as the selected range has headers.
Press OK.

Check if it has become dynamic or not. We typed a new ID under the last ID entry, and the table expanded automatically.

Method 2 – Apply Formula to Create Dynamic Range

1. One-Dimensional Dynamic Named Range

From our dataset, we will create a one-dimensional dynamic named range for the Full Name column.

Click the Formulas tab > Define Name.

In the New Name box, type Employee_Name.
In the Refers to box, enter the following formula and press OK.

=$C$6:INDEX($C$6:$C$99,COUNTIF($C$6:$C$99,"<>"&""))

Select any cell and type the named range, and you will see the contents of the dynamic named range.

=Employee_Name

We entered a new name under the Full Name column. You can see the name automatically added in the dynamic named range below.

2. Two-Dimensional Dynamic Named Range

Go to Formulas > Define Name.

In the New Name box, type ID_Name.
Enter the following formula in the Refers to the box.

=OFFSET($B$6,,,COUNTA($B$6:$B$99),2)

=$B$6:INDIRECT("C"&COUNTA($C$6:$C$99)-2+ROW($C$6))

Press OK.

In cell C17, type the named range we created just now

=ID_Name

And you can see the two-dimensional dynamic named range visible there.

We entered a new ID and name under the Employee ID and Full Name column. The name is automatically added in the dynamic named range below.

How to Find Named Ranges in Excel

Go to the Home tab >> expand Find & Select option >> Go To option.

A box named Go To, with a list of named ranges, will appear.
Select Employee_ID named range >> press OK.

You will see the named range selected in your dataset.

What Are the Keyboard Shortcuts for Excel Named Range?

There are some keyboard shortcuts for creating named ranges in Excel.

Ctrl + Shift + F3: To avail Create from selection option.
Ctrl + F3: To open Name Manager in Excel.
F3: To get the list of all Excel names created in a workbook.

How to Edit, Filter and Delete Named Ranges in Excel

Method 1 – To Edit Named Ranges in Excel

Using the Name Manager, we can edit named ranges in Excel. Here, we want to edit the named range “Employee_ID” which we have already created.

Select the Formulas tab > Name Manager.

In the Name Manager dialog box, select the Employee_ID named range.
Press the Edit button.

A box named Edit Name will pop up.
You can change the name of the named range in the Name field.
You can change the cell references in the Refers to box. You can either type the cell reference or click the upside arrow and select the cell range directly from your dataset.
Press OK to save the changes.

Method 2 – To Filter Named Ranges in Excel

Using Name Manager, you can also filter the names. There are different filtering options, such as:

– Clear Filter
– Names Scoped to Worksheet
– Names Scoped to Workbook
– Names with Errors
– Names without Errors
– Defined Names
– Table Names

We will filter table names from the name list.

Open the Name Manager from the Formulas tab.
Click on the Filter option.
Choose Table Names to filter out the tables.

Name Manager will show the table name that we created while demonstrating the way of creating a dynamic named range.

Note

You can withdraw all filters by clicking on the Clear Filter option.

Method 3 – To Delete Named Ranges in Excel

In this section, we will delete the named range “Count Employees” using Name Manager.

Open Name Manager.
Select the name “Count Employees”.
Press the Delete button.

A warning message will appear. Press OK to delete the named range.

How to Get List of All Named Ranges in Workbook

We will paste all the named ranges listed in our dataset.

Select cell B18.
Go to the Formulas tab >> Use in Formula >> Paste Names.

Paste Name box with the name list will be visible.
Click the Paste List option to get the list.

You will see the named ranges with their data references in the selected cell.

Note

You can select any name from the list and paste only that named range inside a formula using this method.

How to Display Named Range Contents in Excel

We will display the contents of the named range of the Employee_Name.

Select cell C18 >> type equal to (=) and the named range.
Excel will show the suggestion for that name.
Select the name, press Tab, and Enter.

You will see the contents of that named range pasted in the cells.

How to Ignore Blank Cells in Excel Named Range

We have a dataset of Salaries from 2022 and 2023 for some employees. Now, we want to calculate the increase or decrease in their salary. But there are some blank cells in the column for 2023 salaries. We need to ignore blank cells in Excel Named range because it can create incorrect results.

Create a named range for Salary 2022 and Salary 2023 columns.

Select the cell range of the “Salary (2022)” column.
Type “Salary_2022” in the Name Box and press Enter.

Click the image to get a better view

Name the column “Salary (2023)”.

Click the image to get a better view

Use these two named ranges to calculate salary increases or decreases, ignoring the blank cells.

Insert the following formula in cell G6 and hit Enter.

=IF(ISBLANK(Salary_2023),"",((Salary_2023-Salary_2022)/Salary_2022))

Click the image to get a better view

The ISBLANK function checks if there is any blank in the Salary_2023 named range, and the IF function calculates the salary increase or decrease for the cells that are not blank.

How to Use Dynamic Named Range in Excel Chart

Excel charts visualize data but don’t autofit with new data. To make dynamic charts, use dynamic named ranges. Create two dynamic named ranges “ID” and “Salary” and then create an Excel chart with them.

Click on Formulas > Define Name.

Type “ID” and insert the formula for creating a dynamic named range.

=OFFSET($B$6,,,COUNTA($B$6:$B$99),1)

We created another dynamic named range, “Salary” using the following formula.

=OFFSET($F$6,,,COUNTA($F$6:$F$99),1)

Now that the dynamic named ranges are created, we will insert a chart in the Excel sheet.

Go to Insert tab.
From the Charts section, select the 2D Column chart.

A blank chart will be inserted in the spreadsheet.
Right-click on the chart, and click on the Select Data option from the context menu.

The Select Data Source named box will appear.
Click on the Add button under the Legend Entries (Series) option.

In the Edit Series box, you will see the Series Name box.
As a series, we want to enter Salary values, so we entered the cell reference F5 which contains the name Annual_Salary.
Enter the dynamic named range “Salary” in the Series Values option and press OK.

Pressing OK will take you back to the Select Data Source box. Now, we will enter employee IDs as Horizontal Axis Labels.
Select the Edit option under the Horizontal (Category) Axis Labels option.

In the Axis Labels box, enter the dynamic named range, ID, and press OK.

In the Select Data Source box, the selected dynamic named range is showing.
Press OK.

You will see that the chart shows the values in the dynamic named ranges.

Change the color of the columns (this is optional).

For that, double-click on any column, and on the right side of the sheet, you will see the Format Data Series option.
Select Series Options, and click on the Fill & Line option.
From the Color option, choose any color of your choice.

Click the image to get a better view

The column’s color has changed, and the chart has become dynamic using the named range. We added a new row of data, and it is visible in the chart.

Tips and Tricks for Excel Named Range

We will show you some tips and tricks with named ranges.

1. Applying Excel Names in Already Existing Formulas

Excel can replace a cell reference with a defined named range if its reference matches.

In the image below, we showed a formula for calculating the “Total Salary of Sales Department”.

=SUMIF(D6:D15,“Sales”,F6:F15)

The formula consists of cell references that we have already named. We can replace these cell references using the Apply Names feature of Excel.

Select cell E17 >> go to the Formulas tab >> click the Define Name option and select Apply Names.

Apply Names box will appear.
Dept and Salary_Annual named ranges match with the cell references D6:D15 and F6:F15. They got selected automatically.
Keep the checkboxes Ignore Relative/Absolute and Use row and column names checked and press OK.

The cell references were replaced with the named ranges.

Note

You can use this trick to apply named ranges to multiple formulas at once.

Method 2 – Changing Absolute Excel Names to Relative Excel Names

You can define a named range without absolute cell reference, which will change according to the cell where you used the named range.

We added department names in cell range B18:B21 and added a column where we will apply the formula for calculating total salaries.

Select cell C18 and go to the Formulas tab > Define Name.
In the New Name box, type Criteria in the name box.
Type C18 as the relative cell reference in the Refers to the box.
Press OK.

Insert the following formula in cell C18.

=SUMIF(Department,Criteria,Annual_Salary)

Here, in the image you can see that the Department named range defines D6:D15 cell range, Annual_Salary defines F6:F15 cell range. Though we defined cell C18 as Criteria, it is showing B18 as Criteria. This is the beauty of relative Excel names. It changes cell reference with its relative position.

Press Enter to see the total salary of the Executive department.
Drag the Fill Handle to copy down the formula.

You can see the total salaries of all departments.

Notice that the relative cell reference changed at each row according to the position of the formula, thus returning the exact results.

How to Create a Name for a Constant in Excel

We want to calculate the tax amount each employee has to pay from the dataset below.

Let us assume the tax rate is 7%, and we will define a named range for this constant value.

Open the Define Name option from the Formulas tab.
In the New Name box, type Tax_Rate.
Enter 7% into the Refers to the box and press OK.

Select cell G6 and insert the formula containing the named range.

=Annual_Salary*Tax_Rate

How to Define Name for a Formula in Excel

In Excel, you can define a name for a formula to use it multiple times. Let’s define a name for the formula to find the maximum salary of employees.

First, open the Define Name option from the Formulas tab.
Type Max_Salary as the Name of the formula.
In Refers to box, enter the formula below.

=MAX(Annual_Salary)

Press OK.

Select cell D17 and type:

=Max_Salary

Press Enter to see the result.

How to Name Columns and Rows in Excel

We can name columns and rows in Excel by using the Create from Selection option.

We will name the Employee ID and Full Name columns and their top rows.

Select the cell range B5:C15.
Go to the Formulas tab > Select Create from Selection.
A box named Create Names from Selection will appear.
Check the boxes Top row and Left column to name the selection Employee ID.
Press OK.

In Name Manager, you can view the created names of columns and rows.

Note

Notice that, named rows have workbook-level scope whereas named columns have worksheet-level scope.

How Do You Use Named Range in Another Sheet?

To use the named range in another sheet, you just have to keep the Scope of the named range as Workbook. This will let you use your named range in any worksheet in that workbook.

We have a “Designation” named range with a workbook-level scope. We can use this named range in any other worksheet in the workbook.

Issues with Named Ranges in Excel

1. Occurrence of Name Conflict Dialog Box

The name conflict dialog box appears when you try to copy one or more sheets into another workbook containing named ranges having a common name.

For example, we have already created Annual_Salary named range in our current workbook, named “Excel Named Range”. We have another workbook named “Employee Details” with the same named range Annual_Salary, shown in the image below.

We will try to copy this sheet to our current workbook, named “Excel Named Range”.

Right-click on the sheet name.
Choose Move or Copy from the context menu.

A box named Move or Copy will show up.
Click on To book drop-down.
Choose the workbook name where you want to move or copy the sheet. We selected the “Excel Named Range.xlsx” workbook.

The sheet names of that book will be available. You can choose where you want to put the copied sheet. We kept it at first.
Check the Create a copy box and press OK.

A warning message from Microsoft Excel will pop up stating that the Annual_Salary name already exists with three options. Press:

Yes: to use that version of the name
Yes to all: to use that version name for all the name that conflicts at once
No: to rename the version of the named range you are moving or copying

Click the image to get a better view

If you press No, then another box named Name Conflict will appear. In the New Name box, you can change the name that conflicts.

2. Occurrence of Name Errors (#REF and #NAME)

While working, deleting some cells or editing might cause errors. Usually, #NAME and #REF errors are the most common to occur. Let us show you why these errors occur and how to solve them.

#NAME Error

#NAME error occurs when any non-existing name is entered, or any name is misspelled.

Like in the image below, we wanted to calculate the highest salary for 2023. But we misspelled the named range. We entered Salary_202 instead of Salary_2023. As a result, Excel is showing a #NAME error.