In addition to the standard data pasting, Excel allows you to paste data in a special way. Unlike normal pasting, in Paste Special you have more control over how the pasted content will look like. The Paste Special works only when you copy (not cut) the contents of a cell.
In order to use the Paste Special feature, copy the contents of the selected cells and choose a place where you want to paste these values. Use the right-click to open the contextual menu.
Here, you will find the most popular options for pasting. If none of them meets your needs, you can click the triangle on the right side of Paste Special to display additional options.
The paste options are divided into three groups: Paste, Paste values, and Other Paste Options.
This is the standard method for pasting data. It has the same effect as using the Ctrl + V keyboard shortcut.
In this case, Excel remembers that the pasted data has to be treated as a formula. Sometimes when you open more than one instance of Excel and then you use the standard paste method (Ctrl + V), it might be treated as an ordinary value. Therefore, in such cases, it is safer to use Copy as Formula.
Formulas & Number Formatting (O)
In this pasting method, the pasted value keeps the format. For example, 4.0934% is pasted as 4.0934% and not, for example as 4.09%.
Keep Source Formatting (K)
This method is similar to the one above. The difference is that this method also copies such changes as font and background formatting.
No Borders (B)
If you copy from the cell with borders and then you paste the data to another cell, all the formatting but borders are copied.
Keep Source Column Widths (W)
Pastes cells in the same way as the Keep Source Formatting method. The only difference is that it copies also the width of the column.
You can use this method if you want to switch columns and rows.
Let’s suppose that the cell displays value 6, but, in fact, this is the formula (=2+1+3). With this type of pasting the value 6, not the formula is copied.
If you enter 4.0954% + 1% then the displayed text will be 5.10% (rounded 5.0954%). Paste this value using the Values method and it will be displayed as 0.050954.
Values & Number Formatting (A)
Let’s use the formula from the previous example (=4.0954% + 1%). In this case, the formatting will be preserved. However, it won’t be remembered as a formula, but as value 5.0954%.
Values & Source Formatting (E)
This pasting method is similar to Values & Number Formatting, with the difference that it also preserves cell formatting.
Other Paste Options
In this case, we don’t copy the cell value, but its formatting (background color, size, font color, etc.).
Paste Link (N)
This method pastes a link to the copied cell. If you copy cell A1 to B1, then cell B1 will contain a link to cell A1 (=$A$1). That means, that when you change the contents of cell A1, the contents of cell B2 will change automatically. In this case, the formatting is not preserved.
Pastes the contents of a cell together with formatting and saves it as a picture. Here, you can perform the same operations as on any other image.
Linked Picture (I)
Similar to the previous example it pastes contents as a picture. This time, however, the picture is linked to the copied cell and, therefore, it is updated every time you change the contents of the cell.
The Paste Special button
If you need more options, click the Paste Special button.
Here, you will find such features as automatic addition, subtraction, division or multiplication. Additionally, you can copy the only width of a column or its validation.