Microsoft Excel 2007-2013 part-5

Entering Simple Addition Formula

The first thing we'll do to our spreadsheet from the previous section is to add up all those numbers, the ones going down under the days of the week headings. The total for each day of the week will be placed on Row 9. So Monday's total will go in cell B9, Tuesday's total will go in cell C9, and so on.

Here's our spreadsheet again:

Our first total will go in cell B9.

 

Adding up in Excel

Excel needs to know which cells you want to add up. Look at the numbers for the Monday column. We have a 1 in cell B4, a 7 in cell B5, an 8 in cell B6, and a 1 in cell B7. So we want the answer to this:

B4 + B5 + B6 + B7

To let Excel know that this is what we want, try this:

• Click inside cell B9, which is where we want the answer to appear
• Once you've clicked on cell B9, click into the formula bar at the top
• Type this:

B4 + B5 + B6 + B7

When you have entered the formula in the formula bar, press the enter key on your keyboard. Your spreadsheet should look like ours below:

Something has gone wrong! This is not quite what we were expecting. We wanted Excel to add up the numbers for us, but it hasn't done anything except enter the cells we typed.

What went wrong was that we didn't "tell" excel to add up. Excel needs you to type an equals (=) sign first, and then those cell references. If you don't include the equals sign, Excel thinks it's just plain text, and so doesn't do any calculating.

So enter this inside of your formula bar instead:

= B4 + B5 + B6 + B7

In other words, put an equals sign ( = ) before B4. Press your enter key and you should have the correct answer in cell B9.

Now click back inside the formula bar, and delete the equals sign. Press the enter key again. You should then just have the same text as in the image above. We're doing this to show you an easier way to add up - with the SUM function.

The SUM Function Continued

Using the same spreadsheet you've been working on in the previous section, you'll now get some more practice with the SUM function in Excel, in order to add up values in cells. Our spreadsheet now looks like this, though:

You've just used the easy way to add up values in consecutive cells for a column. Just do this:

=SUM(B4:B7)

Using that formula gave us the answer to how many chocolate bars we ate from Monday to Sunday. You can use this same colon ( : ) shorthand to add up numbers in a Row.

• Click inside cell J3 of your Chocolate Addiction spreadsheet
• Type the text Individual Totals (you may have to widen the column a bit, as you did for a previous section)
• Your spreadsheet will then look like this:

We'll use a SUM formula to add up the values in each Row. This will tell us how many of a particular chocolate bar we ate in one week: how many Mars Bars, how many Twix, etc.

The first answer we'll try is how many Mars Bars we ate in one week. We'll place this answer in cell J4. The cells we're going to be adding up are these:

B4 + C4 + D4 + E4 + F4 + G4 + H4

Because we have consecutive cells, we can use the colon shorthand again.

• Click into cell J4 of your spreadsheet
• Then click into the formula bar at the top
• Enter the following formula:

=Sum(B4:H4)

Press the enter key on your keyboard, and you'll see the answer appear in J4. To complete the rest of the rows, we can use AutoFill again.

• Click back in cell J4 to make it the active cell
• Move your mouse pointer to the bottom right of cell J4
• You'll see the pointer change to a thin black cross:

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

When your mouse pointer gets to cell J7, let go of the left button. Excel will use AutoFill to get the answers for the other three cells. Hopefully, your spreadsheet now looks like ours:

Select any of the cells J4, J5, J6 and J7. Then examine the formula in the formula bar. You should be able to understand what is being added up, and what all the formulas mean.

Now that we have totals for each individual chocolate bar, we can work out how many chocolate bars we ate for the whole week. We'll put the Grand Total in cell F11. First, we'll enter some text to explain what is being added up

• Click inside cell A11 on your spreadsheet
• Type the following text: Number of Chocolate bars consumed in a week
• Hit the Enter key on your keyboard
• You should see the text you just typed. But it will all be in individual cells. Highlight the cells A11 to E11, and merge them together (You learned how to merge cells in a previous section.)
• This is what your spreadsheet should now look like:

There are two ways we can calculate the Grand Total. You can just add up the Individual totals in the J column, or ... Well, how else could you get the number of chocolate bars consumed in one week?

• Click into cell F11 on your spreadsheet
• Enter your formula to calculate the number of chocolate bars consumed in one week
• Hit the Enter key when you think you have the correct formula

