There are situations where a standard copy-paste destroys the presentation of your worksheet. To avoid that it's better to use the 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 collages. To enable this option, you must click on the arrow under the Paste icon and you're at the bottom of this list the Paste Special option
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, your protect the format of each cell.
Watch the video to understand how to use it
Paste Special VALUE
When you use the random functions of Excel or also the function TRIM to remove the spaces of a string, you don't need to keep the formula but the result of the formula.
So in that case, you must use the option Paste Special in Value to convert the formula by its result in a cell.
Paste Special OPERATION
This option is not the most popular but very useful when you have to apply a basic calculation to a huge range of cell.
For instance, if you want to divide all you data by 100 (for a percentage for instance) or by 1000 (to display your figure in K$).
Also, it is very useful to convert whole number in hours 😍😎
Paste Special TRANSPOSE
This option allows you to permute 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 column and then put the dates in row.
Paste Special Skip Blank
If you have to update inside a list of data only few cells, this option is very useful and will save a lot of time.
Shortcut for Paste Special
You can display the same dialog with these 2 shortcuts
With Excel 2007, Excel 2010 and Excel 2013
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)