5 Must Know Paste Special Tricks

Add to Favorites
Author:

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:
    06fc6483e5484d6d46eb5b56fe42c6e5.png
    You can see that this cell contains a VLOOKUP() function.
    cdeda05ba311e01e927489517c5dd6fb.png
  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).
    b011ad4671e68731ce05085d7b72fc35.png
  4. Hit Alt V and then Enter.
    Once you hit Alt+E+S, a paste-special window will open and it looks like this:
    62a1ddb2401fc26b1dd7349db1c3d2e4.png
    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!

f3fb9bc40f6840fbdd6da86f463cff20.png

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
    66c0f8b2a4e95c792f4e457b307701fe.png
  2. Move to the new cell where you want only the formula and hit Alt + F and then Enter
    0c377c880b986eb31f91cc505553a16c.png

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:
    934822ad9f044abdafb1fb375359fa2c.png
  2. Go to the new cell and hit Alt + E + S + T and then Enter
    9349e26b2a468f8cd27c7cc626ca4104.png

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.
    365e639d16ece0deb94a6374867386ea.png
  2. Go to the new cell to paste it and hit: Alt + E + S + N and then Enter
    22f7873092c367d69644e0bfa625d85e.png

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:
    5a0d2e67b718555cfdc95a7c79e914c2.png
  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.
    a7e2e8e10a9f02f0635d969b5d8faef9.png

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.

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...
Quickly Move Cells Around a worksheet in Excel Cut/Paste Trick
Tutorial: Moving Cells around a worksheet in Excel is fast an easy. Below I have a table of forename...
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 contains...