Microsoft Office Tutorials and References
In Depth Information
Recall from the earlier section on scope (“A Name's Scope”) that when you write a formula that uses a
worksheet-level name on the sheet in which it's defined, you don't need to include the worksheet name in the
range name. If you use the name in a formula on a different worksheet, however, you must use the entire name
(sheet name, exclamation point, and name). For example, if the name UnitsSold represents a worksheet-level
name defined on Sheet1, the following formula (on a sheet other than Sheet1) calculates the total of the Unit-
When composing a formula and you need to insert a name, you have three options:
• Start typing the name, and it will appear in the Formula AutoComplete drop-down list, along with a list of
worksheet functions. To use Formula AutoComplete, begin typing the defined name until it is highlighted
on the list and then press Tab to complete the entry. Or, use the down arrow key ( ) and press Tab to select a
name from the list.
• Press F3 to display the Paste Name dialog box. This dialog box displays a list of defined names. Just select
the name and click OK, and it's inserted into your formula.
• Choose Formulas ⇒ Defined Names ⇒ Use In Formula. This command also displays a list of defined names.
Click a name, and it's inserted into your formula. Figure 3-10 shows an example.
Figure 3-10: One way of inserting a name into a formula.