Search in book...
Toggle Font Controls
Create new playlist

Name your new playlist

Playlist description (optional)
Sign In

Email address

Password

Forgot Password?

or

Continue with Facebook

Continue with Google
Sign Up

Full Name

Email address

Confirm Email Address

Password

or

Continue with Facebook

Continue with Google

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

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, keep in mind that 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 we 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 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 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

='My Sheet'!My_Name

Referencing names from another workbook

Chapter 2, “Basic Facts About Formulas,” 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, we 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 tell you more about themselves. For instance, you can prefix all your workbook-level names with wb and your worksheet-level names with ws. With this method, you 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.

Image described by surrounding text. — **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.

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 are updated.

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.

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.

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

Rules for naming names

Although Excel is quite flexible about the names you can define, it does have some rules:

Names can’t contain spaces. You might want to use an underscore or a period character to simulate a space (such as Annual_Total or Annual.Total).
You can use any combination of letters and numbers, but the name must begin with a letter or underscore. A name can’t begin with a number (such as 3rdQuarter).
A name cannot look like a cell reference. This excludes names such as Q3 and TAX2012.
You cannot use symbols, except for underscores and periods. Although not documented, we’ve found that Excel also permits a backslash () and question mark (?) as long as they don’t appear as the first character in a name.
Names cannot exceed 255 characters in length.
You can use single letters (except for R or C). However, it is not recommended because it also defeats the purpose of using meaningful names.
Names are not case sensitive. The name AnnualTotal is the same as annualtotal. Excel stores the name exactly as you type it when you define it, but it doesn’t matter how you capitalize the name when you use it in a formula.

Although periods are allowed, you can’t use a period if the resulting name can be construed as a range address. For example A1.A12 is not a valid name because it’s equivalent to the address A1:A12.

Excel also uses a few names internally for its own use. Although you can create names that override Excel’s internal names, you should avoid doing so unless you know what you’re doing. Generally, avoid using the following names: Print_Area, Print_Titles, Consolidate_Area, Database, Criteria, Extract, FilterDatabase, and Sheet_Title.

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.

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 spaces, enclose the sheet name in single quotes, 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.

Screenshot of a cell in an excel sheet shows a drop down menu listing month, running_total and units_sold along with the table listing the units sold and running total for February and from April to December. — **Figure 3.3** The Name box provides a quick way to select a named cell or range.

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.

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 R to create names for the corresponding values in columns S through AD. Excel makes this easy to do.

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.

Dialog box of an excel sheet shows the columns from R to AD. A window with header create names from selection is shown in the middle of the page with check boxes for top and bottom rows and left and right columns. — **Figure 3.5** The Create Names from Selection dialog box.

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

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.

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.6 is an example of such a worksheet. If you create a name for the data in column I, you need to modify the name’s reference each day you add new data. The solution is to name the entire column.

Image described by caption. — **Figure 3.6** This worksheet, which tracks daily sales, uses a named range that consists of an entire column.

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

=Sheet3!$I:$I

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 I:

=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 you understand the consequences.

Hidden names

Some Excel macros and add-ins create hidden names. These names exist in a workbook but don’t appear in the Name Manager dialog box or the Name box. For example, the Solver add-in creates a number of hidden names. Normally, you can just ignore these hidden names. However, sometimes these hidden names create problems. If you copy a sheet to another workbook, the hidden names are also copied, and they may create a link that is difficult to track down.

Although Excel’s Name Manager is versatile, it doesn’t have an option to display hidden names. Here’s a simple VBA procedure that lists all hidden names in the active workbook. The macro adds a new worksheet, and the list is written to that worksheet:

Sub ListHiddenNames()
    Dim n As Name, r As Long
    Worksheets.Add
    r = 1
    For Each n In ActiveWorkbook.Names
       If Not n.Visible Then
        Cells(r, 1) = n.Name
        Cells(r, 2) = "'" & n.RefersTo
        r = r + 1
       End If
    Next n
End Sub

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.7, 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, it’s 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.

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):

Activate Sheet1.
Choose Formulas ➜ Defined Names ➜ Define Name to display the New Name dialog box.
Type DataCube into the Name field.
Highlight the range reference in the Refers To field and press Delete to delete the range reference.
Click the sheet tab for Sheet1.
Press Shift and click the sheet tab for Sheet3.

