Microsoft Excel 2008: Working with Range Names (Mac)

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

1. Select the data to be included in the range
2. From the Insert menu, select Name » Define...
   OR
   Press [] + [F3]
   The Define Name dialog box appears.
   
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: If the Name Box does not appear, from the View menu, select Formula Bar.
   
3. Type the desired range name
4. 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. 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
4. Click ADD
5. Repeat steps 2-4 as necessary for additional ranges
6. 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.

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.

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

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

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

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.



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