Excel Named Range – Make Your Work Easier!

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.

Content

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 named range is easy to understand and maintain.
  • Names are easy to memorable 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 named range
  • When you type 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 cotain space
  • Names can be up to 255 characters in length
  • You cannot use cell reference as names. For example you cannot use “A1” or “AB1” as names.
  • Names are case insensitive. For example, “LineTotal” and “linetotal” treated as same name.

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 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!LineTotal

Method 1: Use name box to create named ranges

The use of the name box is the easiest method to create a named range in Excel.

  1. Select the Cell or the Range of Cells to be named
  2. Type the range name in the Name Box
  3. Press Enter

Note: The named range created from 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 “Define name” option to create 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.

  1. Select the cell or range of cells.
  2. Go to Formulas tab > Defined Names group and click Define Name button. This will open the New Name dialog box.
  3. In the New Name dialog box.
    • Name: Type the name of you 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 include the cell reference. It is automatically generated based on your selection in step 1. You can adjust the cell reference if necessary.
  4. Click OK to save the new name.

Method 3: Use “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 selection has a workbook-level scope.

  1. Select the range of cells you need to namge, including the row or column labels.
  2. Go to Formulas > Create from Selection. (you can also use the sortcut Ctrl+Shift+F3)
  3. In the Create Names from Selection dialog box, choese the location that contain the labels by selecting Top row, Left column, Bottom row, or Right column check boxes.
  4. 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,

  1. Go to Formulas > Name Manager ( in Defined Names group) or click Ctrl+F3
  2. In the Name Manager dialog box, click New… button
  3. 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?

  1. Go to Formulas > Name Manager
  2. Find and select the name you want to edit
  3. Click Edit button
  4. It will open the edit name dialog box with following options
    1. Name: Name of the range
    2. Scope: Scope of the range. This option is disabled. Excel do not allow you to change the scope of the names you already defined.
    3. Comment: You can add some comments to describe the named range.
    4. Refers to: Here you can update the range of cells that refers the named range.
  5. Click OK to apply your changes.

How to delete Named Ranges in Excel?

To delete named ranges,

  1. Go to Formulas > Name Manager
  2. 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.
  3. Click delete button
  4. 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, or 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 Name Manager dialog box.

Ctrl + Shift + F3 : Open Create Names from 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

  1. Click the dropdown arrow of the named box
  2. Click on the named range you want to locate/ find. Then it will activate the relevent sheet, and select the range of cells that represented by the name and bring it 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,

  1. Go to Home > Find & Select > Go To… (Alternatively you can use the keyboard shortcut Ctrl+G to save time)
  2. 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 relevent 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 arrange that is a specified number of rows and columns from a cell or range of cells. The function has five arguments as below.

OFFSET(reference, rows, cols, [height], [width])

In our case, we can use the first three arguments to define the initial range. 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.

image

To create the dynamic named range,

  1. Go to Formulas > Define Name
  2. For “Name” field type a name for your named range. In this case I type “Amount”
  3. 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$6 is the first cell of the range.
  • The second and third arguments set to zero,
  • Forth argument, COUNTA(Sheet1!$F$6:$F$100) counts the non blank cells adjecent to the first cell. “Sheet1” is the name of the sheet which named range defined. It counts cells upto 100th row, and you may change it according to your requirement.
  • The fifth argument 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 formula

To use named range in formula,

  1. Select a cell and start writng formula (E.g. =SUM…)
  2. Place the cursor where you want to insert the name and start typing first few letters of the named range.
  3. Then, the list of names and function matches the letters you type will appear in a dropdown.
  4. Select the name and hit Tab key (you do not need to type the entire name, use the Tab key to enter selected name or function).

You can also got Formulas > Us in Formula and select the name while typing the formula to enter 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.

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.