Copying and Pasting in Excel (continued)

-  Your spreadsheet from the previous part of the lesson should look like the one below.

For the Number column, you can Copy and Paste the figures from the Individual Totals column.

• So highlight the four totals in the J column
• Click on Edit from the menu bar
• Click on copy
• You get the marching ants again around your highlighted cells

• Now click inside cell C15 under the Numbers heading
• Hit the Return key on your keyboard to paste the numbers to the new location
• Something odd happens. Your spreadsheet will look like the one below:

So what went wrong? Why did we get all those #REF comments in the cells?

The problem is that there's a formula underneath the numbers we copied. And Excel tried to paste the formulas as well as the numbers. But the formulas are for the J column, and the cell references belong to that column. They don't belong in the C column. So Excel gives you the #REF error comments.
To solve the problem, do this:

• Click on Edit from the menu bar. From the drop down menu, click on "Undo Paste"
• Highlight the four cells in the J column again
• Click on Edit from the menu bar. From the drop down menu, click on "Copy"
• Click inside cell C15 and highlight the four cells down to C18
• Click with your right hand mouse button anywhere in the highlighted area. A menu pops up, as in the picture below:

• When you see the pop up menu, click on Paste Special with your left hand mouse button
• A dialogue box pops up - Paste Special
• Right at the top of the dialogue box there will be a black circle next to the word "All"
• Click the word "Values"
• Then click the OK button at the bottom

When you click the OK button, only the values from the J column cells will be pasted, and not the formulas underneath them.

So your spreadsheet should now look like this:

With only four values to copy and paste, it's probably easier just to type then in again! But if you have lots of values to copy and paste, then Paste Special comes in really handy.

OK, the next thing to do is to work out the sums for the Cost column. We need another formula. But only one, because we can Auto Fill the others. But we need to multiply, and we'll do that in the next part.