Contextures

Excel Names - Named Ranges

Create Excel names that refer to cells, a constant value, or a formula. Use names in formulas, or quickly select a named range.

Excel Names - Introduction

In Excel, you can create names that refer to:

  • Cell(s) on the worksheet
  • Specific value
  • Formula

After you define Excel names, you can:

  • Use those names in a formula, instead of using a constant value or cell references.
  • Type a name, to quickly go to that named range of cells

The instructions below show how to create names and use names in your Excel files. Get the sample Excel workbook, to follow along with the instructions.

How to Name Cells

Watch this short video to see how to name a group of cells. Then, go to that named group of cells, or use the name in a formula. The written instructions are below the video. TOP

Name Cells - Name Box

You quickly name the selected cells by typing in the Name Box. NOTE: There are a few rules for Excel names

  1. Select the cell(s) to be named
  2. Click in the Name box, to the left of the formula bar
  3. Type a valid one-word name for the list, e.g. FruitList.
  4. Press the Enter key.

Excel names in name box

Rules for Creating Names

There are rules for Excel names on the Microsoft site, and those are summarized below.

For an in-depth look at what characters are allowed, see Martin Trummer's GitHub project excel-names -- it has written examples, and an Excel file to download.

Follow these rules from Microsoft, when you're creating a name in Excel.

  • The first character of a name must be one of the following characters:
    • letter
    • underscore (_)
    • backslash (\).
  • Remaining characters in the name can be
    • letters
    • numbers
    • periods
    • underscore characters
  • The following are not allowed:
    • Space characters are not allowed as part of a name.
    • Names can't look like cell addresses, such as A$35 or R2D2
    • C, c, R, r -- can't be used as names -- Excel uses them as selection shortcuts
  • Names are not case sensitive. For example, North and NORTH are treated as the same name.

Change a Named Range

After you create a named range, you might need to change the cells that it refers to. Follow these steps to change the range reference:

  1. On the Ribbon, click the Formulas tab
  2. Click Name Manager
  3. In the list, click on the name that you want to change
  4. In the Refers To box, change the range reference, or drag on the worksheet, to select the new range.
  5. Click the check mark, to save the change
  6. Click Close, to close the Name Manager TOP

Change a name in Name Manager

Create Names from Cell Text

To quickly name individual cells, or individual ranges, you can use heading cell text as the names. Watch this video to see the steps. Written instructions are below the video.

Create Names from Cell Text

A quick way to create names is to base them on heading cell text (worksheet labels). In the example shown below, the cells in column E will be named, based on the labels in column D.

NOTE: If the labels contains spaces, those are replaced with an underscore. Other invalid characters, such as & and # will be removed, or replaced by an underscore character.

select labels and cells

To name cells, or ranges, based on worksheet labels:

  1. Select the labels and the cells that are to be named. The labels can be above, below, left or right of the cells to be named. In this example, the labels are in column B, to the left of the cells that will be named.
  2. select labels and cells

  3. On the Ribbon, click the Formulas tab, then click Create from Selection.
  4. select labels and cells

  5. In the Create Names From Selection window, add a check mark for the location of the labels, then click OK. In this example, the labels are in the left column of the selected cells.
  6. select labels and cells

  7. Click on a cell to see its name. In the screen shot below, cell C4 is selected, and you can see its name in the Name Box -- Full_Name.
    NOTE: The space character was replaced with an underscore.

    select labels and cells

How to Use Excel Names

After creating names, you can use them:

If a name refers to a range, you can select that name in the Name Box dropdown list, to select the named range on the worksheet.

NOTE: If a name does not appear in the drop down list, you can type the name instead

select labels and cells

Use Names in Formulas

You can also use names in formulas. For example, you could have a group of cells with quantities sold. Name those cells Quantity, then use this formula to calculate the total amount:

=SUM(Quantity)

select labels and cells

Create a Dynamic Named Range

If the list that you want to name will change frequently, having items added and removed, you should create a dynamic named range. A dynamic named range will automatically adjust in size, when the list changes. Here are two ways to create a dynamic named range:

Use a Named Excel Table

Use a Formula

Use a Named Excel Table

The easiest way to create a dynamic named range is to start by creating a named Excel table. Then, define a range based on one or more columns in that table.

In this example there is a list of parts on the worksheet, and a named table, and dynamic named ranges will be created. Later, if you add new items to the table, the named range will automatically expand.

First, create the table:

  1. Select a cell in the parts list
  2. On the Ribbon's Insert tab, click Table
  3. Check that the correct range has been selected, and add a check mark to My Table Has Headers
  4. Click OK, to create the table.
  5. parts table

  6. (optional) Change the table's default name (e.g. Table1) to a meaningful name, such as tblParts
  7. parts table

Next, create a dynamic list of part IDs:

  1. Select cells A2:A9, which contain the Part IDs (not the heading)
  2. Click in the Formula Bar, and type a one-word name for the range: PartIDList
  3. Press the Enter key, to complete the name.

Part ID List name

To see the name's definition, follow these steps:

  • Click the Ribbon's Formulas tab, and click Name Manager.
  • There are two named items in the list:
    1. the Parts table, with the default name, Table1 (or the name that you gave to the table)
    2. the PartIDList, which is based on the PartID field in Table1.

Part ID List name

Text the Dynamic Range

Because the PartIDList named range is based on a named table, the list will automatically adjust in size if you add or remove part IDs in the list.

  • Add a new item in the list of Part IDs
  • In the Name Box, select the PartIDList name
  • The named range is selected, and it includes the new Part ID. TOP

Part ID List name

Dynamic Named Range - Formula

When you create a named range in Excel, it doesn't automatically include new items. If you plan to add new items to a list, you can use a dynamic formula to define an Excel named range. Then, as new items are added to the list, the named range will automatically expand to include them.

The written instructions are below the video.

Dynamic Named Range Based on Formula

If you don't want to use a named table, you can use a dynamic formula to define a named range. As new items are added, the range will automatically expand.

Note: Dynamic named ranges will not appear in the Name Box dropdown list. However, you can type the names in the Name Box, to select that range on the worksheet.

  1. On the Ribbon, click the Formulas tab
  2. Click Define Name
  3. Type a name for the range, e.g. NameList
  4. Leave the Scope set to Workbook.
  5. Excel names dialog box

  6. In the Refers To box, enter an Offset formula that defines the range size, based on the number of items in the column, e.g.:
    =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
    In this example, the list is on Sheet1, starting in cell A1
    The arguments used in this Offset function are:
    • Reference cell: Sheet1!$A$1
    • Rows to offset: 0
    • Columns to offset: 0
    • Number of Rows: COUNTA(Sheet1!$A:$A)
    • Number of Columns: 1
    • Note: for a dynamic number of columns, replace the 1 with:
                  COUNTA(Sheet1!$1:$1)
  7. Click OK TOP

Download the Sample File

To follow along with the instructions on this page, download the Excel Names Sample File. The zipped file is in xlsx format, and does not contain any macros. TOP

Using Names in Formulas  

Named Excel Tables

Create Dynamic Ranges With a Macro 

OFFSET Function

 

Custom Search

 

30 Excel Functions in 30 Days

 

 

 

 

excel tools

 

 

 

 

 

pivot power premium

 

 

 

chart tools

 

 

30 Excel Functions in 30 Days

 

 

 

excel tools

 

 

30 Excel Functions in 30 Days

 

 

Last updated: November 16, 2017 1:22 PM