Now that we have covered the different ways of getting into ‘Paste Special’, we shall explore the different features in the ‘Paste Special’ pallete that can be leveraged when one ought to paste the items copied. For those who have not read our previous article, do have a look at it before proceeding further.
In the above image, there are umpteen variations of pasting the copied data listed under 2 categories – Paste & Operation. We shall demonstrate each of them as follows. The default selection of ‘All’ in the dialog box above is the conventional option that pastes the contents on the destination cells as is. So, we might very well skip this & move on with the other options.
Exploring the Paste Section
We shall explore each of the options under the Paste section of the above dialog box. So, for all the options the Operation section is to be set with ‘None’ as a selection.
Paste – Values
Select the ‘Values’ option after copying the cells & hit OK. All the formatting applied to the copied cells is ripped off & only the values are pasted in the destination column G as shown below.
Paste – Formats
If one is only interested in copying the format of the cells rather than its contents, then choose the ‘Formats’ option after copying the cells & hit OK. The source formatting is now applied to the destination cells in column G.
Paste – Comment
To paste only the comment given in a cell, copy the cell with the comment & then use the Comments option in the ‘Paste Special’ dialog box followed by clicking OK. Only the comment shall be pasted in the destination.
Following is the image with a cell containing a comment & is copied.
G58 is selected as the destination for pasting & Comments option is chosen in the ‘Paste Special’ dialog box.
It can be observed that only the comment is pasted in the destination cell as given in the below image.
Paste – Validation
To paste the cells with the cell validation feature select the ‘Validation’ option in the dialog box after copying the cells & then click OK. Following is an image with a cell having a list of contents to validate its entry.
The destination cell is chosen as G59 & then the Validation option is chosen in the ‘Paste Special’ dialog box followed by clicking OK.
Paste – Formula
If only the formula is to be copied rather than the contents of the cells, the same can be done by selecting the Formula option in the ‘Paste Special’ dialog box.
Those which are on the right of the Paste section as indicated below are self-explanatory features so we shall skip to the features in the Operation section.
Exploring the Operation Section
In this section ‘Paste Special’ goes the extra mile for you by performing the basic arithmetic operations. Consider the following list of numbers that are to be incremented by 5. Type in 5 in the adjacent cells as shown in the below image.
Copy the numbers under column G and then select the adjacent cells in column H as shown below.
Get into the ‘Paste Special’ dialog box & choose the Add option to increment each of the copied numbers by 5. Click OK.
The results in column H are increased by 5 as shown in the below image.
The process is the same if one needs to perform other arithmetic operations such as subtracting, multiplying & dividing.
Conclusion
Hope now you would be familiar with the many features of ‘Paste Special’. For more such tips and tricks on using MS Excel, please do look into our space QuickExcel. Until then, Cheers!