There are situations where a standard copy-paste destroys the presentation of your worksheet. To avoid that it's better to use Paste Special.
Principle of paste special
Once the "copy" has been activated, you can display a specific dialog box that offers a wide range of paste options. To select Paste Special, just click on the arrow under the Paste icon and then on the Paste Special option at the bottom of the list.
And the following dialog box appears.
Let's see in this article the different way to use these options.
Paste Special FORMULA
This option is useful to keep the formatting of your documents. When you copy your formulas, you keep the formatting of each cell.
Watch the video to understand how to use it:
Paste Special VALUE
When you use the random functions of Excel or the TRIM function to remove the spaces in a string, you don't want to keep the formula. You just want to keep the result of the formula.
So in that case, you can use the option Paste Special in Value to convert the formula to its result in a cell.
Paste Special OPERATION
This option is not the most popular but is very useful when you have to apply a basic calculation to a huge range of cells.
For instance, if you want to divide all your data by 100 (for a percentage for instance) or by 1000 (to display your figure in $K).
It is also very useful to convert whole numbers to hours 😍😎
Paste Special TRANSPOSE
This option allows you to change the orientation of a table from column to row (or the other way round).
The best example to use this option is to convert a calendar with dates in the columns and to one with dates in the rows.
Paste Special Skip Blank
If you have to update only few cells inside a list of data, this option is very useful and will save a lot of time.
Shortcut for Paste Special
|Ctrl + Alt + V
|To open the dialog box Paste Special
Then you press the letter corresponding to one of the underlined letters to enable this option (for example, if you want to enable the option value, simply press the V key)