You can use labels instead of cell references when you create formulas in worksheet tables. Labels at the top of columns and to the left of rows identify the adjacent cells in the table when you use the labels in a formula. This is called a natural-language formula, which is a fairly accurate description of the way it works, providing you use language that is natural.

Tip: Cross the language barrier

The capability to create natural-language formulas is an option that is normally turned off. To enable this feature, choose Tools, Options; on the Calculation tab, click the Accept Labels In Formulas option. Also, be aware that the "Accept labels in formulas" option is not a global setting, but it is stored with each workbook. So, you must enable the option in each workbook in which you want to use natural language formulas.

Note You can find the sample file used in this example in Regional Sales.xls.

The formulas in rows 9 and 10 might normally contain formulas with range references, such as =SUM(B4:B7). But instead of a range reference, a natural-language formula uses a column label from the table, such as =SUM(Qtr 1). When you use text instead of cell references in formulas, Excel looks for column and row labels that match. If Excel finds a matching label, it extrapolates what you are after, using a complex set of internal rules. For our purposes, we can say the following:

If a formula contains a label from the same column or row where the formula resides, Excel assumes that you want to use the entire contiguous range of entries adjacent to the label (either below a column label or to the right of a row label).

If a formula contains a label from a column or row other than the one where the formula resides, Excel assumes that you want to act on a single cell at the intersection of the labeled column or row and the column or row containing the formula.

If what you intend is not clear, Excel displays a dialog box asking you to select the label.

To identify a specific cell in a natural-language formula, we use intersection. For example, the formula =Region 2 Qtr 2 pinpoints the cell located at the intersection of the Region 2 row and the Qtr 2 column, which is—cell C5. (Note that you must enter a space between the row and column label in the formula.) If Excel can’t tell which label you want to use, the Identify Label dialog box appears.

You can copy and paste natural-language formulas; the columns and rows to which you refer adjust accordingly. If the formula in cell B9 was copied to cells C9:E9. All of the formulas in the copy range adjusted so that the label for the current column was used. For example, after copying the formula in cell B9 to the right, the formula in cell E9 became =SUM(Qtr 4). Note that if you copy natural-language formulas to places that do not make sense, Excel alerts you that something is wrong. For example, if you copy the formula in cell H9 to cell J9, Excel displays the error value NAME? because no label is available in column J. If you change the column and row labels in tables, the labels you typed into formulas adjust. For example, if we change the label Total in Cell G3 to Year, the formula in cell G9 adjusts accordingly to read =SUM(Year).