Examples of Paste Special (all options)

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

Alt + e then s

With Excel 2007, Excel 2010 and Excel 2013

Alt + Ctrl + v

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)

Related posts

Permanent link to this article: https://www.excel-exercise.com/examples-of-paste-special-all-options/

Leave a Reply

Your email address will not be published.