Using Labels in Formulas and Functions in Excel 2003

Content
  1. Simplify your Excel 2003 formulas
  2. Range names
  3. labels
  4. Turn on ‘Accept labels in formulas’
  5. Add data to cells
  6. Add a function to a worksheet
  7. Overview

Simplify your Excel 2003 formulas

While Excel and other spreadsheet applications are useful programs, cell reference is an area that causes many problems for users.

Although cell references are easy to understand, they cause problems for users when they try to use them in functions, formulas, charts, and any other time they need to identify a range of cells from cell references.

Range names

One option that helps is to use range names to identify blocks of data. It’s certainly helpful to name each data element, especially on a large sheet, but it’s a lot of work. Added to this is the problem of trying to remember which name corresponds to which range of data.

Another way to avoid cell references is to use labels in functions and formulas.

labels

Labels are column and row headings that identify data on a worksheet. In the image accompanying this article, instead of typing B3:B9 links to find the data in the function, use the title label Cost

Excel assumes that a label used in a formula or function applies to all data immediately below or to the right of the label. Excel contains all the data in the function or formula until it reaches an empty cell.

Turn on ‘Accept labels in formulas’

Before using shortcuts in functions and formulas in Excel 2003, make sure the dialog box parameters: enabled Accept shortcuts in formulas † Do it:

  1. Choose from the menu Tools parameters: to open the dialog parameters:

  2. Click on the tab Calculations

  3. Check box Accept labels in formulas

  4. Press the button Okay to close the dialog box.

Add data to cells

Enter the following information in the specified cells:

  1. Cell B2 – Numbers

  2. Cell B3 – 25

  3. Cell B4 – 25

  4. Cell B5 – 25

  5. Cell B6 – 25

Add a function to a worksheet

Enter the following function using the heading in cell B10:

=SUM(Numbers)

and press the key ENTER on keyboard.

The answer 100 will be present in cell B10.

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

Overview

To summarize:

  1. Make sure the option Accept labels in formulas including.

  2. Enter label titles.

  3. Enter the information below or to the right of the labels.
    Enter formulas or functions using labels instead of ranges to specify data to include in the function or formula.

Leave a Reply

Your email address will not be published.