Chapter 3: Working with Names

In This Chapter

• An overview and the advantages of using names in Excel

• The difference between workbook- and worksheet-level names

• Working with the Name Manager dialog box

• Shortcuts for creating cell and range names

• How to create names that extend across multiple worksheets

• How to perform common operations with range and cell names

• How Excel maintains cell and range names

• Potential problems that may crop up when you use names

• The secret behind names, and examples of named constants and named formulas

• Examples of advanced techniques that use names

Most intermediate and advanced Excel users are familiar with the concept of named cells or ranges. Naming cells and ranges is an excellent practice and offers several important advantages. As you'll see in this chapter, Excel supports other types of names — and the power of this concept may surprise you.

What's in a Name?

You can think of a name as an identifier for something in a workbook. This “something” can consist of a cell, a range, a chart, a shape, and so on.

Although you can give a name to any object in Excel, this chapter focuses exclusively on cell and range names (which are handled differently than other types of names).

If you provide a name for a range, you can then use that name in your formulas. For example, suppose your worksheet contains daily sales information stored in the range B2:B200. Further, assume that cell C1 contains a sales commission rate. The following formula returns the sum of the sales, multiplied by the commission rate:

=SUM(B2:B200)*C1

This formula works fine, but its purpose is not at all clear. To help clarify the formula, you can define a descriptive 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 formula to use the names instead of the actual range addresses:

=SUM(DailySales)*CommissionRate

As you can see, using names instead of cell references makes the formula self-documenting and much easier to understand.

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 address.

• 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⇒Editing⇒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 formula by using the drop-down list that's displayed when you enter a formula. Or, press F3 to get a list of defined names.

• 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 name.

• Worksheet-level names: Can be used only in the worksheet in which they are defined, unless they are preceded 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.

Referencing names

You can refer to a workbook-level name just by using its name from any sheet in the workbook. For worksheet-level names, you must precede the name with the name of the worksheet unless you're using it on its own worksheet.

For example, assume that you have a workbook with two sheets: Sheet1 and Sheet2. In this workbook, you have Total_Sales (a workbook-level name), North_Sales (a worksheet-level name on Sheet1), and South_Sales (a worksheet-level name on Sheet2). On Sheet1 or Sheet2, you can refer to Total_Sales by simply using this name:

=Total_Sales

If you're on Sheet1 and you want to refer to North_Sales, you can use a similar formula because North_Sales is defined on Sheet1:

=North_Sales

However, if you want to refer to South_Sales on Sheet1, you'll need to do a little more work. Sheet1 can't “see” the name South_Sales because it's defined on another sheet. Sheet1 can see workbook-level names and worksheet-level names only as defined on Sheet1. To refer to South_Sales on Sheet1, prefix the name with the worksheet name and an exclamation point:

=Sheet2!South_Sales

If your worksheet name contains a space, enclose the worksheet name in single quotes when referring to a name defined on that sheet:

='My Sheet'!My_Name

Only the worksheet-level names on the current sheet appear in the Name box. Similarly, only worksheet-level names on the current sheet appear in the list under Formulas⇒Defined Names⇒Use in Formulas.

Referencing names from another workbook

Chapter 2 describes how to use links to reference cells or ranges in other workbooks. The same rules apply when using names defined in another workbook.

For example, the following formula uses a range named MonthlySales, a workbook-level name defined in a workbook named Annual Budget.xlsx (which is assumed to be open):

