Microsoft Office Tutorials and References
In Depth Information
After you define the name, you can use it in your formulas. For example, the following formula returns the sum
of the values in the range named DataCube:
Multisheet names do not appear in the Name box or in the Go To dialog box (which ap-
pears when you choose Home ⇒ Editing ⇒ Find & Select & Go To). In other words, Excel
enables you to define the name, but it doesn't give you a way to automatically select
the cells to which the name refers. However, multisheet names do appear in the For-
mula AutoComplete drop-down list that appears when you type a formula.
If you insert a new worksheet into a workbook that uses multisheet names, the multisheet names include the
new worksheet — as long as the sheet resides between the first and last sheet in the name's definition. In the
preceding example, a worksheet inserted between Sheet1 and Sheet2 will be included in the DataCube range.
However, a worksheet inserted before Sheet1 or after Sheet3 will not be included.
If you delete the first or last sheet included in a multisheet name, Excel changes the name's range in the Refers
To field automatically. In the preceding example, deleting Sheet1 causes the Refers To range of DataCube to
change to this:
Multisheet names can be scoped at the workbook level or worksheet level. If it's a worksheet-level name, the
name will be valid only on the sheet that it's scoped to.
Working with Range and Cell Names
After you create range or cell names, you can work with them in a variety of ways. This section describes how
to perform common operations with range and cell names.
Creating a list of names
If you create a large number of names, you may need to know the ranges that each name refers to, particularly if
you're trying to track down errors or document your work. You might want to create a list of all names (and
their corresponding addresses) in the workbook. The Name Manager dialog box doesn't provide this option, but
there is a way to do it.
To create a list of names, first move the cell pointer to an empty area of your worksheet. (The two-column name
list, created at the active cell position, overwrites any information at that location.) Use the Formulas ⇒ Defined
Names ⇒ Use in Formula ⇒ Paste Names command (or press F3). Excel displays the Paste Name dialog box that
lists all the defined names. To paste a list of names, click the Paste List button. Figure 3-9 shows the Paste
Name dialog box, plus an example of a list it generates.