5 Must Know Paste Special Tricks

Add to Favorites

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


Paste Values

Paste Formulas

Paste Formats

Paste Validation

Transpose Shortcut


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 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 CtrlC
  2. Move to the new cell where you want only the formula and hit Alt + 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


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.


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.

Question? Ask it in our Excel Forum

Downloadable Files: Excel File

Similar Content on TeachExcel
Vlookup on Dates and Times in Excel
Tutorial: Perform a lookup on dates and times in Excel: vlookup, hlookup, index/match, any kind of l...
Make Negative Numbers Positive in Excel and Vice Versa
Tutorial: I will show you a few ways to change negative numbers to positive numbers and back again...
Quickly Switch the Columns and Rows of a Data Set in Excel
Tutorial: How to switch a data set in Excel so that the columns become rows and the rows become col...
Convert Numbers Stored as Text to Numbers in Excel
Tutorial: I'll show you 4 ways to convert numbers stored as text to numbers in Excel.  This situat...
Make All Numbers in a List Positive in Excel
Tutorial: Take a list of numbers and make them all positive, regardless of whether the list contain...
5 Bite-Size Vlookup Tricks for Excel
Tutorial: 5 tricks for using Vlookup on data that might not always be 'friendly.' This includes: Re...