Using your own labels in formulas

Applies to
Microsoft Excel 2000

Cell references can make formulas harder to read, especially when your worksheet has lots of complex formulas. Fortunately Microsoft Excel 2000 provides an easier way to refer to cells whenever a worksheet has labels for the rows and columns: you can use the labels to refer to the cells. This approach results in formulas that are easier to understand, especially if you're modifying a worksheet that you haven't changed for a while or that someone else created.

As shown in the following example, instead of the formula SUM(D2:G2), you can use SUM(Golf) to calculate the total for the Golf row.

Example of a label used in a formula

Excel provides two ways for you to use row and column labels in formulas:

  • You can have Excel recognize the labels automatically.
  • You can define names from the labels—a name is a string of characters that you designate to represent a cell or range—and then use the names in formulas.

Each approach has its advantages.

Recognizing labels automatically    This is the easiest way to use labels in formulas. However, if you add rows or columns to the ranges that the labels refer to, you may have to update your formulas manually.

Defining names based on labels    This approach requires an extra step of creating the names, and if you add new labels you may have to repeat this step. However, you can add rows and columns to named ranges, update the names, and Excel updates your formulas automatically. If you have existing formulas that use cell references, Excel can automatically convert the formulas to use the names.

Using labels automatically in formulas

In Excel 2000, automatic recognition of labels in formulas is turned off by default. To turn this feature on, click Options on the Tools menu, click the Calculation tab, and then select the Accept labels in formulas check box under Workbook options.

This option affects only the current workbook and must be set separately for each workbook. If you want to set it by default for new workbooks, you can modify the default workbook template. For the procedure, type default template in the Office Assistant or on the Answer Wizard tab in the Excel Help window, click Search, and then click "Create a workbook template for new workbooks."

Including labels in formulas

You can use the label for a row or column to refer to a range of numbers in that row or column. The example above uses label Golf to refer to cells D2:G2 in the Golf row, or you could use Qtr4 to refer to cells G2:G4 in the Qtr4 column.

To refer to a single cell, you can use both the row and column labels: Golf Qtr4 refers to cell G2, at the intersection of the Golf and Qtr4 columns (in the formula you'd type a space between the two labels). Either the row or column label can be first: Qtr4 Golf also refers to cell G2.

If a label includes an internal space, such as Unit Cost, be sure to enclose the label in single quotes in your formula: Sum('Unit Cost').

For more information about the syntax for using labels in formulas, type using labels in the Office Assistant or on the Answer Wizard tab in the Excel Help window, click Search, click "Name cells in a workbook," and then click "Learn about labels and names in formulas."

Updating formulas when you add cells to labeled ranges

Excel determines what range a label refers to when you enter a formula. If you add or delete cells at the bottom or right side of the range a label refers to, existing formulas that use the label aren't updated to refer to the new larger or smaller range, even though new formulas that you enter using the labels will use the expanded ranges.

You can fix this by re-entering the formula: click the formula cell, press F2, and then press ENTER. Or, to update all formulas on a worksheet, do the following:

  1. On the Edit menu, click Replace.
  2. In the Find what box, type an equal sign (=).
  3. In the Replace with box, also type an equal sign (=).
  4. Click Replace All.

This procedure modifies every formula on the worksheet and thereby causes Excel to update the ranges. If you need to use this frequently, you can record it in a macro. For more information, type record macro in the Office Assistant or on the Answer Wizard tab in the Excel Help window, and then click Search.

If you use labels automatically and encounter problems with updating ranges, you may want to convert the references in your formulas to use names instead of labels, as explained in the next section of this article.