Microsoft Excel 2008

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.

Naming Ranges: Tips
Creating Range Names
Adjusting Range References
Deleting Range Names
Using Range Names in Formulas/Functions
Using Range Names: Additional Options

Naming Ranges: Tips

The name should be descriptive (grades is more descriptive than range1).
The first character must be a letter or an underscore character. Other characters can be letters, numbers, or periods.
The name cannot look like a cell reference (e.g., "A1", "R1C1").
To separate words, the underscore character or a period must be used instead of a space.
The name can be up to 255 characters long.
The names are not case sensitive; they can contain upper and lowercase letters. If you name one range BUDGET and a second range Budget, Excel will overwrite the first range with the second one.

Creating Range Names

Creating Range Names: Dialog Box Option

Select the data to be included in the range
From the Insert menu, select Name » Define...
OR
Press [] + [F3]
The Define Name dialog box appears.
In the Names in workbook text box, type the desired range name
Click OK
The range name is created.

Creating Range Names: Name Box Option

Select the cells to be included in the range
In the Name Box, highlight the currently appearing range name
HINT: If the Name Box does not appear, from the View menu, select Formula Bar.
Type the desired range name
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.

From the Insert menu, select Name » Define...
The Define Name dialog box appears.
In the Names in workbook text box, type the desired range name
In the Refers to text box, type the range of cells
OR
To select the cells,
1. Click COLLAPSE DIALOG
2. Select the cells to be included in the range
  HINT: To select non-contiguous cells, press the [] key while clicking the cells.
3. Click RESTORE DIALOG
Click ADD
Repeat steps 2-4 as necessary for additional ranges
Click OK
The range names are created.

Adjusting Range References

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

From the Insert menu, select Name » Define...
The Define Name dialog box appears.
From the Names in workbook scroll box, select the range name whose cell reference, formula, or constant you want to change
In the Refers to text box, change the reference, formula, or constant
Click OK
The range reference(s) are adjusted.

Deleting Range Names

To delete a range name, use the following directions:

From the Insert menu, select Name » Define...
The Define Name dialog box appears.
From the Names in workbook scroll box, select the range name to be deleted
Click DELETE
To delete other range names, repeat steps 2-3 as necessary
Click OK
The range name(s) are deleted.

Using 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

Begin creating your formula/function
To add the range name, press [F5]
The Go To dialog box appears.
Under Go To, select the desired range name
Click OK
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:

Begin creating your formula/function
To add the range name, type it into the formula/function
EXAMPLE: =SUM(RangeName)
Continue creating your formula/function, repeating step 2 as necessary to add other range names

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