The correct answer is 80. If you got a different answer, or are struggling in any way to come up with the correct formula, then it's a good idea to go over the previous section. But don't just type 80 into cell F11 and move on!

How to Copy and Paste

From the previous section, you now have a spreadsheet that looks like this:

If we're eating that many chocolate bars in one week, we'd like to know how much this habit is costing us! And what about the yearly cost of the addiction? Excel makes sums like this quite easy to calculate. First let's have some new headings.

Copy and Paste

To create your new headings, do this:

• Locate cell A13 on your spreadsheet and click on it
• Type Cost of Addiction
• Merge the cells, in the same way you learned previously
• Add a bit of formatting to the text, if you like

We're going to be needing the names of the chocolate bars again. These will go in cells A15 to A18. Instead of typing them all out by hand, Excel 2007 and Excel 2010 make it easy to copy and paste the names. Try this:

• Highlight the cells A4, A5, A6 and A7
• Locate the Clipboard panel in the Ribbon at the top of the page (on the Home menu)
• From the Clipboard panel, click Copy

You'll see some moving lines surrounding your highlighted cells - the so-called marching ants!

• Once you see the marching ants, click into cell A15
• To paste the copied text over, simply press the Enter key on your keyboard
• You spreadsheet should now look something like ours below:

We need some new headings on the spreadsheet. Under these headings will be the price of each chocolate bar, how many of a particular chocolate bar we're eating each week, and how much this is costing us each week. So, do the following:

• In cell B14 enter the word Price
• In cell C14 enter the word Number
• In cell D14 enter the word Cost

Your spreadsheet should now look like this:

We're going to put the price of each chocolate bar in cells B15, B16, B17 and B18. We'll have the following for the prices:

Mars Bars £0.35
Twix £0.29
Bounty £0.32
Others £0.40

So go ahead and enter those prices in cells B15, B16, B17 and B18 of your spreadsheet. (You can have your own currency symbol, instead of the UK pound.)
When you're finished, your spreadsheet should look like this one.

 

Paste Special

In the previous section, you created new areas of your spreadsheet that look like this:

We have prices in the B column. Under the Number heading, we're going to put how many of each chocolate bar we ate in one week: how many Mars Bars we ate will go in cell C15, how many Twix will go in cell C16, how many Bounty bars will go in cell C17, and how many other chocolate bars we ate will go in cell C18.

But we already have the weekly totals elsewhere in the spreadsheet, so we don't need to calculate them all over again. We can Copy and Paste the formula over to cells C15, C16, C17 and C18.

 

Paste Special in Excel 2007 to 2013

We have the weekly totals for each chocolate bar in the J column, under the Individual Totals heading.

• So highlight your four totals in the J column of your spreadsheet
• From the Clipboard panel, click Copy
• You'll see the marching ants again:

• Now, under the Numbers heading, click into cell C15
• Press the enter key on your keyboard to paste the numbers across

What you should notice is that something has gone wrong!

So what happened? Why have all those strange #REF comments appeared in the cells?

If you hold your mouse over the exclamation mark in the yellow diamond, you'll see this:

That complex error message means that Excel tried to paste the formulas over. But the cell references it has are all for the J column.

To solve the problem, we can paste the values over and not the formula.

• Click the left curved arrow at the very top of Excel to Undo (or press CTRL + Z on your keyboard)

• Highlight the four cells in the J column again
• From the Clipboard panel, click copy

• Highlight the cells C15 to C18
• Using your right mouse button, click anywhere in the highlighted area. You'll see the following menu in Excel 2007:

• From the menu, click Paste Special with your left mouse button
• The Paste Special dialogue box will appear:

The item that is selected by default is All, under the Paste option at the top. Select the Values option instead. Then click the OK button.
In Excel 2010 and 2013, however, the right-click menu looks like this:

Select Paste Special to see the submenu above. From the submenu select the Values option, which is circled in red in our image.
What you've just done is to tell Excel to paste only the Values (the numbers) across, and not the formulas we used to get these values.
If you did it correctly, your spreadsheet should look ours below:

Of course, it would have been easy just to type out the values again, since we only have 4. But if you have a lot of values to paste over then the Paste Special dialogue box or menu can save you a lot of time.