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?
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.
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.
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.
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.
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.
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.
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.