«

»

Paste Special

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.

You select one of these options in this dialog box and you validate to apply the special paste.

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)

Paste Special FORMULA

This option is useful to keep the formatting of your documents. Like that, when you copy your formulas, your protect your specific format for 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$) this option is the one you have to use.

Look at this video to see how to convert value in hour

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.

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


Leave a Reply

Your email address will not be published. Required fields are marked *