There are situations where a standard copy-paste destroys the presentation of your worksheet. To avoid that it's better to use 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.
This option is useful to keep the formatting of your documents. When you copy your formulas, you keep the formatting of each cell.
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.
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).
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.
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.