At this point, the Refers To field contains the following:
```
='Sheet1!Sheet3'!
```
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
```
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)

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 is included in the DataCube range. However, a worksheet inserted before Sheet1 or after Sheet3 is not included.

If you delete the first or the 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 is 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’s 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.8 shows the Paste Name dialog box.

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.

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 you’re composing a formula and you need to insert a name, you have three options:

Start typing the name, and it appears 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.

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.9 shows a worksheet containing named ranges that correspond to the row and column labels. For example, January refers to B2:E2, and Region3 refers to D2:D13. The following formula returns the contents of the cell at the intersection of the January range and the Region3 range:

=January Region3

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.10 shows a worksheet that contains a range (A2:A13) named MonthNames. Cell G5 contains the simple formula shown here:

=MonthNames

Notice that cell G5 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.

If you enter the formula into 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 G5 contains the following formula (which doesn’t use a named range):

=$A$2:$A$13

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 into cell G5, 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.9. For example, this formula returns the sum of the values in the 12-cell range that extends from Region1 January (cell B2) through Region4 March (cell E4):

=SUM((Region1 January):(Region4 March))

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 the 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 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.11. In the Apply Names dialog box, select which names you want applied to the formulas. Only those names you select are applied to the formulas.

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 Excel to apply names whether the formulas are a relative or absolute reference. So leave the Ignore Relative/Absolute check box selected.

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 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 don’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.

Choose File ➜ Options and then click the Advanced tab in the Excel Options dialog box.
Under the Lotus Compatibility Settings section, place a check mark next to Transition Formula Entry and then click OK.
Select a cell that contains a formula that uses one or more cell or range names.
Press F2 and then press Enter.
In other words, edit the cell but don’t change anything.
Repeat steps 3 and 4 for other cells that use range names.
Go back to the Options dialog box and remove the check mark from the Transition Formula Entry check box.

The edited cells use relative range references rather than names.

Names with errors

If you delete the rows or columns that contain named cells or ranges, the names are not deleted (as you might expect). Rather, each name contains 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.

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.12. The border and name do not print; they simply help you visualize the named ranges on your sheet.

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 adjusts if you change the definition for the name.

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 a 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!$C$2:$E$4.

If you delete a row or a 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 are also 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 is also present on the copied sheet. However, it is 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). After copying the sheet, the workbook contains four names. The new sheet has two worksheet-level names.

Not only is this proliferation of names when copying a sheet confusing, but it can 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.

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

Name problems when deleting sheets

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

For instance, imagine your workbook contained a sheet named Sheet2, which has a workbook-level name (MyRange). After deleting Sheet2, the name MyRange still exists in the workbook, but the Refers To column displays a #Ref error for the sheet name. You might see something like this:

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

Keeping erroneous names in a workbook doesn’t cause harm, but it’s still good practice to delete or correct all names that contain an erroneous reference.

Naming objects

When you add an object to a worksheet (such as a shape, an image, or a chart), the object has a default name that reflects the type of object (for example, Rectangle 3 or Text Box 1).

To change the name of an object, select it, type the new name in the Name box, and press Enter.

Excel is a bit inconsistent with regard to the Name box. Although you can use the Name box to rename an object, the Name box does not display a list of objects, so you can’t use the Name box to select an object. However, you can use the Selection Pane to list all objects and make them easy to select. To display the Selection Pane, choose Home ➜ Editing ➜ Find & Select ➜ Selection Pane.

Excel also allows you to define a name with the same name as an object; two or more objects can even have the same name. The Name Manager dialog box does not list the names of objects.

The Secret to Understanding Names

Excel users often refer to named ranges and named cells. In fact, we 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 can 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.

For example, if your workbook contains a name (InterestRate) referring to cell B1, that name is technically a named formula, not a named cell. 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

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 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.13 demonstrates the following steps:

Choose Formulas ➜ Defined Names ➜ Define Name to bring up the New Name dialog box.
Type the name (in this case, SalesTax) into the Name field.
Click in the Refers To field, delete its contents, and replace it with a simple formula, such as =.075.
Click OK to close the New Name dialog box.

The preceding steps create a named formula that doesn’t use 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.

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.14 shows another example of a named formula. In this case, the formula is named ThisMonth, and the actual formula is this:

=MONTH(TODAY())

The formula in Figure 3.14 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 returns 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")

Now enter the following formula into a cell, and it returns 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.15 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)

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 adjusts 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 always returns 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 nonempty 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 results 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 interesting named formulas.

Using named formulas with relative references

As we 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, we 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:

Start with an empty worksheet.
Select cell A1.

This step is important.
Choose Formulas ➜ Defined Names ➜ Define Name.

This brings up the New Name dialog box.
Type CellToRight in the Name field.
Delete the contents of the Refers To field and type the following formula. (Don’t point to the cell in the sheet.)
```
=Sheet1!B1
```
Click OK to close the New Name dialog box.
Type something (anything) into cell B1.
Enter this formula into cell A1:
```
=CellToRight
```
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. 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. You 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

If you use the CellToRight name on a different worksheet, you 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:

Activate cell A1 on Sheet1.
Choose Formulas ➜ Defined Names ➜ Name Manager to bring up the Name Manager dialog box.
In the Name Manager dialog box, select the CellToRight item in the list box.
In the Refers To field, delete the sheet name (but keep the exclamation point). The formula should look like this:
```
=!B1
```
Click Close to close the Name Manager dialog box.

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

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:

Activate cell A13 (very important).
Choose Formulas ➜ Defined Names ➜ Define Name to bring up the New Name dialog box.
Type Sum12Cells into the Name field.
Type this formula into the Refers To field:
```
=SUM(!A1:!A12)
```

After creating this named formula, you can insert the following formula into any cell in row 13 or higher to return the sum of the 12 cells directly above that cell:

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

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 see that the Refers To field for the Sum12Cells name displays the following:

=SUM(!D28:!D39)

Using a mixed range reference

As we discussed 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 a named formula. Activate cell A2 and create a FirstInColumn name using this formula:

=!A$1

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

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.

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.

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):

Select the range A3:L3.
Type =MonthNames into the Formula bar.
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 the selected adjacent cell. 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 see its formula listed as follows:

{=MonthNames}

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), do the following:

Select the range A1:A12.
Type =TRANSPOSE(MonthNames) into the Formula bar.
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.17. This sheet contains a listing of sales by month, through the month of June.

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.17. Then follow these steps:

Bring up the New Name dialog box.
Type SalesData into the Name field.
Type the following formula into the Refers To field:
```
=OFFSET(Sheet1!$B$1,0,0,COUNTA(Sheet1!$B:$B),1)
```
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.

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.

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.18 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 twenty-first file.

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

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.