As you might already know, Paste special is a gateway to many powerful operations in Excel. To use Paste Special, just copy normally, then use the shortcut Ctrl + Alt + V in Windows, Ctrl + Command + V on the Mac. Using this shortcut doesn’t actually finish the Paste, it simply displays the Paste Special dialog, where you can choose which options you want. In Windows, you can type a letter to select options. On the Mac, you’ll need to type the Command key plus a letter. I’ll run through a few examples. This table is a mix of regular data and formulas, along with various formatting. In cases where you just want to get the data without any formulas, use Paste Special with V for Values. This converts all formulas to values and strips all formatting. Paste special with U, maintains number formatting, but gets rid of formulas. Paste special with R keeps formulas and number formats, but drops everything else. This can be a good way to clean up a table copied in from somewhere else. When you want to convert to values, and retain all formatting, use Paste Special twice. The first time, use with V for values. Then, use it again with T to bring in formatting. Now you’ll have a replica of the original data without formulas. You can also use Paste Special with to copy formatting only. In this example, new data has been pasted below with no formatting. But I can copy formatting from a row above, and then paste it below, using paste special with T. I could use the format painter, but Paste Special is a lot is faster when you have large sets of data. Paste Special also includes options to paste things like comments, data validation, everything but borders, and even column widths. So, for example if we copy this sheet, then mess up the columns. I can come go back to the original, copy the work area, and then use Paste Special with W to past column widths. This can be a handy way to make one sheet look like another. Paste Special also gives you a way to perform math on cells without using formulas. For example, I can add exactly 1 week to these dates by copying 7 in this cell, and then using Paste Special with V for values and D for add. In a similar way, I can add 10% to these prices by copying 1.1 and then using Values > with V for values and M for multiply. Finally, there are two more interesting options at the bottom of the Paste Special dialog. Skip blanks (Paste Special B) lets you copy information from one location to another and not overwrite data with empty cells. And the Transpose option, which is Paste Special + E, let’s you rotate vertical data to a horizontal layout…and vice versa.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
