A defined name in a formula can make it easier to understand the purpose of the formula. For example, the formula =SUM(FirstQuarterSales) might be easier to identify than =SUM(C20:C30).

Names are available to any sheet. For example, if the name ProjectedSales refers to the range A20:A30 on the first worksheet in a workbook, you can use the name ProjectedSales on any other sheet in the same workbook to refer to range A20:A30 on the first worksheet.

Names can also be used to represent formulas or values that do not change (constants). For example, you can use the name SalesTax to represent the sales tax amount (such as 6.2 percent) applied to sales transactions.

You can also link to a defined name in another workbook, or define a name that refers to cells in another workbook. For example, the formula =SUM(Sales.xls!ProjectedSales) refers to the named range ProjectedSales in the workbook named Sales.

Note  By default, names use absolute cell references (absolute cell reference: In a formula, the exact address of a cell, regardless of the position of the cell that contains the formula. An absolute cell reference takes the form $A$1.).

Guidelines for names

What characters are allowed? The first character of a name must be a letter, an underscore character (_), or a backslash (\). Remaining characters in the name can be letters, numbers, periods, and underscore characters.

Can names be cell references? Names cannot be the same as a cell reference, such as Z$100 or R1C1.

Can more than one word be used? Yes, but spaces are not allowed. Underscore characters and periods may be used as word separators — for example, Sales_Tax or First.Quarter.

How many characters can be used? A name can contain up to 255 characters.

Note  If a name defined for a range contains more than 253 characters, you cannot select it from the Name box.

Are names case sensitive? Names can contain uppercase and lowercase letters. Microsoft Excel does not distinguish between uppercase and lowercase characters in names. For example, if you have created the name Sales and then create another name called SALES in the same workbook, the second name will replace the first one.

When you create a formula that refers to data in a worksheet, you can use the column and row labels in the worksheet to refer to the data. For example, to calculate the total value for the Product column, use the formula =SUM(Product).

Using a label

Or if you need to refer to the Product 3 amount for the East division (that is, the value 110.00), you can use the formula =Product 3 East. The space in the formula between "Product 3" and "East" is the intersection operator (operator: A sign or symbol that specifies the type of calculation to perform within an expression. There are mathematical, comparison, logical, and reference operators.). This operator designates that Microsoft Excel should find and return the value in the cell at the intersection of the row labeled East and the column labeled Product 3.

Note  By default, Excel does not recognize labels in formulas. To use labels in formulas, click Options on the Tools menu, and then click the Calculation tab. Under Workbook options, select the Accept labels in formulas check box.

Stacked labels   When you use labels for the columns and rows on your worksheet, you can use those labels to create formulas that refer to data on the worksheet. If your worksheet contains stacked column labels — in which a label in one cell is followed by one or more labels below it — you can use the stacked labels in formulas to refer to data on the worksheet. For example, if the label West is in cell E5 and the label Projected is in cell E6, the formula =SUM(West Projected) returns the total value for the West Projected column.

The order for stacked labels   When you refer to information by using stacked labels, you refer to the information in the order in which the labels appear, from top to bottom. If the label West is in cell F5 and the label Actual is in cell F6, you can refer to the actual figures for West by using West Actual in a formula. For example, to calculate the average of the actual figures for West, use the formula =AVERAGE(West Actual).

Using dates as labels   When you label a range by using the Label Ranges dialog box and the range contains a year or date as a label, Excel defines the date as a label by placing single quotation marks around the label when you type the label in a formula. For example, suppose your worksheet contains the labels 2007 and 2008 and you have specified these labels by using the Label Ranges dialog box. When you type the formula =SUM(2008), Excel automatically updates the formula to =SUM('2008').