Microsoft Office Tutorials and References
In Depth Information
Naming cells and ranges
Excel provides several ways to name a cell or range:
h Choose Formulas➜Defined Names➜Define Name to display the New Name dialog box.
h Use the Name Manager dialog box (Formulas➜Defined Names➜Name Manager or press
Ctrl+F3). This method isn’t the most efficient because it requires clicking the New button
in the Name Manger dialog box, which displays the New Name dialog box.
h Select the cell or range and then type a name in the Name box and press Enter. The
Name box is the drop-down control displayed to the left of the formula bar.
h If your worksheet contains text that you’d like to use for names of adjacent cells or
ranges, select the text and the cells to be named and choose Formulas➜Defined
Names➜Create from Selection. In Figure 3-2, for example, B3:E3 is named North, B4:E4 is
named South, and so on. Vertically, B3:B6 is named Qtr_1, C3:C6 is named Qtr_2, and so
on. Note that Excel changes the names to make them valid. (A hyphen isn’t a valid
character in a name.)
Using names is especially important if you write VBA code that uses cell or range references. The
reason? VBA does not automatically update its references if you move a cell or range that’s
referred to in a VBA statement. For example, if your VBA code writes a value to Range(“C4”) ,
the data will be written to the wrong cell if the user inserts a new row above or a new column to
the left of cell C4. Using a reference to a named cell, such as Range(“InterestRate”) , avoids
these potential problems.
Figure 3-2: Excel makes it easy to create names that use descriptive text in your worksheet.