This browser does not support basic Web standards, preventing the display of our site's intended design. May we suggest that you upgrade your browser?

Microsoft Excel 2003/2004

Working with Range Names

Range names refer to a specific group of cells. They are often used for cell references in functions and for printing.

return to topNaming Ranges: Tips

return to topCreating Range Names

Creating Range Names: Dialog Box Option

  1. Select the data to be included in the range

  2. From the Insert menu, select Name » Define...
    OR
    Windows: Press [Ctrl] + [F3]
    Macintosh: Press [command] + [F3]
    The Define Name dialog box appears.
    Define Name dialog box

  3. In the Names in workbook text box, type the desired range name

  4. Click OK
    The range name is created.

Creating Range Names: Name Box Option

  1. Select the cells to be included in the range

  2. In the Name Box, highlight the currently appearing range name
    HINT: On Macintosh, if the Name Box does not appear, from the View menu, select Formula Bar.
    Name Box

  3. Type the desired range name

  4. Windows: Press [Enter]
    Macintosh: Press [return]
    The range name is created.

Creating Range Names: Multiple Range Names

You may want to assign names to multiple ranges in your worksheet without having to follow a separate dialog box process for each range.

  1. From the Insert menu, select Name » Define...
    The Define Name dialog box appears.

  2. In the Names in workbook text box, type the desired range name

  3. In the Refers to text box, type the range of cells
    OR
    To select the cells,
    1. Windows: Click COLLAPSE DIALOGCollapse Dialog button
      Macintosh: Click COLLAPSE DIALOGCollapse Dialog box button
    2. Select the cells to be included in the range
      HINT: To select non-contiguous cells, press the [Ctrl] key for Windows or the [command] key for Macintosh while clicking the cells.
    3. Windows: Click RESTORE DIALOGRestore Dialog box button
      Macintosh: Click EXPAND DIALOGExpand Dialog box button

  4. Click ADD

  5. Repeat steps 2-4 as necessary for additional ranges

  6. Click OK
    The range names are created.

return to topAdjusting Range References

Adjusting range references allows you to modify the cell references, formulas, and constants included in the range.

  1. From the Insert menu, select Name » Define...
    The Define Name dialog box appears.

  2. From the Names in workbook scroll box, select the range name whose cell reference, formula, or constant you want to change

  3. In the Refers to text box, change the reference, formula, or constant

  4. Click OK
    The range reference(s) are adjusted.

return to topDeleting Range Names

To delete a range name, use the following directions:

  1. From the Insert menu, select Name » Define...
    The Define Name dialog box appears.

  2. From the Names in workbook scroll box, select the range name to be deleted

  3. Click DELETE

  4. To delete other range names, repeat steps 2-3 as necessary

  5. Click OK
    The range name(s) are deleted.

return to topUsing Range Names in Formulas/Functions

When writing functions, referring to a range name rather than using the cell references reduces the chance of errors.

Range names can be used almost anywhere that you would enter a range of cell references. Common uses of range names are in formulas, functions, and print ranges. When writing a function, simply use the range name instead of the cell references. A SUM formula is written as follows:
EXAMPLE: =SUM(C3:C15)

With the range C3:C15 named Payroll, the function could also be written as follows:
EXAMPLE: =SUM(Payroll)

Both functions will yield the same result. The second is often easier to write, understand, and troubleshoot.

If you cannot recall a range name when you are writing a function/formula, you can use one of the following methods to determine your names and to include them in the function/formula. For more information, refer to Creating Range Names.

Using Range Names in Functions: List Option

  1. Begin creating your formula/function

  2. To add the range name, press [F5]
    The Go To dialog box appears.

  3. Under Go To, select the desired range name

  4. Click OK

  5. Continue creating your formula/function, repeating steps 2-4 as necessary to add other range names

Using Range Names in Functions: Typing Option

If you know the range name you want to use and do not need to see the range name list, follow these instructions:

  1. Begin creating your formula/function

  2. To add the range name, type it into the formula/function
    EXAMPLE: =SUM(RangeName)

  3. Continue creating your formula/function, repeating step 2 as necessary to add other range names

return to topUsing Range Names: Additional Options

To move to a specific cell on a worksheet, you can use the Go To command and your range names. The Go To command is useful when moving between ranges. For more information, refer to Using the Go To Command.

Range names can also be used to help you define a printing area. For instructions on how to print ranges, refer to Printing a Range of Cells.

Excellence. Our Measure. Our Motto. Our Goal.