Name Range In Excel

What Is Name Range In Excel?

While working in Excel, it is common to refer to cells by their names, such as A1, B4, D3, etc. However, users can give custom names based on the excel cell(s) data instead of referring to cells with the default names. Defining the names for cells or a cell range is called Named Range in Excel. We can easily work with Excel by defining the name for cells or a cell range since we can move from one cell using the names instead of navigating through the cells.

For instance, consider the below table with sales and cost data in column A and the values in column B. The table also has profit value which is obtained by subtracting Sales-Cost. We need to use the following steps to understand Name Range in excel.

Name Range in Excel Intro

In the formula bar, we can see the formula as B2-B3.

After we name cell B2 as ‘Sales’ and B3 as ‘Cost,’ we can see the named cell references in the profit calculation cell.

Name Range in Excel Intro Example

Naming cells also help users understand the actual function. For instance, when we click on cell B4, users can instantly understand that the cell shows the value of profit obtained by using the formula, ‘Profit = Sales-Cost’ when compared with the original references, ‘=B2-B3.’

Please Note: We can view the actual (original) cell references by pressing the F2 key. This key activates the edit mode.

So, now we have understood what name range in excel is about. Let us understand the tool in detail in the following sections.

Key Takeaways
  • Named Ranges in excel can be created by using the name box.
  • It can hold the formulas and the result.
  • This feature makes our work easier; we need not navigate the separate worksheet to select the targeted range if the named range is already created for the required range of cells.
  • Dynamic named ranges make the drop-down list, and any addition in the specified range will be updated dynamically.
  • The top row headers are selected as names for the following cell.

How To Create A Name Range In Excel?

We can create a name range in excel by using 4 different ways. However, before we look at the methods, we must know the rules we must follow before creating a name range in excel. They are:

  • The name should not start with a number. It should always start with the text, underscore (_), or backslash (/).
  • It should not have a space or special characters except underscore (_).
  • It needs to be unique.
  • We cannot name range in excel only with the cell reference. For instance, AZ1 is a cell reference, so we cannot use it.
  • We cannot use single letters such as ‘R’ or ‘C’ because these are the characters used to select rows or columns for the currently selected cell when we enter them in the name box.
  • We can name each cell using a maximum of 255 characters.
  • The name cannot be case-sensitive.

For instance, the function treats ‘January,’ ‘january’, and ‘JANUARY’ as same.

Make sure to remember these points. Now, let us look at the 4 different ways to name a range in excel.

Create Name Range Using Name Box

We can name a cell or range using an Excel name box. When we select any of the cells at the top left corner, we can see the name box displaying the excel cell reference, as shown in the image below:

Basic Example 1

Users can name a cell or a range according to their requirements.

In our example, let us name the cell ‘Product_Cell.’

Name Range in Excel Basic Example 1.1

Similarly, we can also name a range in excel using the name box.

In our example, let us select the entire table, A1:D4, and name it ‘Product_Sales.’

Name Range in Excel Basic Example 1.2

Create Name Range Using Define Name

Under the Formulas tab, click on the Define Name option from the Defined Names group.

Basic Example 2

When we click the option, the New Name window pops up.

Name Range in Excel Basic Example 2.1

Let us understand each of the four options in the window to understand better.

Name: We should type the required name in this box.

So, let us type the name ‘Product_Table’ in this section.

Basic Example 2.2

Scope: By default, the scope will be at the workbook level. We can choose from the drop-down options to change it to a different worksheet.

Comment: This is the comments section where users can add the reason for naming a cell or a range. These comments help future users understand the worksheet and allotted names.

Basic Example 2.3

Refers To: Here, we need to choose the cell range that is being named.

Since we are naming the entire table in our example, choose the cell range A1:D4 in this section.

Basic Example 2.4

Select OK to name range in excel.

Please Note: As soon as we choose the cell range, it becomes an absolute reference by default.

Now, we can use the name ‘Product_Table’ anywhere in the workbook.

For instance, if we type =product in any of the cells, excel will list out the name in the options as shown in the image below:

Basic Example 2.5

When we select the name prompted by excel, it will highlight the cell range.

In our case, excel highlights the range, A1:D4.

Basic Example 2.6

To insert the table into the worksheet, press Enter key.

Excel returns the table as shown in the image below:

Basic Example 2.7

Apart from displaying the range’s name, we can also see that the table has a blue line. It indicates that the table has been imported.

Create Name Range Using Name Manager

Select the Name Manager option from the Formulas tab.

Basic Example 3.1

