Using Names in Formulas

After you define a name for a cell or range, you can use it in a formula. If the name is a workbook-level name (the default type), you can use the name in any sheet in the workbook. Just enter the name in place of the cell reference. For example, the following formula calculates the sum of the values in the range named TotalUnits:

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 TotalUnits represents a worksheet-level name defined on Sheet1, the following formula (on a sheet other than Sheet1) calculates the total of the TotalUnits range:

As you type a formula, simply press F3 to display the Paste Name dialog box. Select a name from the list, click OK, and Excel inserts that name into your formula. The Paste Name dialog box lists all workbook-level names, plus worksheet-level names for the active sheet only.

If you use a nonexistent name 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.