HOW TO: Use Global and Local References in Formulas in Excel 2000

View products that this article applies to.

Summary

In Microsoft Excel, you can define names on a worksheet as either global names or local names. This article explains what global names and local names are. It also shows you how to create global names and local names, as well as how to use them in calculations on the same worksheet, on different worksheets in the same workbook, and on worksheets in other workbooks.

Global Names

Global names are available to the whole workbook. For example, if you define a range on Sheet1 as Sales, you can enter the formula =SUM(Sales) on Sheet2 to get a sum of values in the range on Sheet1. When you use a global name, you do not have to indicate a sheet name because a global name is available to any sheet in the workbook.

Local Names

A local name is available by default only on the sheet where it is defined. You can create a local name by including the sheet name with an exclamation point (!) before the range name that you enter either in the Define Name dialog box or in the Name box.

How to Create Global Names and Local Names

To create global names and local names, follow these steps:

Start Excel, and then open a new workbook.
Type the following data in cells A1:B3:
```
    Pots     25
    Pans     35
    Plates   45
```
Name the worksheet North.
Select the range A1:A3, point to Name on the Insert menu, and then click Define to open the Define Name dialog box.
In the Names in workbook box, type List, click Add, and then click OK.
Select the range B1:B3, and then open the Define Name dialog box.
In the Names in workbook box, type North!Sales, and then click Add.

The word North is to the right of Sales in the range name list.
Click OK.
Save the workbook with the name Products.

The range List is global and the range North!Sales is local. To see the effect of this, click Sheet2, and then open the Define Name dialog box. Notice that only the List range is displayed. Click OK.

The range North!Sales is not visible on Sheet2 because it is defined locally on Sheet1. To use this name on a another sheet in the workbook, you must create an external reference. For example, to add up the values in the North!Sales range, enter the formula =SUM(North!Sales) in a cell on Sheet2.

Local names take precedence over global names. To refer to a global name that duplicates a local name on the active sheet, use an external reference by specifying the workbook name or the name of a worksheet that does not have the same name defined locally. The next sections discuss how to refer to global names and local names, both in the current workbook and in other workbooks.

How to Refer to Global Names and Local Names Located in the Current Workbook

The following examples extend the Products workbook to include the worksheets South and Total. Sales are defined as a global name in Products and as a local name in the worksheet South, but not in the worksheet Total.

To create these global names and local names, follow these steps:

In the Products workbook, click the North worksheet, and then click Move or Copy Sheet on the Edit menu.
In the Move or Copy dialog box, click Sheet2 in the Before sheet list, click to select the Create a copy check box, and then click OK.
Rename the new copy South.
Repeat steps 1 through 3 to create a worksheet that is named Total.
Click the South worksheet.
Open the Define Name dialog box.

Both List and Sales are defined locally; that is, they are listed with both the range name and worksheet name.

NOTE: When a worksheet is copied, Excel copies any locally defined names so that they have the corresponding local definition in the new worksheet. Excel also converts any globally defined names (which cannot be defined twice globally) to local names in the new worksheet. In the latter case, the local definition takes precedence and is the one listed in the Define Name dialog box for that worksheet.

A similar result is obtained when you create names by using labels in adjacent cells. To do this, you select a range, point to Name on the Insert menu, and then click Create. If you carry out this action on different sheets with similarly named cells, the first operation creates global names, but subsequent operations on other sheets create local names.
Click the entry List, and then click Delete.

Notice that the name List remains but the worksheet name South has been removed. After you delete the locally defined name, the globally defined name becomes visible.
Click the Total worksheet, delete the local definitions of List and Sales in the worksheet's Define Name dialog box, and then click OK.
Select the cells B1:B3, and then use the Define Name dialog box to name them Sales. (Do not name the cells Total!Sales.) Click OK.
Clear the contents of the Sales range.
In cell B1, enter the formula =SUM(North:South!B1).

NOTE: In this case, you must use the specific cell reference B1. If you have given a local name such as Pots to that cell on each sheet, the formula =SUM(North:South!Pots) produces a #NAME! error.
Use the fill handle to fill the formula into cells B2:B3.
Click the South worksheet, and then enter the following data in cells A5:B6
```
    Local     =SUM(Sales)
    Global    =SUM(Products!Sales)
```
Notice that the Global sum is based on the globally defined range Sales, which is found on the Total worksheet.
Select the cells A5:B6, and then click Copy. On the North worksheet, select cell A5, and then click Paste.

You receive a message that asks whether you want to use the locally defined Sales range or rename the one that refers to the source worksheet. In this case, you want to refer to the local Sales range, so click Yes.

The following table summarizes the syntax used to refer to the locally and globally defined ranges in these worksheets, using the SUM function as an example.

To refer to this name	On this sheet	Use this formula
North!Sales	North	`=SUM(Sales)`
North!Sales	South	`=SUM(North!Sales)`
North!Sales	Total	`=SUM(North!Sales)`
South!Sales	North	`=SUM(South!Sales)`
South!Sales	South	`=SUM(Sales)`
South!Sales	Total	`=SUM(South!Sales)`
Sales	North	`=SUM(Products!Sales)`
Sales	South	`=SUM(Products!Sales)`
Sales	Total	`=SUM(Sales)`

Search Order of Global Names and Local Names on the Current Workbook

If you enter the formula =SUM(Sales) on a worksheet in Products, Excel uses the following search order:

Local Sales on the current worksheet in Products
Global Sales on Products

If you enter the formula =SUM(North!Sales) or the formula =SUM([Products]North!Sales) on a worksheet in Products, Excel uses the following search order:

Local Sales on North
Global Sales on Products

If you enter the formula =SUM(Products!Sales) on a worksheet in Products, Excel uses the following defined name:

Global Sales on the Products workbook

Refer to Global Names and Local Names Located in Another Workbook

You can also refer to global names and local names from other workbooks. To do that with the Products workbook, follow these steps:

Without closing the Products workbook, open a new workbook.
Enter the following data in cells A5:B6
```
   North    =SUM([Products]North!Sales)
   Total    =SUM(Products!Sales)
```
The North sum refers only to the range defined locally on the North worksheet in the Products workbook, while the Total sum refers to the globally defined range.

The following table summarizes the syntax for creating external references between workbooks by using the example earlier in this article.

To refer to this name in Products	On a worksheet in this workbook	Use this formula
North!Sales	Book2	`=SUM([Products]North!Sales)`
South!Sales	Book2	`=SUM([Products]South!Sales)`
Sales	Book2	`=SUM(Products!Sales)`

To refer to a global or local name in another workbook, you must include the workbook name.

Search Order of Global Names and Local Names in Another Workbook

If you enter the formula =[Products]North!Sales on a worksheet in Book2, Excel uses the following search order:

Local Sales on North
Global Sales in Products

If you enter the formula =Products!Sales on a worksheet in Book2, Excel searches for the following defined name:

Global Sales in the Products workbook

If the name Sales is found in the first step of the search, that value of Sales is returned, and the search is complete. If the name Sales is not found after the final step in the search, the #NAME! error is returned.

↑ Back to the top

References

For more information about naming cells and ranges, click Microsoft Excel Help on the Help menu, type name cells in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

↑ Back to the top