Contextures logo

Search Contextures Sites

Excel Names -- Excel Named Ranges

Name a Range -- Excel Name Box 
Use Excel Names  
Create a Dynamic Named Range

 

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

After you define the Excel 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 Excel named range.
   

Name a Range - Excel Name Box

You can create an Excel named range quickly by typing in the Excel Name Box.
  1. Select the cell(s) to be named
  2. Click in the Excel Name box, to the left of the formula bar
  3. Type a one-word name for the list, e.g. FruitList.
  4. Press the Enter key.

Use Excel Names

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

You can also use Excel 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 Excel names TotalSales and TaxRate have been defined.

=TotalSales * TaxRate

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

 

Create a Dynamic Named Range

Excel names in name box

Another way to create Excel names is to use a dynamic formula to define an Excel 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 Excel 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
 

Excel names dialog box

  1. 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:
    1. Reference cell: Sheet1!$A$1
    2. Rows to offset: 0
    3. Columns to offset: 0
    4. Number of Rows: COUNTA(Sheet1!$A:$A)
    5. Number of Columns: 1
        Note: for a dynamic number of columns, replace the 1 with:
                  COUNTA(Sheet1!$1:$1)
  2. Click OK

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

Excel 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.

       Home     Excel Tips     Excel Files     The Excel Store     Blog     Contact

 

Privacy Policy

 

Contextures Inc., Copyright ©2012
All rights reserved.

 

Last updated: October 2, 2010 2:34 PM