Use paste special to copy values in Excel

excel logoSo here's the typical scenario. You've got some values in Excel that are the results of using a formula or formulas. You want to copy the resulting values to another area on the sheet, to another worksheet or to another workbook. The problem is, when you copy and then paste the information in the new location you are copying the formulas, not the values, so you don't get the information in the place that you wanted it. You then resort to, of all things, retyping. But wait, you don't really have to do that.

The following examples all refer to Microsoft Excel 2003. If you are using a version that is different than that your screens will look slightly different, but the basic functionality should be the same.

So, let's look at an example. I've entered some very basic data into a blank spreadsheet.

To create this data set I entered two values, 4200 and 6200 into cells B3 and B4, then expanded the cells down to row 13 (click here to see how). I then entered the value 1.234 into cell C3, and created a simple multiplication formula in D3 that used an absolute reference to that cell (click here to see how). I then again used the autofill feature of Excel to multiply all of the initial column by cell C3.


excel paste special 01


Now, for whatever reason you want to copy the values in column D into another sheet. So you highlight the cells, right click with the mouse hovering over the selected cells and select copy:


excel paste special 02


Now you click on the tab for Sheet2, click on the cell where you want to start the paste and hit the key combination Ctrl - V (paste), and you end up seeing this:


excel paste special 03


Rats, that's not what you were looking for. It copied the formulas, not the values that you wanted. So now you resort to retyping the values from Sheet1 to Sheet2, right? Wrong. You use paste special. Go back to Sheet1 and reselect the values and recopy just like you did above. Now, when you go to Sheet2 instead of selecting paste from the menu or the shortcut key I want you to right click on the cell where you want to start the paste, and select Paste Special from the pop-up menu:


excel paste special 04


From the dialog box select Values:


excel paste special 05


Then click OK. You should a end up with this;


excel paste special 06


The values you wanted, copied over from Sheet1. Keep this in mind the next time your first inclination is to retype something. You hardly ever have to retype, there almost always an easier way.