=AVERAGE(‘Annual Budget.xlsx'!MonthlySales)

If the name MonthlySales is a worksheet-level name on Sheet1, the formula looks like this:

=AVERAGE(‘[Annual Budget.xlsx]Sheet1'!MonthlySales)

If you use the pointing method to create such formulas, Excel takes care of the details automatically.

Conflicting names

Using worksheet-level names can be a bit confusing because Excel lets you define worksheet-level names even if the workbook contains the same name as a workbook-level name. In such a case, the worksheet-level name takes precedence over the workbook-level name but only in the worksheet in which you defined the sheet-level name.

For example, you can define a workbook-level name of Total for a cell on Sheet1. You can also define a worksheet-level name of Sheet2!Total. When Sheet2 is active, Total refers to the worksheet-level name. When any other sheet is active, Total refers to the workbook-level name. Confusing? Probably. To make your life easier, I recommend that you simply avoid using the same name at the workbook and worksheet levels.

One way you can avoid this type of conflict is to adopt a naming convention when you create names. By using a naming convention, your names will tell you more about themselves. For instance, you could prefix all your workbook-level names with wb and your worksheet-level names with ws. With this method, you'll never confuse wbTotal with wsTotal.

The Name Manager

Now that you understand the concept of scope, you can start creating and using names. Excel has a handy feature for maintaining names called the Name Manager, shown in Figure 3-1.

Figure 3-1: The Name Manager dialog box.

To display the Name Manager, choose Formulas⇒Defined Names⇒Name Manager. Within this dialog box, you can view, create, edit, and delete names. In the Name Manager main window, you can see the current value of the name, what the name refers to, the scope of the name, and any comments that you've written to describe the name. The names are sortable, and the columns are resizable, allowing you to see your names in many different ways. If you use a lot of names, you can also apply some predefined filters to view only the names that interest you.

Note that the Name Manager dialog box is resizable. Drag the lower-right corner to make it wider or taller.

Creating names

The Name Manager contains a New button for creating new names. The New button displays the New Name dialog box, as shown in Figure 3-2.

Figure 3-2: The New Name dialog box.

In the New Name dialog box, you name the name, define its scope and what it refers to, and (optionally) add any comments about the name to help yourself and others understand its purpose. The Refers To field displays the range address that was selected when you invoked the dialog box. You can change the address displayed by typing or by selecting cells in the worksheet.

Editing names

Clicking the Edit button in the Name Manager displays the Edit Name dialog box, which looks strikingly similar to the New Name dialog box. You can change any property of your name except the scope. If you change the Name field, all the formulas in your workbook that use that name will be updated.

To change the scope of a name, you must delete the name and re-create it. If you're careful to use the same name, your formulas that use that name will still work.

The Edit Name dialog box isn't the only way to edit a name. If the only property that you want to change is the Refers To property, you can do it right in the Name Manager dialog box. At the bottom of the dialog box is the field labeled Refers To. Simply select the name that you'd like to edit in the main window and change the reference in the Refers To field.

If you edit the contents of the Refers To field manually, the status bar displays Point, indicating that you're in point mode. If you try to use keys such as the arrows, Home, or End, you'll find that you're navigating around the worksheet rather than editing the Refers To text. This is a constant source of frustration to many Excel users. But there's a simple solution. To switch from point mode to edit mode, press F2 and note that the status bar changes to show Edit.

Deleting names

Clicking the Delete button in the Name Manager permanently removes the selected name from your workbook. Excel warns you first because this action cannot be undone.

Unfortunately, Excel does not replace deleted names with the original cell references. Any formulas that use a name that you delete will display the #NAME? error.

Shortcuts for Creating Cell and Range Names

Excel provides a few additional ways to create names for cells and ranges other than the Name Manager. I discuss these methods in this section, along with some other relevant information that pertains to names.

The New Name dialog box

You can access the New Name dialog box directly by choosing Formulas⇒Defined Names⇒Define Name. The New Name dialog box that's displayed is identical in form and function to the one from the New button on the Name Manager dialog box.

A single cell or range can have any number of names. I can't think of a good reason to use more than one name, but Excel does permit it. If a cell or range has multiple names, the Name box always displays the name that's first alphabetically when you select the cell or range.

A name can also refer to a noncontiguous range of cells. You can select a noncontiguous range by pressing Ctrl while you select various cells or ranges with the mouse.

Creating names using the Name box

A faster way to create a name for a cell or range is to use the Name box. The Name box is the drop-down list box to the left of the Formula bar. Select the cell or range to name, click the Name box, type the name, and then press Enter to create the name. If a name already exists, you can't use the Name box to change the range to which that name refers. Attempting to do so simply selects the original range. You must use the Name Manager dialog box to change the reference for a name.

When you type a name in the Name box, you must press Enter to actually record the name. If you type a name and then click in the worksheet, Excel won't create the name.

To create a worksheet-level name using the Name box, precede the name with the active worksheet's name, followed by an exclamation point. For example, to create the name Total as a worksheet-level name for Sheet1, type this into the Name box and press Enter:

Sheet1!Total

If the worksheet name contains any spaces, enclose the sheet name in single quote, like this:

‘Summary Sheet'!Total

Because the Name box works only on the currently selected range, typing a worksheet name other than the active worksheet results in an error.

If you type an invalid name (such as May21, which is a cell address), Excel activates that address (and doesn't warn you that the name is not valid). If the name you type includes an invalid character, Excel displays an error message.

The Name box serves double-duty by also providing a quick way to activate a named cell or range. To select a named cell or range, click the Name box and choose the name, as shown in Figure 3-3. This selects the named cell or range. Oddly, the Name box does not have a keyboard shortcut. In other words, you can't access the Name box by using the keyboard; you must use the mouse. After you click the Name box, however, you can use the direction keys and Enter to choose a name.

Notice that the Name box is resizable. To make the Name box wider, just click the three vertical dots icon to the right of the Name box, and drag it to the right. The Name box shares space with the Formula bar, so if you make the Name box wider, the Formula bar gets narrower.

Figure 3-3: The Name box provides a quick way to select a named cell or range.

Creating names from text in cells

You may have a worksheet containing text that you want to use for names of adjacent cells or ranges. Figure 3-4 shows an example of such a worksheet. In this case, you might want to use the text in column A to create names for the corresponding values in columns B through M. Excel makes this very easy to do.

Figure 3-4: Excel makes it easy to create names by using text in adjacent cells.

To create names by using adjacent text, start by selecting the name text and the cells that you want to name. (These can consist of individual cells or ranges of cells.) The names must be adjacent to the cells that you're naming. (A multiple selection is allowed.) Then choose Formulas⇒Defined Names⇒Create from Selection (or Ctrl+Shift+F3). Excel displays the Create Names from Selection dialog box, as shown in Figure 3-5.

The check marks in this dialog box are based on Excel's analysis of the selected range. For example, if Excel finds text in the first row of the selection, it proposes that you create names based on the top row. If it finds text in the first column, it proposed to create names based on those cells. If Excel doesn't guess correctly, you can change the check boxes. Click OK, and Excel creates the names.

Note that when Excel creates names using text in cells, it does not include those text cells in the named range.

Figure 3-5: The Create Names from Selection dialog box.

If the text in a cell would result in an invalid name, Excel modifies the name to make it valid. For example, if a cell contains the text Net Income (which is invalid for a name because it contains a space), Excel converts the space to an underscore character and creates the name Net_Income. If Excel encounters a value or a formula instead of text, however, it doesn't convert it to a valid name. It simply doesn't create a name.

Double-check the names that Excel creates. Sometimes, the Create Names from Selection dialog box works counterintuitively. Figure 3-6 shows a small table of text and values. Select the entire table, choose Formulas⇒Defined Names⇒Create from Selection, and then accept Excel's suggestions (Top row and Left column options). What range does Product refer to? You might expect it to refer to A2:A7 — or maybe even B1:C1. But the Product name actually refers to B2:C7. If the upper-left cell of the selection contains text, and you choose the Top row and Left column options, Excel uses that text for the name of the entire set of data — excluding the top row and left column. So, before you accept the names that Excel creates, take a minute to make sure that they refer to the correct ranges.

Figure 3-6: Using the Create Names from Selection dialog box to create names from the data in this table may produce unexpected results.

Naming entire rows and columns

Sometimes it makes sense to name an entire row or column. Often, a worksheet is used to store information that you enter over a period of time. The sheet in Figure 3-7 is an example of such a worksheet. If you create a name for the data in column B, you need to modify the name's reference each day you add new data. The solution is to name the entire column.

Figure 3-7: This worksheet, which tracks daily sales, uses a named range that consists of an entire column.

For example, you might name column B as DailySales. This range is on Sheet3; its reference would appear like this:

=Sheet3!$B:$B

To define a name for an entire column, select the column by clicking the column letter. Then type the name in the Name box and press Enter (or use the New Name dialog box to create the name).

After defining the name, you can use it in a formula. The following formula, for example, returns the sum of all values in column B:

=SUM(DailySales)

Names created by Excel

Excel creates some names on its own. For example, if you set a print area for a sheet, Excel creates the name Print_Area. If you set repeating rows or columns for printing, you also have a worksheet-level name called Print_Titles. When you execute a query that returns data to a worksheet, Excel assigns a name to the data that is returned. Also, many of the add-ins that ship with Excel create hidden names. (See the “Hidden names” sidebar.)

You can modify the reference for any of the names that Excel creates automatically, but make sure that you understand the consequences.

Creating Multisheet Names

Names can extend into the third dimension; in other words, they can extend across multiple worksheets in a workbook. You can't simply select the multisheet range and type a name in the Name box, however. You must use the New Name dialog box to create a multisheet name. The syntax for a multisheet reference is the following:

FirstSheet:LastSheet!RangeReference

In Figure 3-8, a multisheet name, DataCube, defined for A1:E5, extends across Sheet1, Sheet2, and Sheet3.

You can, of course, simply type the multisheet range reference in the Refers To field. If you want to create the name by pointing to the range, though, you'll find it a bit tricky. Even if you begin by selecting a multisheet range, Excel does not use this selected range address in the New Name dialog box.

Figure 3-8: Create a multisheet name.

Follow this step-by-step procedure to create a name called DataCube that refers to the range A1:E5 across three worksheets (Sheet1, Sheet2, and Sheet3):

1. Activate Sheet1.

2. Choose Formulas⇒Defined Names⇒Define Name to display the New Name dialog box.

3. Type DataCube in the Name field.

4. Highlight the range reference in the Refers To field and press Delete to delete the range reference.

5. Click the sheet tab for Sheet1.

6. Press Shift and click the sheet tab for Sheet3.

At this point, the Refers To field contains the following:

='Sheet!Sheet3'!

7. Select the range A1:E5 in Sheet1 (which is still the active sheet).

The following appears in the Refers To field:

='Sheet1:Sheet3'!$A$1:$E$5

8. Because the Refers To field now has the correct multisheet range address, click OK to close the New Name dialog box.

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:

=SUM(DataCube)

Multisheet names do not appear in the Name box or in the Go To dialog box (which appears 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 Formula 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:

='Sheet2:Sheet3'!$A$1:$E$5

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.

The list of names does not include hidden names or worksheet-level names that appear in sheets other than the active sheet.

Figure 3-9: Use the Paste Name dialog box to create a list of names.

The list of names pasted to your worksheet occupies two columns. The first column contains the names, and the second column contains the corresponding range addresses. The range addresses in the second column consist of text strings that look like formulas. You can convert such a string to an actual formula by editing the cell. Press F2 and then press Enter. The string then converts to a formula. If the name refers to a single cell, the formula displays the cell's current value. If the name refers to a range, the formula may return a #VALUE! error, or, in the case of multisheet names, a #REF! error.

I discuss formula errors such as #VALUE! and #REF! in Chapter 22.

Using names in formulas

After you define a name for a cell or range, you can use it in a formula. For example, the following formula calculates the sum of the values in the range named UnitsSold:

=SUM(UnitsSold)

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 UnitsSold range:

=SUM(Sheet1!UnitsSold)

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.

If you use a nonexistent name (or a name that's scoped to a different worksheet) in a formula, Excel displays a #NAME? error, indicating that it cannot find the name you are trying to use. Often, this means that you misspelled the name or that the name was deleted.

Using the intersection operators with names

Excel's range intersection operator is a single space character. The following formula, for example, displays the sum of the cells at the intersection of two ranges: B1:C20 and A8:D8:

=SUM(B1:C20 A8:D8)

The intersection of these two ranges consists of two cells: B8 and C8.

The intersection operator also works with named ranges. Figure 3-11 shows a worksheet containing named ranges that correspond to the row and column labels. For example, January refers to B2:B10, and Arizona refers to B2:M2. The following formula returns the contents of the cell at the intersection of the May range and the Utah range:

=May Utah

Figure 3-11: The formula in cell C3 uses the intersection operator.

Using a space character to separate two range references or names is known as explicit intersection because you explicitly tell Excel to determine the intersection of the ranges.

Excel can also perform implicit intersections, which occur when Excel chooses a value from a multicell range based on the row or column of the formula that contains the reference. An example should clear this up. Figure 3-12 shows a worksheet that contains a range (C3:C14) named MonthNames. Cell E8 contains the simple formula shown here:

=MonthNames

Notice that cell E8 displays the value from MonthNames that corresponds to the formula's row. Similarly, if you enter the same formula into any other cell in rows 3 through 14, the formula displays the corresponding value from MonthNames. Excel performs an implicit intersection using the MonthNames range and the row that contains the formula. It's as if the following formula is being evaluated:

=MonthNames 8:8

Figure 3-12: Range C3:C14 in this worksheet is named MonthNames. Cell E8 demonstrates an implicit intersection.

If you enter the formula in a cell that's in a row not occupied by MonthNames, the formula returns an error because the implicit intersection returns nothing.

By the way, implicit intersections are not limited to named ranges. In the preceding example, you get the same result if cell E8 contains the following formula (which doesn't use a named range):

=$C$3:$C$14

If you use MonthNames as an argument for a function, implicit intersection applies only if the function argument is interpreted as a single value. For example, if you enter this formula in cell E6, implicit intersection works, and the formula returns 5 (the number of characters in “April”):

=LEN(MonthNames)

But if you enter this formula, implicit intersection does not apply, and the formula returns 12, the number of cells in the MonthNames range:

=COUNTA(MonthNames)

Using the range operator with names

You can also use the range operator, which is a colon (:), to work with named ranges. Refer to Figure 3-11. For example, this formula returns the sum of the values in the nine-cell range that extends from Arizona Jan (cell B2) through Idaho March (cell D4):

=SUM((Arizona Jan):(Idaho Mar))

Referencing a single cell in a multicell named range

You can use Excel's INDEX function to return a single cell from a multicell named range. Assume that range A1:A10 is named DataRange. The following formula displays the fourth value (the value in A4) in DataRange:

=INDEX(DataRange,4)

The second and third arguments for the INDEX function are optional — although at least one of them must always be specified. The second argument (used in the preceding formula) specifies the row offset within the DataRange range.

If DataRange consists of multiple cells in a single row (for example, A1:J1), use a formula like the following one to return the fourth element in the range. This formula omits the second argument for the INDEX function, but uses the third argument that specifies the column offset with the DataRange range:

=INDEX(DataRange,,4)

If the range consists of multiple rows and columns, use both the second and third arguments for the INDEX function. For example, if DataRange is defined as A1:J10, this formula returns the value in the fourth row and fifth column of the named range:

=INDEX(DataRange,4,5)

Applying names to existing formulas

When you create a name for a cell or range, Excel does not scan your formulas automatically and replace the cell references with your new name. You can, however, tell Excel to “apply” names to a range of formulas.

Select the range that contains the formulas that you want to modify so they will use names rather than cell references. Then choose Formulas⇒Defined Names⇒Define Name⇒Apply Names. The Apply Names dialog box appears, as shown in Figure 3-13. In the Apply Names dialog box, select which names you want applied to the formulas. Only those names that you select will be applied to the formulas.

Figure 3-13: The Apply Names dialog box.

To apply names to all the formulas in the worksheet, select a single cell before you display the Apply Names dialog box.

The Ignore Relative/Absolute check box controls how Excel substitutes the range name for the actual address. A cell or range name is usually defined as an absolute reference. If the Ignore Relative/Absolute check box is selected, Excel applies the name only if the reference in the formula matches exactly. In most cases, you will want to ignore the type of cell reference when applying names.

If the Use Row and Column Names check box is selected, Excel takes advantage of the intersection operator when applying names. Excel uses the names of row and column ranges that refer to the cells if it cannot find the exact names for the cells. Excel uses the intersection operator to join the names. Clicking the Options button displays some additional options that are available only when you select the Use Row and Column Names check box.

Applying names automatically when creating a formula

When you insert a cell or range reference into a formula by pointing, Excel automatically substitutes the cell or range name if it has one.

In some cases, this feature can be very useful. In other cases, it can be annoying; you may prefer to use an actual cell or range reference instead of the name. For example, if you plan to copy the formula, the range references won't adjust if the reference is a name rather than an address. Unfortunately, you cannot turn off this feature. If you prefer to use a regular cell or range address, you need to type the cell or range reference manually (don't use the pointing technique).

Unapplying names

Excel does not provide a direct method for unapplying names. In other words, you cannot replace a name in a formula with the name's actual cell reference automatically. However, you can take advantage of a trick described here (which works only for workbook-level names). You need to (temporarily) change Excel's Transition Formula Entry option so that it emulates Lotus 1-2-3.

1. Choose File⇒Options and then click the Advanced tab in the Excel Options dialog box.

2. Under the Lotus Compatibility Settings section, place a check mark next to Transition Formula Entry and then click OK.

3. Select a cell that contains a formula that uses one or more cell or range names.

4. Press F2 and then press Enter.

In other words, edit the cell but don't change anything.

5. Repeat Steps 3 and 4 for other cells that use range names.

6. Go back to the Options dialog box and remove the check mark from the Transition Formula Entry check box.

You'll find that the edited cells use relative range references rather than names.

This trick is not documented, and it might not work in all cases, so make sure that you check the results carefully.

Names with errors

If you delete the rows or columns that contain named cells or ranges, the names will not be deleted (as you might expect). Rather, each name will contain an invalid reference. For example, if cell A1 on Sheet1 is named Interest and you delete row 1 or column A, Interest then refers to =Sheet1!#REF! (that is, an erroneous reference). If you use Interest in a formula, the formula displays #REF.

To get rid of this erroneous name, you must delete the name manually using the Delete button in the Name Manager dialog box. Or, you can redefine the name so it refers to a valid cell or range.

The Name Manager allows you to filter the names that it displays using predefined filters. One of the filters provided, Names with Errors, shows only those names that contain errors, which enables you to quickly locate problematic names.

Viewing named ranges

As you probably know, you can change the zoom factor of a worksheet by using the slider on the right side of the status bar (or, use commands in the View⇒Zoom group). When you zoom a worksheet to 39 percent or smaller, you see a border around the named ranges with the name displayed in blue letters, as shown in Figure 3-14. The border and name do not print; they simply help you visualize the named ranges on your sheet.

Figure 3-14: Excel displays range names when you zoom a sheet to 39 percent or less.

Using names in charts

When you create a chart, each data series has an associated SERIES formula. The SERIES formula contains references to the ranges used in the chart. If you have a defined range name, you can edit a chart's SERIES formula and replace the range reference with the name. After doing so, the chart series will adjust if you change the definition for the name.

See Chapter 17 for additional information about charts.

How Excel Maintains Cell and Range Names

After you create a name for a cell or range, Excel automatically maintains the name as you edit or modify the worksheet. The following examples assume that Sheet1 contains a workbook-level name (MyRange) that refers to the following nine-cell range:

=Sheet1!$C$3:$E$5

Inserting a row or column

When you insert a row above the named range or insert a column to the left of the named range, Excel changes the range reference to reflect its new address. For example, if you insert a new row 1, MyRange then refers to =Sheet1!$C$4:$E$6.

If you insert a new row or column within the named range, the named range expands to include the new row or column. For example, if you insert a new column to the left of column E, MyRange then refers to =Sheet1!$C$3:$F$5.

Deleting a row or column

When you delete a row above the named range or delete a column to the left of the named range, Excel adjusts the range reference to reflect its new address. For example, if you delete row 1, MyRange refers to =Sheet1!$B$3:$D$5.

If you delete a row or column within the named range, the named range adjusts accordingly. For example, if you delete column D, MyRange then refers to =Sheet1!$C$3:$D$5.

If you delete all rows or all columns that make up a named range, the named range continues to exist, but it contains an error reference. For example, if you delete columns C, D, and E, MyRange then refers to =Sheet1!#REF!. Any formulas that use the name also return errors.

Cutting and pasting

When you cut and paste an entire named range, Excel changes the reference accordingly. For example, if you move MyRange to a new location beginning at cell A1, MyRange then refers to =Sheet1!$A$1:$C$3. Cutting and pasting only a part of a named range does not affect the name's reference.

Potential Problems with Names

Names are great, but they can also cause some problems. This section contains information that you should remember when you use names in a workbook.

Name problems when copying sheets

Excel lets you copy a worksheet within the same workbook or to a different workbook. Focus first on copying a sheet within the same workbook. If the copied sheet contains worksheet-level names, those names will also be present on the copy of the sheet, adjusted to use the new sheet name. Usually, this is exactly what you want to happen. However, if the workbook contains a workbook-level name that refers to a cell or range on the sheet that's copied, that name will also be present on the copied sheet. However, it will be converted to a worksheet-level name! That is usually not what you want to happen.

Consider a workbook that contains one sheet (Sheet1). This workbook has a workbook-level name (BookLevel) for cell A1 and a worksheet-level name (Sheet1!SheetLevel) for cell A2. If you make a copy of Sheet1 within the workbook, the new sheet is named Sheet1 (2). You'll find that, after copying the sheet, the workbook contains four names, as shown in Figure 3-15. The new sheet has two worksheet-level names.

Figure 3-15: Copying a worksheet creates duplicated names.

This proliferation of names when copying a sheet is not only confusing; it can also result in errors that can be difficult to identify. In this case, typing the following formula on the copied sheet displays the contents of cell A1 in the copied sheet:

=BookLevel

In other words, the newly created worksheet-level name (not the original workbook-level name) is being used. This is probably not what you want.

If you copy the worksheet from a workbook containing a name that refers to a multisheet range, you also copy this name. A #REF! error appears in its Refers To field.

When you copy a sheet to a new workbook, all the names in the original workbook that refer to cells on the copied sheet are also copied to the new workbook. These include both workbook-level and worksheet-level names.

Copying and pasting cells from one sheet to another does not copy names, even if the copied range contains named cells.

Bottom line? You must use caution when copying sheets from a workbook that uses names. After copying the sheet, check the names and delete those that you didn't intend to be copied.

Name problems when deleting sheets

When you delete a worksheet that contains cells used in a workbook-level name, you'll find that the name is not deleted. The name remains with the workbook, but it contains an erroneous reference in its Refers To definition.

Figure 3-16 shows the Name Manager dialog box that displays an erroneous name. The workbook originally contained a sheet named Sheet2, which had a named range (a workbook-level name, MyRange) for A1:E12. After deleting Sheet2, the name MyRange still exists in the workbook, but the Refers To field displays the following:

=#REF!$A$1:$E$12

As far as I can tell, keeping erroneous names in a workbook doesn't cause any harm, but it's still good practice to delete or correct all names that contain an erroneous reference.

Figure 3-16: Deleting the sheet that contains the range named MyRange causes an erroneous reference.

The Secret to Understanding Names

Excel users often refer to named ranges and named cells. In fact, I use these terms frequently throughout this chapter. Technically, this terminology is not quite accurate.

Here's the secret to understanding names: When you create a name, you're actually creating a named formula. Unlike a normal formula, a named formula doesn't exist in a cell. Rather, it exists in Excel's memory.

This is not exactly an earth-shaking revelation, but keeping this “secret” in mind will help you understand the advanced naming techniques that follow.

When you work with the Name Manager dialog box, the Refers To field contains the formula, and the Name field contains the formula's name. The content of the Refers To field always begins with an equal sign, which makes it a formula.

As you can see in Figure 3-17, the workbook contains a name (InterestRate) for cell B1 on Sheet1. The Refers To field lists the following formula:

=Sheet1!$B$1

Whenever you use the name InterestRate, Excel actually evaluates the formula with that name and returns the result. For example, you might type this formula into a cell:

=InterestRate*1.05

Figure 3-17: Technically, the name InterestRate is a named formula, not a named cell.

When Excel evaluates this formula, it first evaluates the formula named InterestRate (which exists only in memory, not in a cell). It then multiplies the result of this named formula by 1.05 and displays the result. This cell formula, of course, is equivalent to the following formula, which uses the actual cell reference instead of the name:

=Sheet1!$B$1*1.05

At this point, you may be wondering whether it's possible to create a named formula that doesn't contain any cell references. The answer comes in the next section.

Naming constants

Consider a worksheet that generates an invoice and calculates sales tax for a sales amount. The common approach is to insert the sales tax rate value into a cell and then use this cell reference in your formulas. To make things easier, you probably would name this cell something like SalesTax.

You can handle this situation another way. Figure 3-18 demonstrates the following steps:

1. Choose Formulas⇒Defined Names⇒Define Name to bring up the New Name dialog box.

2. Type the name (in this case, SalesTax) into the Name field.

3. Click in the Refers To field, delete its contents, and replace it with a simple formula, such as =.075.

4. Click OK to close the New Name dialog box.

Figure 3-18: Defining a name that refers to a constant.

The preceding steps create a named formula that doesn't use any cell references. To try it out, enter the following formula into any cell:

=SalesTax

This simple formula returns .075, the result of the formula named SalesTax. Because this named formula always returns the same result, you can think of it as a named constant. And you can use this constant in a more complex formula, such as the following:

=A1*SalesTax

If you didn't change the scope from the default of Workbook, you can use SalesTax in any worksheet in the workbook.

Naming text constants

In the preceding example, the constant consisted of a numeric value. A constant can also consist of text. For example, you can define a constant for a company's name. You can use the New Name dialog box to create the following formula named MS:

=”Microsoft Corporation”

Then you can use a cell formula, such as the following:

=”Annual Report: “&MS

This formula returns the text Annual Report: Microsoft Corporation.

Names that do not refer to ranges do not appear in the Name box or in the Go To dialog box (which appears when you press F5). This makes sense because these constants don't reside anywhere tangible. They do appear in the Paste Names dialog box and in the Formula AutoComplete drop-down list, however, which does make sense because you'll use these names in formulas.

As you might expect, you can change the value of the constant at any time by accessing the Name Manager dialog box and changing the formula in the Refers To field. When you close the dialog box, Excel uses the new value to recalculate the formulas that use this name.

Although this technique is useful in many situations, changing the value takes some time. Having a constant located in a cell makes it much easier to modify.

Using worksheet functions in named formulas

Figure 3-19 shows another example of a named formula. In this case, the formula is named ThisMonth, and the actual formula is this:

=MONTH(TODAY())

Figure 3-19: Defining a named formula that uses worksheet functions.

The formula in Figure 3-19 uses two worksheet functions. The TODAY function returns the current date, and the MONTH function returns the month number of its date argument. Therefore, you can enter a formula such as the following into a cell and it will return the number of the current month. For example, if the current month is April, the formula returns 4.

=ThisMonth

A more useful named formula would return the actual month name as text. To do so, create a formula named MonthName, defined as follows:

=TEXT(TODAY(),”mmmm”)

See Chapter 5 for more information about Excel's TEXT function.

Now enter the following formula into a cell, and it will return the current month name as text. In the month of April, the formula returns the text April:

=MonthName

Using cell and range references in named formulas

Figure 3-20 shows yet another example of creating a named formula, this time with a cell reference. This formula, named FirstChar, returns the first character of the contents of cell A1 on Sheet1. This formula uses the LEFT function, which returns characters from the left part of a text string. The named formula is the following:

=LEFT(Sheet1!$A$1,1)

Figure 3-20: Defining a named formula that uses a cell reference.

After creating this named formula, you can enter the following formula into a cell. The formula always returns the first character of cell A1 on Sheet1:

=FirstChar

Note that if you insert a new row above row 1, the reference in the FirstChar name will adjust so it shows the first character in cell A2. It's possible to create a name that always refers to a specific cell or range, even if you insert new rows or columns. For example, suppose you want the name FirstChar to always refer to cell A1. You need to modify the formula for FirstChar so that it users the INDIRECT function:

=LEFT(INDIRECT(“$A$1”),1)

After creating this named formula, FirstChar will always return the first character in cell A1, even if you insert new rows or columns. The INDIRECT function, in the preceding formula, lets you specify a cell address indirectly by using a text argument. Because the argument appears in quotation marks, it never changes.

Here's an example that uses a range reference in a named formula. The formula named ColumnACount returns the number of non-empty cells in column A of Sheet1. The formula is this:

=COUNTA(Sheet1!$A:$A)

You can display this count in a cell by using this formula:

=ColumnACount

Note, however, that entering this formula in column A of Sheet1 will result in a circular reference error — just what you would expect.

Notice that the cell references in the preceding named formulas are absolute references. By default, all cell and range references in named formulas use an absolute reference, with the worksheet qualifier. But, as you can see in the next section, overriding this default behavior by using a relative cell reference can result in some very interesting named formulas.

Using named formulas with relative references

As I noted previously, when you use the New Name dialog box to create a named formula that refers to cells or ranges, the Refers To field always uses absolute cell references and the references include the sheet name qualifier. In this section, I describe how to use relative cell and range references in named formulas.

Using a relative cell reference

Begin by following these steps to create a named formula that uses a relative reference:

1. Start with an empty worksheet.

2. Select cell A1.

This step is very important.

3. Choose Formulas⇒Defined Names⇒Define Name.

This brings up the New Name dialog box.

4. Type CellToRight in the Name field.

5. Delete the contents of the Refers To field and type the following formula (don't point to the cell in the sheet):

=Sheet1!B1

6. Click OK to close the New Name dialog box.

7. Type something (anything) into cell B1.

8. Enter this formula into cell A1:

=CellToRight

You'll find that the formula in A1 simply returns the contents of cell B1.

Next, copy the formula in cell A1 down a few rows. Then enter some values in column B. You'll find that the formula in column A returns the contents of the cell to the right. In other words, the named formula (CellToRight) acts in a relative manner.

You can use the CellToRight name in any cell (not just cells in column A). For example, if you enter =CellToRight into cell D12, it returns the contents of cell E12.

To demonstrate that the formula named CellToRight truly uses a relative cell reference, activate any cell other than cell A1 and display the Name Manager dialog box (see Figure 3-21). You'll see that the Refers To field contains a formula that points one cell to the right of the active cell, not A1. For example, if cell B7 is selected when the Name Manager is displayed, the formula for CellToRight appears as follows:

=Sheet1!C7

Figure 3-21: The CellToRight named formula varies, depending on the active cell.

If you use the CellToRight name on a different worksheet, you'll find that it continues to reference the cell to the right — but it's the cell with the same address on Sheet1. This happens because the named formula includes a sheet reference. To modify the named formula so it works on any sheet, follow these steps:

1. Activate cell A1 on Sheet1.

2. Choose Formulas⇒Defined Names⇒Name Manager to bring up the Name Manager dialog box.

3. In the Name Manager dialog box, select the CellToRight item in the list box.

4. In the Refers To field, delete the sheet name (but keep the exclamation point). The formula should look like this:

=!B1

5. Click Close to close the Name Manager dialog box.

After making this change, you'll find that the CellToRight named formula works correctly on any worksheet in the workbook.

Interestingly, the CellToRight named formula works even if you use it in column XFD (the last column, which has no column to its right). The formula displays the value in column A. In other words, it's as if the worksheet wraps around, and column A comes after column XFD.

Using a relative range reference

This example expands upon the previous example and demonstrates how to create a named formula that sums the values in 12 cells directly above a particular cell. To create this named formula, follow these steps:

1. Activate cell A13 (very important).

2. Choose Formulas⇒Defined Names⇒Define Name to bring up the New Name dialog box.

3. Type Sum12Cells in the Name field.

4. Type this formula in the Refers To field:

=SUM(!A1:!A12)

After creating this named formula, you can insert the following formula into any cell (as long as it's in row 13 or a higher number row) in any sheet, and it then displays the sum of the 12 cells directly to the above:

=Sum12Cells

For example, if you enter this formula into cell D40, it returns the sum of the values in the 12-cell range D28:D39.

Figure 3-22 shows an example of this named formula in use.

Figure 3-22: Using the Sum12Cells named formula to sum monthly values.

Note that because cell A1 was the active cell when you defined the named formula, the relative references used in the formula definition are relative to cell A1. Also note that the sheet name was not used in the formula. Omitting the sheet name (but including the exclamation point) causes the named formula to work in any sheet.

If you select cell D40 and then bring up the Name Manager dialog box, you'll see that the Refers To field for the Sum12Cells name displays the following:

=SUM(!D28:!D39)

If you use Sum12Cells named formula in rows 1:12, it causes a circular reference error because of the “wrap-around” effect I noted earlier. For example, if you enter the named formula in cell D3, it's attempting to sum the values in the range D1048567:D2, and that range includes cell D3.

Using a mixed range reference

As I discuss in Chapter 2, a cell reference can be absolute, relative, or mixed. A mixed cell reference consists of either of the following:

• An absolute column reference and a relative row reference (for example, $A1)

• A relative column reference and an absolute row reference (for example, A$1)

As you might expect, a named formula can use mixed cell references. To demonstrate, activate cell B1. Use the New Name dialog box to create a formula named FirstInRow, using this formula definition:

=!$A1

This formula uses an absolute column reference and a relative row reference. Therefore, it always returns a value in column A. The row depends on the row in which you use the formula. For example, if you enter the following formula into cell F12, it displays the contents of cell A12:

=FirstInRow

And, of course, you can create in FirstInColumn named formula. Activate cell A2 and create a FirstInColumn name using this formula:

=!A$1

You can't use the FirstInRow formula in column A, and you can't use the FirstInColumn formula in row 1. In either case, it generates a circular reference — a formula that refers to itself.

Advanced Techniques That Use Names

This section presents several examples of advanced techniques that use names. The examples assume that you're familiar with the naming techniques described earlier in this chapter.

Using the INDIRECT function with a named range

Excel's INDIRECT function lets you specify a cell address indirectly. For example, if cell A1 contains the text C45, this formula returns the contents of cell C45:

=INDIRECT(A1)

Figure 3-23 shows a worksheet with 12 range names that correspond to the month names. For example, January refers to the range B2:E2. Cell B16 contains the following formula:

=SUM(INDIRECT(A16))

This formula returns the sum of the named range entered as text in cell A16.

Figure 3-23: Using the INDIRECT function with a named range.

You can use the Data⇒Data Tools⇒Data Validation command to insert a drop-down list box in cell A16. (Use the List option in the Data Validation dialog box and specify A2:A13 as the list source.) This allows the user to select a month name from a list; the total for the selected month then displays in B16.

The workbook with this example is available at this book's website. The filename is indirect functions.xlsx.

You can also reference worksheet-level names with the INDIRECT function. For example, suppose you have a number of worksheets named Region1, Region2, and so on. Each sheet contains a worksheet-level name called TotalSales. This formula retrieves the value from the appropriate sheet using the sheet name typed in cell A1:

=INDIRECT(A1&”!TotalSales”)

If cell A1 contains the text Region2, the formula evaluates to the following:

=Region2!TotalSales

Using arrays in named formulas

An array is a collection of items. You can visualize an array as a single-column vertical collection, a single-row horizontal collection, or a multirow and multicolumn collection.

Part IV of this book discusses arrays and array formulas, but this topic is also relevant when discussing names.

You specify an array by using curly brackets. A comma or semicolon separates each item in the array. Use a comma to separate items arranged horizontally, and use a semicolon to separate items arranged vertically.

Use the New Name dialog box to create a formula named MonthNames that consists of the following formula definition:

={“Jan”,”Feb”,”Mar”,”Apr”,”May”,”Jun”,”Jul”,”Aug”,”Sep”,”Oct”,”Nov”,”Dec”}

This formula defines a 12-item array of text strings, arranged horizontally.

When you type this formula, make sure that you include the brackets. Entering an array formula into the New Name dialog box is different from entering an array formula into a cell.

After you define the MonthNames formula, you can use it in a formula. However, your formula needs to specify which array item to use. The INDEX function is perfect for this. For example, the following formula returns Aug:

=INDEX(MonthNames,8)

You can also display the entire 12-item array, but it requires 12 adjacent cells to do so. For example, to enter the 12 items of the array into A3:L3, follow these steps (which assume that you used the New Name dialog box to create the formula named MonthNames):

1. Select the range A3:L3.

2. Type =MonthNames in the Formula bar.

3. Press Ctrl+Shift+Enter.

Pressing Ctrl+Shift+Enter tells Excel to insert an array formula into the selected cells. In this case, the single formula is entered into 12 adjacent cells in Figure 3-24. Excel places brackets around an array formula to remind you that it's a special type of formula. If you examine any cell in A3:L3, you'll see its formula listed as follows:

{=MonthNames}

Figure 3-24: You can enter a named formula that contains a 12-item array into 12 adjacent cells.

You'll notice that you can't delete any of the months because the 12 cells make up a multicell array formula — a single formula that occupies multiple cells.

To insert the month names into A1:A12 (a vertical range):

1. Select the range A1:A12.

2. Type =TRANSPOSE(MonthNames) in the Formula bar.

3. Press Ctrl+Shift+Enter.

Creating a dynamic named formula

A dynamic named formula is a named formula that refers to a range not fixed in size. You may find this concept difficult to grasp, so a quick example is in order.

Examine the worksheet shown in Figure 3-25. This sheet contains a listing of sales by month, through the month of June.

Figure 3-25: You can use a dynamic named formula to represent the sales data in column B.

Suppose you want to create a name (SalesData) for the data in column B, but you don't want this name to refer to empty cells. In other words, the reference for the SalesData range would change each month as you add a new sales figure. You could, of course, use the Name Manager dialog box to change the range name definition each month. Or, you could create a dynamic named formula that changes automatically as you enter new data.

To create a dynamic named formula, start by re-creating the worksheet shown in Figure 3-25. Then follow these steps:

1. Bring up the New Name dialog box.

2. Type SalesData in the Name field.

3. Type the following formula in the Refers To field:

=OFFSET(Sheet1!$B$1,0,0,COUNTA(Sheet1!$B:$B),1)

4. Click OK to close the New Name dialog box.

The preceding steps create a named formula that uses Excel's OFFSET and COUNTA functions to return a range that changes, based on the number of nonempty cells in column B.

This formula assumes that the range doesn't contain any blank cells. For example, if cell B2 is empty, the COUNTA function would not count that cell, and the OFFSET function would return an incorrect range.

To try out this formula, enter the following formula into any cell not in column B:

=SUM(SalesData)

This formula returns the sum of the values in column B. Note that SalesData does not display in the Name box and does not appear in the Go To dialog box. You can, however, type SalesData into the Name box to select the range. Or, bring up the Go To dialog box and type SalesData to select the range.

At this point, you may be wondering about the value of this exercise. After all, a simple formula such as the following does the same job, without the need to define a formula:

=SUM(B:B)

Or, you could just enter this formula directly into a cell without creating a named formula:

=SUM(OFFSET($B$1,0,0,COUNTA($B:$B),1))

The truth is, dynamic named formulas were more important in older versions of Excel. Dynamic named formulas used to be the only way to create a chart that adjusted automatically as you added new data. However, with the introduction of tables (created by using Insert⇒Tables⇒Table), dynamic named formulas are rarely necessary. If you create a chart from data in a table, the chart adjusts automatically.

Refer to Chapter 9 for more information about tables.

The workbook with this example is available at this book's website. The filename is dynamic named formula.xlsx. The named formula is also used in a chart's SERIES formula, creating a self-expanding chart.

Using an XLM macro in a named formula

The final example is both interesting — because it uses an Excel 4 XLM macro function in a named formula — and useful — because it's a relatively simple way of getting a list of filenames into a worksheet.

Start with an empty workbook, and create a formula named FileList, defined as

=FILES(Sheet1!$A$1)

The FILES function is not a normal worksheet function. Rather, it's an old XLM style macro function that is intended to be used on a special macro sheet. This function takes one argument (a directory path and file specification) and returns an array of filenames in that directory that match the file specification.

A normal worksheet formula cannot use these old XLM functions.— but named formulas can.

After defining the named formula, enter a directory path and file specification into cell A1. For example:

E:BackupExcel*.xl*

Then, this formula displays the first file found:

=INDEX(FileList, 1)

If you change the second argument to 2, it displays the second file found, and so on.

Figure 3-26 shows an example. The path and filespec is in cell A1. Cell A2 contains this formula, copied down the column:

=INDEX(FileList,ROW()-1)

The ROW function, as used here, generates a series of consecutive integers: 1, 2, 3, and so on. These integers are used as the second argument for the INDEX function. Note that cell A22 displays an error because the directory has only 20 files, and it's attempting to display the 21st file.

When you change the directory or filespec in cell A1, the formulas update to display the new filenames.

Figure 3-26: Using an XLM macro in a named formula can generate a list of file names in a worksheet.

This workbook is available at this book's website. The filename is file list.xlsm. You must enable macros when you open this workbook.