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.

Using Name Box to Create Named Range

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.

Name Box Resizing handle

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.

Selecting Define Names Option from Formulas Tab

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.

Typing Name and Pressing OK

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.

Selecting Name Manager Option

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

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

Scope Option Not Available

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.

Deleting the name

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.

Applying New Name to Change the Scope

Method 3 – Using Name Manager

  • Click the Formulas tab >> Name Manager option.

Selecting Name Manager Option

  • In the Name Manager dialog box, click New.

Selecting New Option in Name Manager

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

Type the name and click on the up-arrow of 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.

Selecting the cell range

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

Pressing OK

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

Showing Created Named Range and Press Close

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.

Selecting Create from Selection Option

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

Selecting Top Row Option and Pressing 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.

Showing the created named range

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.

Selecting Cell range and Table Option

  • 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 My table has headers option and 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.

Showing Table being dynamic by entering new data

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.

Selecting Define names option

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

Type Name, Paste Formula and press OK

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

Showing that created named range is dynamic

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

Showing the new name automatically added to named range

2. Two-Dimensional Dynamic Named Range

  • Go to Formulas > Define Name.

Selecting Define names option

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


  • Press OK.

Type Name, Paste 2D Dynamic Range Formula and press OK

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

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

Showing 2D Dynamic named range

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.

Showing the new name automatically added to named range

How to Find Named Ranges in Excel

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

Selecting Go To Option

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

Select a Named Range and Press OK

You will see the named range selected in your dataset.

Go to feature selected the named range

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.

Selecting Name Manager Option

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

Selecting a Named range and Pressing Edit Option

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

Edit Name Dialog Box

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.

Selecting an Option from Filter

Showing Filtered Table Name

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.

Selecting a Named Range and Pressing Delete

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

Pressing OK

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.

Selecting Paste Names Option from Use in Formula

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

Select paste list option

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

List of all names pasted

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.

Selecting a cell and typing a named range

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

Contents of  Named range pasted

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.

Dataset with 2022 and 2023 salaries

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.
Naming Salary 2022 column

Click the image to get a better view

Name the column “Salary (2023)”.

Naming Salary 2023 column

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

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.

Selecting Define names option

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

Creating Dynamic Named Range ID

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


Creating Dynamic Named Range Salary

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.

Select Column Chart Option-43

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

Right-Click on Chart and Click on Select Data Option from Context Menu

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

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

Entering Series Name and Values

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

Select Edit Option in Horizontal Axis Label

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

Entering Axis Label range

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

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.
Changing Color of Columns

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.

Entering A New Row of Data that Automatically Added Into 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”.


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

Applying a formula

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

Selecting Apply Names Option

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

Press OK

The cell references were replaced with the named ranges.

Names Automatically Added

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.

Setting Up Relative Reference

  • Insert the following formula in cell C18.

Entering a formula using relative name

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.

Drag the Fill Handle Down

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

Relative Name Giving Proper 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.

Dataset for calculating tax amount

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.

Setting Up Tax Rate to Named range

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

Using Created Named Range for a Constant in a formula

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.
  • Press OK.

Defining Name for the formula

  • Select cell D17 and type:
  • Press Enter to see the result.

Entering the Named range returns the Maximum Salary

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.

Creating Named Range from Column & Row

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

Showing Named Ranges Created from Columns and Rows

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.

Scope Option Not Available

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.

Showing created named range of another workbook

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.

Right-click on the sheet name and select Move option

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

Selecting Book Name

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

Check 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
Name Conflict Warning

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.

Name Conflict Box to change the conflicting name

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.

Showing Name Error occurrence

You have to enter the correct named range inside the formula. Like in the image below.

Correctly Spelled Named Range inserted

#REF Error

#REF error usually occurs if you delete all cells of a certain named range.

Like in the image below, we calculated the highest salary for 2021 using the named range Salary_2021 which contains cell range F6:F15.

Calculating highest salary of 2021

We will delete the F column. Right-click on the F column and select Delete from the context menu.

Deleting the 2021 Salaries column

Click the image to get a better view

You can see that a #REF error appeared with the deletion of the column.

To avoid this error, be careful when deleting data from your dataset. Check whether the data you are deleting is part of any named range.

Showing #Ref error

Download Practice Workbook

Excel Named Range: Knowledge Hub

<< Go Back to Excel Formulas | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

Priti Halder holds a BSc degree in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. She has been a part of the ExcelDemy project for 6 months and during this time, she has written over 30 articles and 5 comments for the platform. Priti is currently employed as an Excel and VBA content developer and provides effective solutions to various Excel-related issues. She is passionate about expanding her knowledge of data analysis and Microsoft... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF