Microsoft Office Tutorials and References
In Depth Information
Working with formulas
The following are some additional facts to keep in mind when working with worksheet-only
and workbook-level names:
Worksheet-only names do not appear in the Name box on the formula bar in
worksheets other than the one in which you define them.
When you select a cell or range to which you have assigned a worksheet-only name,
the name appears in the Name box on the formula bar, but you have no way of
knowing its scope. You can consider adding clues for your own benefit, such as
including the word Sheet as part of all worksheet-only names when you define them.
If a worksheet contains a duplicate workbook-level and worksheet-only name, the
worksheet-level name takes precedence over the workbook-level name on the
worksheet where it lives, rendering the workbook-level version of the name useless on
You can use a worksheet-only name in formulas on other worksheets by preceding
the name with the name of the worksheet followed by an exclamation point. For
example, you could type the formula =Sheet1!TestSheetName in a cell on Sheet3.
You can’t change the scope of an existing name.
Creating names semiautomatically
You can click the Create From Selection button on the Formulas tab to name several
adjacent cells or ranges at once, using row labels, column labels, or both. When you select
this command, Excel displays the Create Names From Selection dialog box shown in
Figure 12-14 Use the Create Names From Selection dialog box to name several cells or ranges
at once using labels.