Page 157 - Excel Timesaving Techniques for Dummies

30_574272 ch26.qxd 10/1/04 10:48 PM Page 142
142
Technique 26: Using Range Names
To quickly select a cell range or nonadjacent When the Left Column check box is selected, the
selection after naming it, click the drop-down program assigns the row headings in the first col-
button on the Name Box and then select the umn of the cell selection to the rows of the table.
selection’s name in the drop-down list. To print (It also assigns the row heading in the top row of
the cell selection, choose File➪Print, click the the leftmost column to all the rows of data in the
Selection option button in the Print dialog box, entire table.)
and click OK.
If the top row of your table doesn’t contain column
Creating names from row and column headings headings, clear the Top Row check box. Likewise, if
its first column doesn’t contain row headings, clear
Instead of taking the time to use individual descrip- the Left Column check box. Also, if your table uses
tive names to assign names to ranges in a standard an unusual layout in which the bottom row contains
data table, it’s almost always more efficient to have the column headings, clear the Top Row check box
Excel do all the naming for you by using a table’s and select the Bottom Row one instead. Finally, if the
existing row and column headings. rightmost column of your table contains the row
headings, clear the Left Column check box and
To do this, select the table (including the cells with
select the Right Column one in its place.
the row and column heading you want assigned) and
then choose Insert➪Name➪Create to open the The table shown in Figure 26-2 illustrates a situation
Create Names dialog box (shown in Figure 26-1). where you can use the Create Names feature to good
advantage. To assign range names to the table (which
in turn I can use to assign names to the formulas in
the Sales Price column [E]), I select the table including
the row headings in column A and the column head-
ings in row 2. Then I open the Create Names dialog
box (Insert➪Name➪Create) and accept the default
settings (Top Row and Left Column). Note that I
don’t bother to select the table’s title in cell A1.

The range names you assign with the Create
Names feature refer only to cells that contain
data of the table and do not include the row
and column headings at the top and left or
bottom and right of the cell selection.

• Figure 26-1: The Create Names dialog box enables you Figure 26-3 shows a list of the range names (and the
to assign names from column and row cell references to which they refer) created with the
headings.
Create Names feature. I pasted this list into the cell
range B10:C20 by selecting cell B10, choosing Insert➪
When you first open the Create Names dialog box, Name➪Paste, and clicking the Paste List button.
Excel automatically selects both the Top Row and
Left Column check boxes: All the range names you assign with Create
Names are added to the Name Box drop-
When the Top Row check box is selected, Excel down list (on the Formula bar), meaning that
assigns the column headings in the first row of you can select their ranges in the worksheet
your cell selection to the columns of data in the simply by clicking their names on this drop-
table. down list.