Microsoft Excel 2007: (Win) Referencing Cells with Names

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

1. Select the range of cells to be referenced by the name
2. On the Ribbon, select the Formulas tab
3. Click NAME MANAGER
   The Name Manager dialog box appears.
   
4. Click NEW...
   The New Name dialog box appears.
   
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
   
3. Type the desired 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.
   
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.

Adjusting 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 MANAGER
   The Name Manager dialog box appears.
   
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 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
5. 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.

1. On the Ribbon, select the Formulas tab
2. Click NAME MANAGER
   The Name Manager dialog box appears.
   
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.

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

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

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.


Make sure to let me know in the comments below or on our Facebook page how you've got with it or Do you have any questions