Microsoft Office Tutorials and References
In Depth Information
Shortcuts for Creating Cell and Range Names
To create names by using adjacent text, start by selecting the name text and the cells that you
want to name. (These can consist of individual cells or ranges of cells.) The names must be
adjacent to the cells that you’re naming. (A multiple selection is allowed.) Then choose Formulas
Create from Selection (or Ctrl+Shift+F3). Excel displays the Create Names from
Selection dialog box, as shown in Figure 3-5.
The check marks in this dialog box are based on Excel’s analysis of the selected range. For
example, if Excel finds text in the first row of the selection, it proposes that you create names based on
the top row. If Excel doesn’t guess correctly, you can change the check boxes. Click OK, and
Excel creates the names. Note that when Excel creates names using text in cells, it does not
include those text cells in the named range.
Figure 3-5: The Create Names from Selection dialog box.
If the text in a cell would result in an invalid name, Excel modifies the name to make it valid. For
example, if a cell contains the text Net Income (which is invalid for a name because it contains a
space), Excel converts the space to an underscore character and creates the name Net_Income. If
Excel encounters a value or a formula instead of text, however, it doesn’t convert it to a valid
name. It simply doesn’t create a name.
Double-check the names that Excel creates. Sometimes, the Create Names from
Selection dialog box works counterintuitively. Figure 3-6 shows a small table of text
and values. Now imagine that you select the entire table, choose Formulas➜Defined
Names➜Create from Selection, and then accept Excel’s suggestions (Top row and Left
column options). What range does Product refer to? You might expect it to refer to
A2:A6 — or maybe even B1:C1. But the Product name actually refers to B2:C6. If the
upper-left cell of the selection contains text and you choose the Top row and Left
column options, Excel uses that text for the name of the entire set of data — excluding the
top row and left column. So, before you accept the names that Excel creates, take a
minute to make sure that they refer to the correct ranges.