The Name Manager window opens with the list of names we have created.

Basic Example 3.2

Click on the New tab to open the New Name window.

Name Range in Excel Basic Example 2.1

As discussed earlier, we can fill the options in the window and create names for a cell range.

Create Name Range Using Create from Selection

So far, we have learned how to name a cell or a range in excel using the name range in excel feature. But, using this method, we can name multiple ranges with just a click.

Let us learn the method using the following example.

The below table lists the products and the amount spent in the first quarter in columns A and B, respectively.

Name Range in Excel Basic Example 4

Let us learn how to name ranges in excel.

For instance, the range, B2:D2, is about the product, ‘Furniture’ and the amount spent.

So, let us name this range as Furniture.

Similarly, we can name cell ranges B3:D3 and B4:D4 for stationery and groceries data.

Likewise, we can name tables using the column header. For example, we can name the cell range from B2:B4, C2:C4, and D2:D4 as Jan, Feb, and Mar, respectively.

Now, select the data range from A1:D4 and click on Create from Selection option under the Formulas tab.

Name Range in Excel Basic Example 4.1

The Create from Selection window pops up.

Basic Example 4.2

We have data headers in the top row and the left column. So, by default, it has selected both options.

Next, click OK to create the name ranges.

Now, go to the Formulas tab and click on the Name Manager option.

The Name Manager window pops up with the list of named ranges in excel.

Name Range in Excel Basic Example 4.3

We have 6 different named ranges. So whenever we want to sum values of Jan month, we have to open the SUM excel function and enter the name ‘Jan’.

Name Range in Excel Basic Example 4.4

As we can see in the above image, ‘Jan’ month refers to the cell range B2:B4.

In this way, we can create multiple named ranges in different ways.

So, we have learned how to create a name range in excel.

How To Use Name Range In Excel?

Let us understand the feature better with the following examples.

Example #1: Find Percentage Of Grand Total

For instance, we have the following monthly sales data in an Excel spreadsheet.

Name Range in Excel Example 1

We need to find the percentage share of each month for calculating the total sales. Generally, we first calculate the sum of all the months, and from that month, we calculate the percentage share of each month. We use the same method, and it is highlighted in the following image.

Name Range in Excel Example 1.1

SUM($B$2:$B13$) is the total sales formula for all the months.

The formula may be confusing, but with the help of name range, we can easily create the total sales name range.

Under the Formulas tab, click on the Define Name option.

Basic Example 2

The New Name window pops up.

Let us type the name Total_Sales in the Name: box.

Example 1.2

Similarly, enter the following formula in the Refers to: box as shown in the image:

=SUM(‘Example 1’!$B$2:$B$13)

The sum of all the values from B2:B13 will be calculated instantly.

Example 1.3

Click OK to name the range as ‘Total_Sales’.

Example 1.4

In the percentage share calculation formula, apply this named range as shown in the following image.

Example 1.5

Instead of the SUM function, we have used named range, and the result is the same as the previous one.

Example #2: Apply Names To Existing Formula

We can still name ranges even if we have already applied the formula with regular references.

For instance, we have applied the SUMIF function in the following image to get the particular month’s sales.

Example 2

We have used two ranges here, i.e., A2:A13 for months and B2:B13 for sales.

Now, let us give a name for the range A2:A13 as ‘Month’.

Name Range in Excel Example 2.1

Similarly, give a name for the range B2:B13 as Sale.

Example 2.2

Now, we need to apply the names to the existing formula in cell E2.

So, select cell E2, go to the Formulas tab and click on Define Name.

We can see two options in the drop-down list. Click on Apply Names…

Example 2.3

The Apply Names window pops up. We can see all the names we have created in the workbook.

 Example 2.4

From the list, the named range in excel feature can be applied to ‘Month’ and ‘Sale.’ So, select these two and click on OK.

Example 2.5

When we click OK, it will update the formula with named ranges.

Name Range in Excel Example 2.6

This way, we can use named ranges in Excel to work efficiently.

Delete Name Range In Excel

We know that creating a name range in excel is simple. Similarly, we can delete name range in excel using Name Manager. Let us learn the steps involved.

Go to the Formulas tab and click on Name Manager.

Basic Example 3.1

This will show us the list of names.

Excel Delete - 1

Select the named range that we want to delete and hit the button Delete on top. If we want to delete multiple named ranges, hold the control key and select the named ranges by clicking on them.

Once the selection is made, we can hit the Delete button, and all the selected named ranges will be deleted.

However, if we have too many named ranges to deal with, we can use Filters at the top.

