Entering Formula into an Excel Spreadsheet

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:

• 
  
  Click in the cell where you want the answer to appear (This is cell B9 for us, the first of the Day Totals)


• 
  
  Now click inside the formula bar


• 
  
  Type in this: B4 + B5 + B6 + B7


• 
  
  Your spreadsheet should look like the one below

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:

=Sum( )

You put what you want to add up in between the two brackets. So we would need this:

=Sum( B4 + B5 + B6 + B7)

So go ahead and enter that in the formula bar. Before you press the Return key on your keyboard, your spreadsheet will look like this:

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:

=Sum(B4: B44)

The colon means: "Add up all the cells between the one on my left and the one on my right."

Now how we could reduce the amount of typing we had to do. We saw that the answer was to use a colon (:)

We can use a colon in our spreadsheet. So do the following:

• 
  
  Click inside cell B9


• 
  
  Click cell B9 with your Right mouse button


• 
  
  A menu pops up, like the one below

• 
  
  Locate "Clear Contents" and then click on it with your left mouse button


• 
  
  The formula disappears from your formula bar


• 
  
  With Cell B9 still highlighted, click inside the formula bar


• 
  
  Type in the following =Sum(B4:B7)


• 
  
  Your spreadsheet will look like the one below

When your formula bar looks like the one above, press the return key on your keyboard. Excels again understands what you want to do, and enters the correct sum in cell B9.

So, you should have the number 17 in cell B9. We'll now use an Auto Fill to add up the rest of the days of the week.

• 
  
  Move your cursor to the bottom right of cell B9


• 
  
  Your cursor will turn into a narrow black cross, like in the image below

• 
  
  When you see the black cross, hold down your left mouse button


• 
  
  Keep it held down, and then drag your mouse across to cell H9, as in the image below

When your cursor reaches cell H9, let go of your left mouse button. Excel will Auto Fill the formula from cell B9. Because the cell B9 added up cells in the B column, Excel will assume that that is what you want to do with the others columns as well: add them up. So it does the sums for you.

Your spreadsheet should now look like the one below:

Notice that the cell H9 is highlighted. The formula in the formula bar reads =Sum(H4:H7). This means "Add up everything in the cells H4 to H7 and pop the answer in the H9 cell."

But we got all that just by using the Auto Fill. We only had to enter one formula for the cell B9 and Excel did the rest.

If you click in any of the cells on row 9 (Click on cell F9, for example), you'll see the formula bar change, with the correct formulas entered for each cell.