How to Create Named Range in Excel?

It easy to use a cell or the range of cells by a name instead of their references. In excel we can create named range which makes the working in excel easier. However, using the named range also saves our time and provide efficiency thereby avoiding mistakes which occur while typing the reference for the range of cells.

In this section, we will discuss the steps to:

1. Name a Single Cell
2. Name Range of Cells

Steps to Create Named Range in Excel 2016:

Create Named Cell in Excel

Step 1: Select a cell that you want to provide a name. In the ‘name box’, you will see the cell number as in the image below the cell I have selected lies in column B & row 3. Hence, in the name box, the name of the selected cell is showing B3.

Step 2: You can directly rename the selected cell in the name box itself or you can name it using the Name Manger or Define Name in the Defined Names group of Formulas Tab. For example, in the image below, I have named the selected cell as ‘Joseph_Staff_id’ as the cell containing the staff_id of Joseph.

Step 3: Once you name a cell you can use that cell by its name anywhere on the workbook. Like, in the image below you can see I have clicked on a random cell in the sheet and have typed:

=joseph_Staff_id

As I have just started typing the name of the cell, the excel recognizes it and starts displaying similar options for it. You can select from those options.

After typing the name of the cell press Enter and you will observe that the value of the named cell is displayed in the selected cell. Look at the image below the cell where I have used the named cell is displaying the value of the named cell.

Similarly, you can name a cell that contains a formula and use that formula just by using the name of the cell. It will avoid the mistakes that you may make while retyping the formula in any other cell or while using the reference of the cell containing the formula.

Create Named Range of Cells

If you have your data in the tabular form and you want to create a name for each row or column, based upon their labels then you can do it by performing the following steps.

Step 1: To create a named range, select the range of cells along with their column or row labels to which you want to provide a name.

Step 2: In Formulas tab under the Defined Names group click on Create from Selection. A Create Names from Selection dialog box will pop up, where you can decide the location that will be considered as the labels for your selected range.
You have options to create the names of the values in the top row, left column, bottom row & right column.

In the image below, you can see that I have created named ranges from the values in the Top row. So, 4 named ranges are created which are Staff, Staff-Id, Sales & Days.

Step 3: To use the created named range let us encounter an example:

Let’s say I want to sum all the days that each staff has worked for.

So, anywhere in the workbook type:

=Sum(Days)  // press enter

It will sum up all the days that each staff has worked for.

Let us create named range for the values in the left column:

Step 1: Select the range of cells along with the column or row labels if any. In the Formulas tab under the Defined Names group, click on Create from Selection.

In the Create Names from Selection box click on Left column and click OK. It will create 14 named ranges which are Staff, Joseph, Stieve, Fred, Charles, Davis Clawson, Natalia, Sheril, Jenny, Emiliy, Annie Rotus, Emma, Jhona, Roma.

Step 2: Let’s see the example for the created named range.

Anywhere on the workbook, I have typed:

= Natalia

Or you can get the name of range by clicking on Use in Formula under Defined Names group of Formulas tab. It will show you the names of all the named ranges. Select the named range of your use.

Press Enter, it will show you the value of the named range.

So, this is all about how one can create named range in Excel. It is very easy to use the name instead of using the reference of the range in formulas.