Microsoft Office Tutorials and References
In Depth Information
Speed Techniques for Entering Formulas
Naming cell ranges has one disadvantage, and it’s a big one. Excel doesn’t
adjust cell references when you copy a formula with a range name from one
cell to another. A range name always refers to the same set of cells. Later in
this chapter, “Copying Formulas from Cell to Cell” explains how to copy
Creating a cell range name
Follow these steps to create a cell range name:
1. Select the cells that you want to name.
2. On the Formulas tab, click the Define Name button.
You see the New Name dialog box.
3. Enter a descriptive name in the Name box.
Names can’t begin with a number or include blank spaces.
4. On the Scope drop-down list, choose Workbook or a worksheet name.
Choose a worksheet name if you intend to use the range name you’re
creating only in formulas that you construct in a single worksheet. If
your formulas will refer to cell range addresses in different worksheets,
choose Workbook so that you can use the range name wherever you go
in your workbook.
5. Enter a comment to describe the range name, if you want.
Enter a comment if doing so will help you remember where the cells
you’re naming are located or what type of information they hold. As I
explain shortly, you can read comments in the Name Manager dialog
box, the place where you go to edit and delete range names.
6. Click OK.
In case you’re in a hurry, here’s a fast way to enter a cell range name: Select
the cells for the range, click in the Name Box (you can find it on the left side
of the Formula bar, as shown in Figure 3-7), enter a name for the range, and
press the Enter key.
Entering a range name as part of a formula
To include a cell range name in a formula, click in the Formula bar where
you want to enter the range name and then use one of these techniques to
enter the name:
✦ On the Formulas tab, click the Use in Formula button and choose a cell
range name on the drop-down list (refer to Figure 3-7).
✦ Press F3 or click the Use in Formula button and choose Paste Names
on the drop-down list. You see the Paste Name dialog box (refer to
Figure 3-7). Select a cell range name and click OK.