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:

  • 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

Entering formula into an Excel spreadsheet

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:

Entering a formula in a spreadsheet

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."

 

This page is getting a bit long now, so you can continue the lesson by clicking the link below.

<-- Back to Entering Data | Continue this lesson on Formulas -->

<--Back to the Excel Contents Page

 
Computer Tutorials List