Microsoft Office Tutorials and References
In Depth Information
Working with formulas
Type a name for the cell or range, and then press Enter. We used TestName in this
Keep the following in mind when using names in formulas:
The Name box usually displays the address of the selected cell. If you named the
selected cell or range, the name takes precedence over the address, and Excel
displays it in the Name box.
● When you define a name for a range of cells, the range name does not appear in the
Name box unless you select the same range.
When you click the Name box and select a name, the cell selection switches to the
● If you type a name in the Name box that you already defined, Excel switches the
selection to the named range instead of redefining the name.
● When you define a name, the stored definition is an absolute cell reference that
includes the worksheet name. For example, when you define the name TestName for
cell C3 in Sheet1, the actual name definition is recorded as Sheet1!$C$3.
For more information about absolute references, see “Understanding relative, absolute, and
mixed references” earlier in this chapter.
Defining and managing names
Instead of coming up with new names for cells and ranges, you can simply use existing text
labels to create names. Click the Define Name button on the Formulas tab on the ribbon to
display the New Name dialog box shown in Figure 12-11. In this example, we selected cells
B4:E4 before clicking the Define Name button, and Excel correctly surmised that the label
Region_1 was the most likely name candidate for that range. If you are happy using the
adjacent label as a name, just press Enter to define the name, or you can first add a note in
the Comment box if you want to provide some helpful documentation.
You can, of course, define a name without first selecting a cell or range on the worksheet.
For example, in the New Name dialog box, type Test2 in the Name text box, and then type
=D20 in the Refers To text box. Click OK to add the name, which also closes the New Name
dialog box. To see a list of the names you defined, click the Name Manager button on the
Formulas tab. The Name Manager dialog box appears, as shown in Figure 12-12.