Premium Excel Course Now Available!
Build Professional - Unbreakable - Forms in Excel
45 Tutorials - 5+ Hours - Downloadable Excel Files
Instant Access! - Lifetime Access!
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
- Select cell that contains the formula:

You can see that this cell contains a VLOOKUP() function.

- Hit Ctrl + C to copy the cell.
- 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).

- 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.
- 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
- Select the cell whose formula you want to copy and hit Ctrl + C

- 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
- Select and copy the cell with the formatting:

- 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
- Select cell with validation and copy it.

- 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
- Select range to transpose and copy it:

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