How to Use Excel Paste Special and Paste Values

If you want to copy and paste text from one cell to another in Excel, it’s a simple matter of selecting Copy on the source cell and Paste on the destination cell. But, have you ever noticed those “extra” paste options like paste special Excel and Excel paste values? Ever wonder what they do?

paste special excel, wonder what they do

Image by Asier Romero

The “Normal” Paste Function

First, we need to understand what the normal Paste function in Excel does. It may not always do what you think. Let’s take a look at a few examples.

In our first example, we have the word “happy” in cell A1. If we copy cell A1 and paste it into cell B1, we see the word “happy” in cell A2, exactly as you would expect.

paste special excel, normal paste - magoosh

Our second example is a little different though. In cells A1 and B1 we have two numbers. In cell C1, we are using a formula to sum the values in cells A1 and B1. The result in cell C1 is the number 61.

In this example, if we copy cell C1 and paste it to C2 (the cell directly below), we end up with a value of zero. You may have expected the number 61. But in this case, Excel recognized the formula in cell C1 and instead copied and pasted the formula from C1 to D1 with updated relative cell references. Thus, the new formula in cell C2 adds the values of A2 and B2. Since those cells are empty, the sum formula result in C2 is zero.

Excel Paste values, paste special excel, copy-paste formula - magoosh

Check out our Excel Lesson Videos to learn more about using relative and absolute cell references in Excel.

Paste Special Excel

If our intent was to really copy the result (or value) of the formula in cell C1 to cell C2 (i.e., the number 61), we cannot use the “normal” Paste operation. Instead, we need to choose one of Excel’s other Paste operations.

To do this, we begin by doing the normal Copy operation on cell C1. But, for the Paste operation, we are going to click in cell C2, click the Paste button on Excel’s Home tab and then choose Paste Special.

Excel Paste value, paste special excel - magoosh

This will bring up a dialog box of available paste operations. Since we want to paste the value, not the formula, of cell C1 into cell C2, we would select the option for Values and then click the OK button.

paste special excel, dialog - magoosh

Cell C2 will now show the value 61, just as if we had typed the number 61 in the cell.

Excel Paste Values

Because pasting the value of a cell is a popular operation, Excel gives us a few shortcuts to accomplish this task. In the example above, we could have taken a shortcut by clicking the Paste button and then clicking on the leftmost icon (the clipboard with 123 on it) under the Paste Values section.

Excel Paste Values, shortcut 1 - magoosh

Another shortcut is to right click in the destination cell and eliminate the need to click the Paste button. In this case, we would right click in cell C2 for the paste operation and then choose the second-from-the-left icon (again, the clipboard with 123 on it) under Paste Options from the menu that appears.

Excel Paste Values, shortcut 2 - magoosh

Other Paste Options

Paste Values are just one of the several Paste Special options in Excel. Feel free to try the others to get a feel for what each of them does.

And be sure to check out our Excel Lesson Videos to learn more about using Excel!

Comments are closed.


Magoosh blog comment policy: To create the best experience for our readers, we will only approve comments that are relevant to the article, general enough to be helpful to other students, concise, and well-written! 😄 Due to the high volume of comments across all of our blogs, we cannot promise that all comments will receive responses from our instructors.

We highly encourage students to help each other out and respond to other students' comments if you can!

If you are a Premium Magoosh student and would like more personalized service from our instructors, you can use the Help tab on the Magoosh dashboard. Thanks!