Microsoft Office Tutorials and References
In Depth Information
This formula works fine, but its purpose is not at all clear. To help clarify the formula, you can define a de-
scriptive name for the daily sales range and another descriptive name for cell C1. Assume, for this example, that
the range B2:B200 is named DailySales and cell C1 is named CommissionRate. You can then rewrite the for-
mula to use the names instead of the actual range addresses:
As you can see, using names instead of cell references makes the formula self-documenting and much easier to
Using named cells and ranges offers a number of advantages:
• Names make your formulas more understandable and easier to use, especially for people who didn't create
the worksheet. Obviously, a formula such as =Income-Taxes is more intuitive than =D20-D40.
• When entering formulas, a descriptive range name (such as Total_Income ) is easier to remember than a cell
address (such as AC21). And typing a name is less likely to result in an error than entering a cell or range
• You can quickly navigate to areas of your worksheet either by using the Name box, located at the left side of
the Formula bar (click the arrow for a drop-down list of defined names), or by choosing Home ⇒ Editin-
g ⇒ Find & Select ⇒ Go To (or press F5) and specifying the range name.
• When you select a named cell or range, its name appears in the Name box. This is a good way to verify that
your names refer to the correct cells.
• You may find that creating formulas is easier if you use named cells. You can easily insert a name into a for-
mula by using the drop-down list that's displayed when you enter a formula. Or, press F3 to get a list of
• Macros are easier to create and maintain when you use range names rather than cell addresses.
A Name's Scope
Before I explain how to create and work with names, it's important to understand that all names have a scope. A
name's scope defines where you can use the name. Names are scoped at either of two levels:
• Workbook-level names: Can be used in any worksheet in the workbook. This is the default type of range
• Worksheet-level names: Can be used only in the worksheet in which they are defined, unless they are pre-
ceded with the worksheet's name. A workbook may contain multiple worksheet-level names that are
identical. For example, three sheets can all have a cell named Region _ Total.
Most of the time, you will use workbook-level names. For some situations, though, using worksheet-level
names makes sense. For example, you might use a workbook to store monthly data, one worksheet per month.
You start out with a worksheet named January, and you create worksheet-level names on that sheet. Then, rather
than create a new sheet called February, you copy the January sheet and name it February. All the worksheet-
level names from the January sheet are reproduced as worksheet-level names on the February sheet.