Excel -- Names -- Naming Ranges
Name a Range -- Name Box
Create a Dynamic Range
 

Name a Range - 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 one-word name for the list, e.g. FruitList.
  4. Press the Enter key.

After naming the range, you can select its name in the Name Box dropdown list, to select the range on the worksheet.

You can also use names in formulas. For example:

=SUM(JanSales)

=TotalSales * TaxRate

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

 

Create a Dynamic Range

You can use a dynamic formula to define a 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 name 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
 

  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

   
1. Names -- Naming Ranges  
2. Names -- Using Names in Formulas  		  
 
       Home     Excel Tips     Excel Files      Blog    Contact

 

RSS Feed

 

 

 

The Excel Store

Last updated: July 18, 2008 11:42 PM