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