5 Must Know Paste Special Tricks

The 5 best things you can do with the Paste Special command in Excel, with examples.

Sections:

Paste Values

Paste Formulas

Paste Formats

Paste Validation

Transpose Shortcut

Notes

Paste Values

Turn formulas and functions into the visible values that they output to the spreadsheet.

Quick Steps: Select cell > Ctrl + C > Select destination cell > Alt + E + S + V > Enter

1. Select cell that contains the formula:
   
   You can see that this cell contains a VLOOKUP() function.
   
2. Hit Ctrl + C to copy the cell.
3. Go to the cell where you want to paste the visible value; you can overwrite the current cell, if you want, by leaving the copied cell as the currently selected cell (this would remove the formula from the spreadsheet completely).
   
4. Hit Alt + E + S + V and then Enter.
   Once you hit Alt+E+S, a paste-special window will open and it looks like this:
   
   The final letter of the above keyboard shortcut is what selects the feature to use from this screen, and hitting Enter accepts the selection and is the same as clicking the OK button.
5. That's it!

The new cell contains only the visible values from the previous cell.

Paste Formulas

Copy only formulas from one cell to another; this means that no formatting will be copied.

Quick Steps: Select cell > Ctrl + C > Go to new cell > Alt + E + S + F > Enter

1. Select the cell whose formula you want to copy and hit Ctrl + C
   
2. Move to the new cell where you want only the formula and hit Alt + E + S + F and then Enter
   
   

Notice here that absolutely no formatting copied over, not even the number formatting. The formulas in both cells are the TODAY() function, however, the new cell doesn't even have date formatting with it and so you see the serial number format used by dates in Excel. To get the date back, just format this cell as a date.

Paste Formats

Copy all formatting to a new cell.

Quick Steps: Select cell > Ctrl + C > Select other cell > Alt + E + S + T > Enter

1. Select and copy the cell with the formatting:
   
2. Go to the new cell and hit Alt + E + S + T and then Enter
   

Paste Validation

In-cell validation, including drop-down menus, can easily be copied using this shortcut.

Quick Steps: Select cell > Ctrl + C > Go to new cell > Alt + E + S + N > Enter

1. Select cell with validation and copy it.
   
2. Go to the new cell to paste it and hit: Alt + E + S + N and then Enter
   

Transpose

Turns a row of data into a column of data and vice versa.

Quick Steps: Select the range to transpose > Ctrl + C > Select new cell with enough space below or to the right of it > Alt + E + S + E > Enter

1. Select range to transpose and copy it:
   
2. Go to new cell that has enough space below it or to the right of it (depending on if you are transposing a row to a column, or the other way around).
   Select the new cell and hit Alt + E + S + E and then Enter.
   

Feel free to delete the original data after you transpose it.

This feature is often used after you use Text-to-Columns to break-up data into many cells in a single row.

Notes

You can also access paste-special when you copy data and then right-click a cell and click Paste Special.

Download the sample file for this tutorial to work with the above examples in Excel.