Microsoft Office Tutorials and References
In Depth Information
Working with formulas
dialog box, the Refers To reference will not be the same as the one you typed because
Excel adjusts it relative to the active cell. Also, if you do not enter an equal sign preceding
the reference, Excel interprets the definition as text. For example, if you type D20 instead
of =D20 , the Refers To text box displays the text constant ="D20" as the definition of the
When you work with tables created using the new table features in Excel, some names are
created automatically and others are implied. If this sounds intriguing, see “Using
structured references” later in this chapter.
Although you can edit name references directly using the Refers To text box in the Name
Manager dialog box, it is preferable to click the Edit button at the top of the dialog box.
Doing so opens the Edit Name dialog box, which is otherwise the same as the New Name
dialog box shown in Figure 12-11 and offers additional opportunities to change the name
and to add a comment.
In the Edit Name dialog box, you can change cell references in the Refers To text box either
by typing or by selecting cells on the worksheet. When you click OK in the Edit Name
dialog box, the Name Manager dialog box reappears, displaying the updated name definition.
Clicking the New button in the Name Manager dialog box (as you might predict) displays
the New Name dialog box; clicking the Delete button removes all selected names from the
list in the Name Manager dialog box. Keep in mind that when you delete a name, any
formula in the worksheet referring to that name returns the error value #NAME? .
Rules for naming
The following rules apply when you name cells and ranges in Excel:
● You must begin all names with a letter, a backslash (\), or an underscore (_). You
cannot use any other symbol.
● You can also use numbers, periods, and underscore characters.
● You cannot use spaces; Excel translates blank spaces in labels to underscores in
● You can’t use names that resemble cell references (for example, AB$5 or R1C7).
● You can use single letters, with the exception of the letters R and C (uppercase
and lowercase), as names.
A name can contain 255 characters. Excel does not distinguish between uppercase and
lowercase characters in names. For example, if you create the name Tax and then create
the name TAX in the same workbook, the second name overwrites the first.