Using Range Names in Functions

To make your functions more readable and easier to type, you can name a range of cells in your worksheet and then use the range name in place of cell references throughout your workbook. For example, you could give the cells E4 through E9 the name Subtotal and then use the SUM function to add the five cells by entering the following formula:

=SUM(Subtotal)

After you assign a name to a cell range, you can use the name in any formula in your workbook.

Creating Range Names

Excel gives you two techniques for naming ranges in a workbook: You can click the Name box and type a range name (the tactile way), or you can use the Create command on the Name submenu (the automated way). If you have a column heading already in place, the automated way is slightly faster.

Range names must begin with a letter and can't include spaces. We recommend that you limit your names to 15 characters or fewer so that they fit easily into the Name box and so that you can type them quickly in formulas.

Creating Range Names: The Tactile Way

To create a range name by selecting, clicking, and typing (the tactile way), follow the steps shown in Figure 20-4. This way is recommended for touchy-feely people.

Figure 20-4. Creating a named range by hand.

Creating Range Names: The Automatic Way

To create a range name using the Create command, follow these steps:

1. Select the range you want to name, and include a row or column heading in the selection to define the name. For example, the following selection includes the text label Subtotal for the range name:



2. Choose Name from the Insert menu, and then choose Create from the submenu.

The Create Names dialog box appears, prompting you for the location of the range name within your selection:



3. Click OK to accept Excel's default selection if you included a row or column label in your range; otherwise, click the option to tell Excel where to find the labels.

In this example, Excel has detected the text label Subtotal at the top of your selection. You can use this name in computations in any worksheet in the workbook.

Putting Range Names to Work

You can use range names as arguments in functions wherever multiple-cell range references are permitted. For example, you could use the Subtotal range name in the SUM and AVERAGE functions because they accept ranges as arguments, but you couldn't use Subtotal in the PMT function because each of the PMT arguments must be a single number.

To insert a range name into a formula or function, follow these steps:

1. Create the formula or function as you normally would. For example, to determine the average of the cells in the Subtotal range, begin your formula as follows:

=AVERAGE(

2. When it's time to specify a range of cells as an argument, type the named range in the formula:

=AVERAGE(Subtotal)

3. When you have finished entering the formula, press Enter.

TIP

---

If you can't remember the names you've given the ranges in your workbook, you can choose one from a list by using the Paste Name dialog box. To insert range names in this manner, type your formula, and when it's time to insert a range name, choose Name from the Insert menu, choose Paste from the submenu, and then double-click the range name that you want to include.

Modifying Ranges

Named ranges make your formulas easy to revise because when you modify the range, Excel automatically updates all your formulas. The Define command on the Name submenu lets you modify a range using the Refers To text box. You can add cells to or remove cells from a range, either by typing in the Refers To text box or by highlighting a new range in the worksheet.

To modify the cells included in a named range, follow these steps:

1. From the Insert menu, choose Name, and then choose Define from the submenu.

The Define Name dialog box includes a list of the named ranges in your workbook and a Refers To text box listing the cells in the named range.

2. Click the named range that you want to modify.
3. Change the cell references in the Refers To text box, or select a new range of cells directly in the worksheet. Just click the Collapse Dialog button in the Refers To box to shrink the dialog box. Then select the cells, and press Enter.



4. Click OK to save your changes.

Deleting Range Names

When you no longer need a range name, you can delete it from your workbook using the Define Name dialog box. Follow these steps to delete a range name:

1. Choose Name from the Insert menu, and then choose the Define command. The Define Name dialog box appears.
2. Click the range name that you want to delete from the workbook.
3. Click the Delete button. The range name is permanently removed from the workbook.
4. Click OK to close the Define Name dialog box.

Range names are an important component of well-documented formulas. When you can use range names well, you'll enjoy using formulas and functions much more.

WARNING

---

If you delete a range name that a formula is currently using, the error value #NAME? will appear in the cell containing the formula. To fix the problem, you'll need to replace the range name in the formula with an actual cell reference or with a valid range name. Note that you can't undo a range name deletion.