Adding up Rows in Excel

In this section, we're going to use the Chocolate addiction spreadsheet from the last section. We'll add to it, and learn how to do some multiplying. We'll also learn how to copy and paste numbers from one column to another. We'll get some more practice with adding columns and rows.

In the last section, you saw how to add up a column of numbers. The long way to do it was this = Sum(A1 + A2 + A3 + A4 + A5). But for a column of consecutive cells, you learnt that there was an easier way. This: = Sum(A1: A5).

We can use that last formula to add up a Row of consecutive cells, too. So, click on cell J3 of your spreadsheet and enter the text "Individual Totals". Widen the column a bit. Your spreadsheet should now look like the one below:



What we're going to do now is add up the Rows. We want to know how many Mars Bars are eaten in any one week, how many Twix, how many Bounty bars, and how many Other bars. So the sum is the Monday total, plus the Tuesday total, plus the Wednesday total, plus the Thursday total, plus the Friday total, plus the Saturday total, plus the Sunday total.

If we start on the Mars Bars, we need to know which cells we want to add up. Then we tell Excel to do the sum. The first Mars Bar total is in cell B4, the second is in C4, the third in D4, the fourth in E4, the fifth F4, the sixth in G4, and the seventh total is in H4. So those are the cells we want to add up. As they are consecutive cells we can use the colon - B4:H4.

Next we decide where we want Excel to put the answer. We've just added a new headings called Individual Totals. This was in the J column. The Mars Bar heading was on Row 4. So the first total needs to go into cell J4.

• 
  
  So click on cell J4 with your left mouse button


• 
  
  Click inside the formula bar at the top


• 
  
  Type in the following formula:

=Sum(B4:H4)

• 
  
  Hit the Return key on your keyboard


• 
  
  The answer to all that addition should appear in cell J4

Once we have the answer in cell J4, we can use Auto Fill to get the answers to the other 3 Rows.

• 
  
  Click on cell J4


• 
  
  Move your mouse to the bottom right of the cell


• 
  
  The cursor turns into a black cross, as in the image below

• 
  
  When the cursor turns into a black cross, hold down your left mouse button


• 
  
  Keep the left mouse button held down and drag your mouse downwards to cell J7

When your cursor gets to cell J7, let go of the left mouse button. Excel will Auto Fill the other three cells, inserting a formula based on the first one. The J column of your spreadsheet now looks like the one below:

Click on any of those four cells in the J Column. Then take a look at the formula bar to see the formulas. Make sure you understand what is being added up in each Row, and how the answer is derived.

All right, we have the individual total for the chocolate bars. But we can also work out how many chocolate bars are eaten in any one week. The grand total, in other words.

• 
  
  Click on cell A11


• 
  
  Enter the text "Number of Chocolate bars consumed in a week"


• 
  
  Press the return key on your keyboard


• 
  
  Click back in cell A11


• 
  
  Highlight all of your new text and merge the cells (Do you remember how to do this? Click on Format from the menu bar. From the drop down menu, click Cells. Click the alignment tab strip in the dialogue box that pops up. Put a tick in the bottom box "Merge cells".)


• 
  
  Centre your new text and make it bold. The font should be Arial, 10 points