Microsoft Excel 2007

Referencing Cells with Names

A range of cells can referred to by a name that you assign. Names are often used for cell references in functions and for printing.

Tips for Assigning Names
Creating Range Names
Adjusting Range Name References
Deleting Range Names
Using Range Names in Formulas/Functions
Using Range Names: Additional Options

Tips for Assigning Names

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 name with the second one.

Creating Range Names

Creating Range Names: Dialog Box Option

Select the range of cells to be referenced by the name
On the Ribbon, select the Formulas tab
Click NAME MANAGER
The Name Manager dialog box appears.
Click NEW...
The New Name dialog box appears.
In the Name text box, type the desired name
From the Scope pull-down list, select where in the workbook the name will apply
OPTIONAL: In the Comment text box, type a comment or description
In the Refers to text box, verify the cells being reference by this name
Click OK
The name is added to the Name Manager.
Click CLOSE

Creating Range Names: Name Box Option

Select the range of cells to be referenced by the name
In the Name Box, highlight the currently appearing name
Type the desired name
Press [Enter]
The name is created.

Creating Range Names: Quick Menu Option

This option allows you to create names quickly and is useful in creating multiple names quickly.

Select the range of cells to be referenced by the name
Right click the selection » select Name a Range...
The New Name dialog box appears.
In the Name text box, type the desire name
From the Scope pull-down list, select where in the workbook the name will apply
OPTIONAL: In the Comment text box, type a comment or description
In the Refers to text box, verify the cells being reference by this name
Click OK
The name is created.

Adjusting Range Name References

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

On the Ribbon, select the Formulas tab
Click NAME MANAGER
The Name Manager dialog box appears.
From the list of names, select the one whose cell reference, formula, or constant you want to change
In the Refers to text box, change the reference, formula, or constant
OR
To select the new range of cells,
1. Click COLLAPSE DIALOG BOX
2. In your worksheet, click and drag to select the new range of cells
3. Click EXPAND DIALOG BOX
  The new range appears in the Refers to text box.
4. To confirm the new range of cells, click ACCEPT
Click CLOSE
The range reference(s) are adjusted.

Deleting Range Names

If you no longer need to refer to a range of cells that you have named, you can delete the name.

On the Ribbon, select the Formulas tab
Click NAME MANAGER
The Name Manager dialog box appears.
From the list of names, select the one you want to delete
Click DELETE
A confirmation dialog box appears.
Click OK
To delete other range names, repeat steps 3–5 as necessary
Click OK
The range name(s) are deleted.

Using Range Names in Formulas/Functions

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

Names can be used almost anywhere that you would enter a range of cell references. Common uses of names are in formulas, functions, and print ranges. When writing a function, simply use the 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.

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

Using Range Names in Functions: Ribbon Option

Begin creating your formula/function
On the Ribbon, select the Formulas tab
In the Named Cells group, click USE IN FORMULA » select the desired range name
The reference to the range is inserted.

Using Range Names in Functions: Typing Option

If you know the name you want to use and do not need to see the list of names used in your workbook, 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 names

Using Range Names: Additional Options

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

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.