Search Contextures Sites

 

Contextures
Excel news
by email

 

 

Learn how to create Excel dashboards.

 

30 Excel Functions in 30 Days

 

 

Your worksheet formulas can create traffic-light charts, highlight chart elements, assign number formats, and more.

 

 

Learn how to create Excel dashboards.

 

 

Live-link your Excel dashboards to nearly any web data.

 

 

Excel Names -- Named Ranges

Video: Name a Range of Cells
Name a Range -- Name Box 
Rules for Creating Names
Change a Named Range
Video: Create Names from Worksheet Labels
Create Names from Worksheet Labels
Use Names  
Create a Dynamic Named Range
Video: Create a Dynamic Named Range in Excel 2003
Create a Dynamic Named Range in Excel 2003
More Names Tutorials

You can create Excel names that refer to cells, a range of cells, a constant value, or a formula.

After you define the names, you can use those names in formulas, to replace values or cell references.

If Excel names refer to cells or a range of cells, you can use the names for navigation, to quickly select the named range.

Video: Name a Range of Cells

In Excel, you can create names that refer to a single cell, a group of cells on the worksheet, a specific value, or a formula. After you define Excel names, you can use the names in a formula, instead of using a constant value or cell references. For example, a cell that contains the tax rate could be named SalesTax. Then, other cells could multiply sales amounts by the named range, SalesTax.

For Excel names that refer to a cell or a range of cells, you can use the names to quickly select the named range, and that makes navigation easier. Just select a range name from a drop down list, and you'll immediately go to that range.

The written instructions are below the video.

Name a Range - Name Box

You can create a named range quickly by typing in the Name Box.

  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

An Excel name can't contain space characters, and there are other rules to follow when you're creating a name.

  • The first character of a name must be a
    • letter
    • underscore (_)
    • backslash (\).
  • Remaining characters in the name can be
    • letters
    • numbers
    • periods
    • underscore characters
  • Spaces are not allowed as part of a name.
  • Names can contain uppercase and lowercase letters, and Excel does not distinguish between them. For example, North and NORTH are treated as the same name.
  • Names cannot be the same as a cell reference, such as A$35 or R2D2.
  • You cannot use C,c,R or r as a defined name -- they are used as selection shortcuts.

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, correct 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

Video: Create Names from Worksheet Labels

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

Create Names from Worksheet Labels

A quick way to create names is to base them on worksheet labels. In the example shown below, the cells in column C will be named, based on the labels in the adjacent cells, in column B.

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 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: If the labels contains spaces, they're replaced with an underscore. Other invalid characters, such as & and # will be removed, or replaced by an underscore character.

    select labels and cells

Use Names

After creating names that refer to a range, you can select a name in the Name Box dropdown list, to select the named range on the worksheet.

You can also use names in formulas. For example, you could have a group of cells with sales amounts for the month of January. Name those cells JanSales, then use this formula to calculate the total amount:

=SUM(JanSales)

In the following example, the names TotalSales and TaxRate have been defined.

=TotalSales * TaxRate

To view the steps in a short Named Range video, click here.

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. Unlike a static named range, a dynamic named range will automatically adjust in size, when the list changes.

A dynamic list is ideal if you are creating a drop down list, and want new items shown automatically, or if you are creating a pivot table, and add new records frequently.

In Excel 2007 and later, 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.

First, to 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.

parts table

Next, to 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, click the Ribbon's Formulas tab, and click Name Manager. There are two named items in the list -- the Parts table, with the default name, Table1, and the PartIDList, which is based on the PartID field in Table1.

Part ID List name

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.

Video: Create a Dynamic Named Range in Excel 2003

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.

Create a Dynamic Named Range in Excel 2003

If you are using Excel 2003, which does not have named tables, 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 the range on the worksheet.

  1. Choose Insert>Name>Define
  2. Type a name for the range, e.g. NameList
  3. Excel names dialog box

  4. 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)
  5. Click OK

Names Tutorials

1. Excel Names -- Naming Ranges  
2. Excel Names -- Using Names in Formulas  
3. Excel Names -- Create Dynamic Ranges With a Macro  

 

Learn how to create Excel dashboards.

 

Privacy Policy

 

Contextures Inc., Copyright 2014
All rights reserved.

 

Last updated: March 9, 2014 6:09 PM