Principle of paste special
After activating the 'copy' function, you can access a dialog box with various options. Click on the arrow beneath the Paste icon and select 'Paste Special' from the list at the bottom.
The following dialog box appears.
Let's see in this article what is the purpose of each one of these options.
Paste Special FORMULA
This option is useful for maintaining the formatting of your documents. When you copy your formulas, you keep the formatting of each cell. It's the best solution to not destroy colors or border style.
Paste Special VALUE
When you use Excel's random functions or the TRIM function to remove spaces in a string, you don't want to keep the formula. You only want to keep the formula's result. So in that case, you can use the option Paste Special in Value to convert the formula to its result in a cell.
Option OPERATION
This option is not the most popular but is useful when applying a basic calculation to a huge range of cells. For instance, you could divide all your data by 100 (for a percentage) or by 1000 (to display your figure in $K). It is also very useful to convert whole numbers to hours 😍😎
Option 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 to one with dates in the rows.
Option Skip Blank
If you have to update only a few cells inside a list of data, this option is very useful and will save a lot of time.
Shortcuts
| Ctrl + Alt + V | To open the Paste Special dialog box |
| Ctrl + Shift + V | Paste in Value what you have copied |
Then, you press the letter corresponding to one underlined letter to enable this option, like F, to paste the formula only.
