| Microsoft Excel is a fantastic tool but one of its | | | | However, as I am sure you will agree, using labels |
| downfalls is the use of cell addresses especially when | | | | makes your formulas a lot easier to read and much |
| you are trying to sum a series of numbers; however | | | | clearer to understand. Now it is your turn. In the cell C5 |
| Microsoft has a really cool tool that allows you to add | | | | write the equivalent label formula for that cell. I will give |
| up a series of cells simply using the labels around your | | | | you a hint if you are not sure - |
| data. In this article we will investigate the steps you | | | | = Sum(Values1) |
| need to follow to be able to use Labels to Sum cells in | | | | How did that go? |
| Microsoft Excel. | | | | Alright, the next issue we are going to visit is using |
| Let us get started ? | | | | numbers as formula labels. Now if we simply typed in |
| To show you how using labels works, the first step | | | | cell D2 the formula - |
| we need to undertake is to simply create a new set | | | | = Sum(1999) |
| of data, so open a new Microsoft Excel workbook | | | | All that would be returned is the value 1999 so this will |
| and click on Sheet 1. First off we are going to build the | | | | not work at all. In fact what we need to do is to tell |
| worksheet so in cell A1 I want you to type - Years, in | | | | Microsoft Excel that we want the cells in A2, A3 and |
| cell B1 type the word Values and in cell C1 type the | | | | A4 to be treated as labels. The first step we must do |
| word Values1 and in cell D1 type the word Total. | | | | is to select cells A2,A3 and A4, then go to the Insert |
| These four values we have typed into cell A1, B1, C1, | | | | menu, choose Name from the drop down menu and |
| D1 are labels. We are now going to put into the | | | | then choose the Label command from the expanded |
| worksheet three more labels. In cell A2 type the value | | | | menu. The Label Ranges dialog box will now be visible |
| 1999, in cell A3 type the value 2000 and in cell A4 type | | | | in front of you. All you simply have to do, to have the |
| the value 2001. The last three values entered will in | | | | cells we chose before defined as Labels is to press |
| fact become labels but we will convert them to labels | | | | the Add button and then press the OK button. |
| a little latter on. | | | | Click once on the cell D2. What we are going to do |
| In the remaining cells simply put the following values: | | | | now is to type in the same formula I mentioned before |
| B2 - 29 C2 - 32 | | | | and that is - |
| B3 - 54 C3 - 99 | | | | = Sum(1999) |
| B4 - 62 C4 - 72 | | | | By the way, press the Enter key if you have not done |
| Now that we have built a very simple spreadsheet | | | | so already. As soon as you do that you should see |
| and we have a few values to work with we have to | | | | that it adds up the values in cells B2 and C2 and you |
| tell Microsoft Excel to actually accept labels in our | | | | will see the value of 61 in your cell. |
| formulas in our spreadsheet. We do this by first going | | | | Try creating the formulas for the other two cells - |
| to the Tools menu and then choosing the Options | | | | D3 = Sum(2000) |
| command from the drop down menu. The Options | | | | D4 = Sum(2001) |
| dialog box will now be open in front of you. Simply | | | | Finally, just to finish our spreadsheet off , we can total |
| choose the Calculation tab and in the bottom right hand | | | | all of our total values in cells D2, D3 and D4 by typing |
| corner you will see a check box that says Accept | | | | the following formula in D4 - |
| Labels in Formulas and you need to click on the check | | | | = Sum(Total) |
| box so that it has a tick in it. Then to complete the | | | | Using labels in Microsoft Excel is a very clean way of |
| process simply press the OK button. | | | | summing your values as it ensures that you do not |
| Now we can use the labels we put in B1 and C1 to | | | | miss a cell address and it makes the formulas a lot |
| add up the values. | | | | simpler. One issue that I do teach my students is not to |
| Lets try it out? | | | | put blank rows in your spreadsheets as I have seen |
| In cell B5, which is the Values column I want you to | | | | cases where the blank rows actually affected the |
| type the formula - | | | | ability of the application to work out what was a label |
| = Sum(Values) | | | | and what is not. By following the design process I |
| The cell should return the total of 145. What you will | | | | outlined above you will find that you have absolutely no |
| notice though is that the formula looked up the column | | | | troubles in using Labels to sum cells in your |
| to where the label was and said everything in this | | | | spreadsheets. |
| column will be added together. We could have simply | | | | Chris Le Roy has written a number of Excel Tutorials, |
| typed the following formula instead of using labels - | | | | Excel Shortcuts and a sample Excel Invoice Template |
| = Sum(B2:B3) | | | | to help you learn more about Microsoft Excel 2003. |