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

return to topTips for Assigning Names

return to topCreating Range Names

Creating Range Names: Dialog Box Option

  1. Select the range of cells to be referenced by the name

  2. On the Ribbon, select the Formulas tab

  3. Click NAME MANAGERName Manager button
    The Name Manager dialog box appears.
    Name Manager dialog box

  4. Click NEW...
    The New Name dialog box appears.
    New Name dialog box

  5. In the Name text box, type the desired name

  6. From the Scope pull-down list, select where in the workbook the name will apply

  7. OPTIONAL: In the Comment text box, type a comment or description

  8. In the Refers to text box, verify the cells being reference by this name

  9. Click OK
    The name is added to the Name Manager.

  10. Click CLOSE

Creating Range Names: Name Box Option

  1. Select the range of cells to be referenced by the name

  2. In the Name Box, highlight the currently appearing name
    Name box with current name

  3. Type the desired name
    Name box with new name

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

  1. Select the range of cells to be referenced by the name

  2. Right click the selection » select Name a Range...
    The New Name dialog box appears.
    New Name dialog box

  3. In the Name text box, type the desire name

  4. From the Scope pull-down list, select where in the workbook the name will apply

  5. OPTIONAL: In the Comment text box, type a comment or description

  6. In the Refers to text box, verify the cells being reference by this name

  7. Click OK
    The name is created.

return to topAdjusting Range Name References

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

  1. On the Ribbon, select the Formulas tab

  2. Click NAME MANAGERName Manager button
    The Name Manager dialog box appears.
    Example Name Manager dialog box

  3. From the list of names, select the one whose cell reference, formula, or constant you want to change

  4. In the Refers to text box, change the reference, formula, or constant
    OR
    To select the new range of cells,
    1. Click COLLAPSE DIALOG BOXCollapse dialog box button
    2. In your worksheet, click and drag to select the new range of cells
    3. Click EXPAND DIALOG BOXExpand dialog box button
      The new range appears in the Refers to text box.
      Refers to section of dialog box
    4. To confirm the new range of cells, click ACCEPTAccept button

  5. Click CLOSE
    The range reference(s) are adjusted.

return to topDeleting Range Names

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

  1. On the Ribbon, select the Formulas tab
  2. Click NAME MANAGERName Manager button
    The Name Manager dialog box appears.
    Example Name Manager dialog box

  3. From the list of names, select the one you want to delete

  4. Click DELETE
    A confirmation dialog box appears.

  5. Click OK

  6. To delete other range names, repeat steps 3–5 as necessary

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

return to topUsing 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

  1. Begin creating your formula/function

  2. On the Ribbon, select the Formulas tab

  3. 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:

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

Excellence. Our Measure. Our Motto. Our Goal.