Stay at Home and Learn

Microsoft Excel Tutorials

Computer Tutorials List

Excel 2007

Qualifications and Certificates

Copying and Pasting in Excel (continued)

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

What your spreedsheet should look like

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

The marching ants

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:

The #REF comments in a cell

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:

Paste Special in Excel

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

The Paste Special dialogue box

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:

The figures Pasted

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.

<-- Back One Page | Move on to the Next Part -->

<--Back to the Excel Contents Page