Microsoft Excel 2003 Shortcuts: How To Use Labels In Formulas

By Author: Chris Le Roy
Total Articles: 92
Comment this article

Microsoft Excel 2003 is one of the most diverse tools in the Microsoft Office 2003 arsenal. It allows you to manage numbers and data by creating formulas that automatically calculate but in many cases users build inefficient formulas. These formulas can in fact cause problems in the future if people add in rows to your field. To resolve this problem we can in fact use labels to add up your fields.

In this article we are going to go through and actually show you how to use labels in a Microsoft Excel spreadsheet. First open Microsoft Excel 2003 and create a new spreadsheet. In cell A1 type the following, PRODUCT and in cell A2 type the word TOTAL.

In the following rows and columns underneath the headings enter the data in as shown below:

PRODUCT TOTAL
Shoes 5.00
Socks 10.00
Shirts 15.00
Pants 20.00
Total:

Ensure in cell A6 that you type the name TOTAL: this will become very important shortly. Leave the cell B6 blank at this point in time. What we now want to do in cell B6 is to ...
... actually turn on in Microsoft Excel 2003 the function that tells the program to use labels. To do this first go to the Tools menu and then choose Options from the drop down menu, then select the Calculation tab in the Options dialog box. In the bottom right hand corner of the of the Options dialog box you will see a option that says - Accept Labels in Formulas. Ensure that there is a tick in the check box next to it and then press the OK button.

By ensuring there is a tick in the check box, you are telling the program that you want to use labels in your formulas. So how do we do this -

Well now it is time to write the formula. We could have written the formula as -

= b2+b3+b4+b5

However, if a user of your worksheet decided that they wanted to add a new row, then the new row would not be included in the formula. The best way to now write this is to use the Column Label we do this by typing -

= sum('TOTAL')

You will now see that the TOTAL column has added up the range of values. The use of labels in your worksheets does rely that you follow a number of rules. Every label in a worksheet must be unique. You will notice that there are in fact two Total labels in the worksheet but they are named differently. The column label is named TOTAL where as the row label is named Total: This ensures the Microsoft Excel program is able to differentiate between the two labels.

One of the other issues to consider when using labels is to try to minimise the use of spaces in your labels if possible. Whilst Microsoft Excel does allow you to have spaces in your labels, it is preferred that you do not have any. This also minimises the chance that you might have double spaced between two words. Doing this will cause an error in your formula and is a common mistake.

Further to this always ensure that if you do use spaces in your labels that you manually insert the single quotes around the label. This ensures that Microsoft Excel 2003 interprets the label correctly.