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)