Microsoft Office Tutorials and References
In Depth Information
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 in-
stead 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 Selec-
tion dialog box works counterintuitively. Figure 3-6 shows a small table of text and val-
ues. 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:A7 — or maybe even B1:C1.
But the Product name actually refers to B2:C7. If the upper-left cell of the selection con-
tains 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, be-
fore you accept the names that Excel creates, take a minute to make sure that they refer
to the correct ranges.