excel Using Names

One of the most useful features in Excel is its ability to provide meaningful names for various
items. For example, you can name cells, ranges, rows, columns, charts, and other objects. You can
even name values or formulas that don’t appear in cells in your worksheet. (See the “Naming
constants” section, later in this chapter.)
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.