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:
After you assign a name to a cell range, you can use the name in any formula in your workbook.
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.
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.
To create a range name using the Create command, follow these steps:
The Create Names dialog box appears, prompting you for the location of the range name within your selection:
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.
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:
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.
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:
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.
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:
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.
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.