Microsoft Office Tutorials and References
In Depth Information
Unfortunately, you can’t automatically unapply names. In other words, if a formula uses
a name, you can’t convert the name to an actual cell or range reference. Even worse, if
you delete a name that a formula uses, the formula doesn’t revert to the cell or range
address — it simply returns a #NAME? error.
My Power Utility Pak add-in (available by using the coupon in the back of the )
includes a utility that scans all formulas in a selection and automatically replaces names
with their cell addresses.
Excel has a special operator called the intersection operator that comes into play when you’re
dealing with ranges. This operator is a space character. Using names with the intersection
operator makes creating meaningful formulas very easy. For this example, refer to Figure 3-2. If you
enter the following formula into a cell
the result is 7,015 — the intersection of the Qtr_2 range and the South range.
Naming columns and rows
Excel lets you name complete rows and columns. In the preceding figure, the name Qtr_1 is
assigned to the range B3:B6. Alternatively, Qtr_1 could be assigned to all of column B, Qtr_2 to
column C, and so on. You also can do the same horizontally so that North refers to row 3, South
to row 4, and so on.
The intersection operator works exactly as before, but now you can add more regions or quarters
without having to change the existing names.
When naming columns and rows, make sure that you don’t store any extraneous information in
named rows or columns. For example, remember that if you insert a value in cell C7, it is included
in the Qtr_1 range.
A named cell or range normally has a workbook-level scope. In other words, you can use the
name in any worksheet in the workbook.
Another option is to create names that have a worksheet-level scope. To create a
worksheetlevel name, define the name by preceding it with the worksheet name followed by an
exclamation point: for example, Sheet1!Sales. If the name is used on the sheet in which it is designed, you
can omit the sheet qualifier when you reference the name. You can, however, reference a
worksheet-level name on a different sheet if you precede the name with the sheet qualifier.