Using Labels in Excel 2003 Formulas and Functions

01
of 05

Simplify Your Excel 2003 Formulas

Excel 2003 formula uses a label
Excel 2003 formula uses a label. © Ted French

Although Excel and other electronic spreadsheet applications are useful programs, one area that causes many users difficulties is that of cell references.

Although not difficult to understand, cell references cause users problems when they try to use them in functions, formulas, chart creation, and any other time when they must identify a range of cells by cell references.

Range Names

One option that helps is to use range names to identify blocks of data. While definitely useful, giving every piece of data a name, especially in a large worksheet, is a lot of work. Added to that is the problem of trying to remember which name goes with which range of data.

However, another method of avoiding cell references is available—that of using labels in functions and formulas.

Labels

The labels  are the column and row headings that identify the data in the worksheet. In the image that accompanies this article, rather than typing in the references B3:B9 to identify the data location in the function, use the heading label Expenses instead.

Excel assumes that a label used in a formula or function refers to all the data directly under or to the right of the label. Excel includes all the data in the function or formula until it reaches a blank cell.

02
of 05

Turn On 'Accept Labels in Formulas'

Make sure to check the box to "Accept labels in formulas"
Make sure to check the box to "Accept labels in formulas". © Ted French

Before using labels in functions and formulas in Excel 2003, you must make sure that Accept labels in formulas is activated in the Options dialog box. To do this:

  1. Choose Tools > Options from the menu to open the Options dialog box.
  2. Click on the Calculations tab.
  3. Check the Accept labels in formulas option.
  4. Click the OK button to close the dialog box.

03
of 05

Add Data to the Cells

Add data to cells in the Excel spreadsheet
Add data to cells in the Excel spreadsheet. © Ted French

Type the following data in the indicated cells

  1. Cell B2 – Numbers
  2. Cell B3 – 25
  3. Cell B4 – 25
  4. Cell B5 – 25
  5. Cell B6 – 25

04
of 05

Add a Function to the Worksheet

Formula using a label in Excel spreadsheet
Formula using a label in Excel spreadsheet. © Ted French

Type the following function using the heading in cell B10:

=SUM(Numbers)

and press the ENTER key on the keyboard.

The answer 100 will be present in cell B10.

You would get the same answer with the function =SUM(B3:B9).

05
of 05

Summary

Formula using a label in an Excel spreadsheet
Formula using a label in an Excel spreadsheet. © Ted French

To summarize:

  1. Make sure the Accept labels in formulas option is turned on.
  2. Enter the label headings.
  3. Enter the data under or to the right of the labels.
    Enter the formulas or functions using labels rather than ranges to indicate the data to include in the function or formula.