Free computer Tutorials
|HOME||Stay at Home and Learn|
Entering Formula into an Excel Spreadsheet
What we want to do in our spreadsheet from the previous page is to add the daily columns. The four numbers for Monday need to be added up; the four numbers for Tuesday need to be added up; the four for Wednesday, and so on. The totals will go into the cells below, in our Day Totals row.
In a spreadsheet, you have to "tell" Excel which cells you want to add up. So for the Monday column, the 1 is in cell B4, the 7 is in cell B5, the 8 is in cell B6, and another 1 in cell B7. So we want the answer to B4 + B5 + B6 + B7.
To let Excel know that is the sum we want working out, do this:
Notice that the Name box top left says B9. This is the cell where we want the answer to appear.
When you have entered the formula in the formula bar, press the return key on your keyboard.
Your spreadsheet will look like the one below:
Something has clearly gone wrong! We were expecting Excel to add up the numbers in those cells. Instead it has entered the cell references themselves: B4 + B5 + B6 + B7.
The problem is that Excel thinks you want text in the cell B9. When we entered B4 + B5 + B6 + B7 in the formula bar we didn't "tell" Excel to add up. So when we pressed the Return key, Excel thought it was just text.
To "tell" Excel to add up, you need an equals sign first. We'll also use the Sum function. The Sum function looks this:
You put what you want to add up in between the two brackets.
So we would need this:
=Sum( B4 + B5 + B6 + B7)
When your spreadsheet looks like the one above, press the Return key on your keyboard.
Finally, Excel understands what you want to do. It adds up the numbers in the cells you gave it, and puts the answer in cell B9. Your spreadsheet now looks like this one:
Notice what is happening in the Name box and the formula bar. The cell B9 is highlighted and displayed in the Name box top left. The formula bar is telling you what you have in cell B9. In this case it is the Sum( ) function. In between the brackets is what we want to add up: B4 + B5 + B6 + B7.
But suppose you had a very long column to add up. Suppose you wanted to add up the cells B4 to B44. Would you really have to enter B4 + B5 + B6 + B7 + B8 + B9 + B10 + B11, and so on right up to B44?
Fortunately not. There is a shorthand that Excels understands, when you want to add up consecutive cells. You use the colon. You type the first cell you want to add up, then type a colon. After the colon, you type the last cell that you want to add up. It would look like this:
The colon means: "Add up all the cells between the one on my left and the one on my right."
This page is getting a bit long now, so you can continue the lesson by clicking the link below.