Name Range in Excel Delete - 2

Filters offer a variety of options like choosing named ranges within the worksheet scope, workbook scope, names that have errors, etc.,

As mentioned, we can delete name range in excel.

Dynamic Name Range In Excel

When we create a named range, it is static and will not be updated when the new list is added to the range. For instance, in the following image, we created a drop-down list using a named range.

Dynamic Name Range in Excel - 1

We have named the cells A2:A6 as Month_List and used the same name while creating a drop-down list.

Dynamic Name Range in Excel - 2

However, when we added the month name Jun in cell A7, we could see that the drop-down list is not updated.

Dynamic Name Range in Excel - 3

It is because the named range ‘Month_List’ is not dynamic and any new entries added to the list will not be updated.

We need to make the named range dynamic.

So, go to the Formulas tab and click on Name Manager from the Defined Named group.

Basic Example 3.1

It will bring the list of all the named ranges. For example, select the named range Month_List and click on the Edit option.

Dynamic Name Range in Excel - 4

Now, enter the following OFFSET excel formula in the Refers to box.

=OFFSET(A2,0,0,COUNTA(A2:A13),1).

Dynamic Name Range in Excel - 5

Click on OK.

Now, enter any values from cells A2 to A13, and the drop-down list will be updated dynamically.

Dynamic Name Range in Excel - 6

We have added 4 more months from cells A7 to A10, and the drop-down list dynamically shows the newly added months.

Break Down of the Formula:

The formula denotes the following:

=OFFSET(A2,0,0,COUNTA(A2:A13),1)

=OFFSET(A2: The OFFSET function starts the offset from cell A1.

=OFFSET(A2,0,0: It moves down 0 rows and 0 columns.

=OFFSET(A2,0,0,COUNTA(A2:A13): COUNTA excel function will return the number of rows we have data in cell A2:A13, and then the OFFSET function offsets the same number of rows starting from cell A2.

=OFFSET(A2,0,0,COUNTA(A2:A13),1): Since we are offsetting only one column, the last argument will be 1.

Like this, we can create a dynamic drop-down list by making the named range dynamic.

Important Things To Note

  • Names should not contain any special characters and spaces.
  • Names should start with an alphabet only.
  • Only underscore (_) and backslash (/) can be used as special characters in a name.
  • The name is not case-sensitive, so the names ‘Apple’, ‘apple’, and ‘APPLE’ are treated as the same.
  • The shortcut key to bring the list of names is F3.
  • We can press the shortcut keys Ctrl + F3 to create a new name range.
  • The shortcut keys to create a name range from the current selection are Ctrl + Shift + F3.

Frequently Asked Questions

How do you name a range of cells in Excel?

We can name range in excel with ease.

For instance, consider the table with the list of fruits in column A.

FAQ 1

To name the entire table, i.e., A2:A7, we need to follow the below steps;

● Select the entire table
● Type the desired name in the name box

In our example, let us type the range as ‘Fruit_List.’



Another advantage of the feature is that we can search for the cell range A2:A7 Fruit_List.

Similarly, we can create name range in excel for a specific cell reference or a range.

What is the Benefit of Using a Named Range in Excel?

Using named ranges in Excel, we can easily refer to cells from different worksheets. It speeds up the work and makes it more efficient.

Apart from this, with the help of structured named ranges, calculations can be easily understood when a new user takes over the file.

How to Edit a Named Range in Excel?

We can edit name range in excel with the following steps:

Step 1: Go to the Formulas tab and click on Name Manager option.

Basic Example 3.1

The Name Manager window with a list of created name range in excel pops up.

Excel Delete - 1

We should click on the Edit option to make the required changes.

Likewise, we can edit name range in excel.

How do you create a named range using selection?

We can name range in excel using selection. For instance, consider the following table with revenue earned in Jan, Feb, Mar, and Apr in columns A, B, C, D, and E, respectively.

FAQ 2

If we want to create a name range for each column by using the column headers, then we have to select the data range.

So, go to the Formulas tab and click on Create from Selection.

Name Range in Excel Basic Example 4.1

The Create Names from Selection window appears. Choose the Top Row option.

FAQ 2.1

Click OK to create a name range using selection.

Download Template

This article must be helpful to understanding Name Range in Excel, with its formula and examples. You can download the template here to use it instantly.

This has been a guide to Name Range in Excel. Here we discuss how to create, edit and delete name range with examples and downloadable excel template. You can learn more from the following articles – 

Reader Interactions

Leave a Reply

Your email address will not be published.