We all might have used the CTRL+C (Copy) & CTRL+V (Paste) shortcuts the very first time we learn to operate in Windows OS. But have we really got into the depths to tap the fullest potential of the Paste function in MS Excel? In this article, we are going to explore the different possibilities that can be unlocked by using an extraordinary function in MS Excel – the Paste Special!
How to get to Paste Special?
Let us first get started by getting to know how to summon the ‘Paste Special’ function. Similar to the ‘Paste’ function, its derivative ‘Paste Special’ too is a conditional feature in MS Excel. What this means is unless anything is being copied, both the above commands won’t work even if you want them to. This can be a no-brainer, but something most tend to forget while using ‘Paste Special’.
Consider the following data in which the entries under column D are copied as shown below.
Now there are 3 ways in which one can summon ‘Paste Special’ viz.
- From Home Tab
- Using Keyboard Shortcut
- The Accelerator Way
Paste Special From Home Tab
In this technique, one shall use the mouse to move the cursor into the clipboard section of the Home tab and click on the down arrow button below the Paste icon as shown below.
Now a drop-down menu with the many variants to paste the copied cells shall appear. We shall not waver our attention with these distractions & move straight ahead into the mothership of pasting contents by clicking on the Paste Special button at the very bottom of the list.
This shall take us to the Paste Special dialog box as shown below.
Paste Special Using Keyboard Shortcut
Those who feel that lots of clicking are involved in the previous method shall be comfy with this. Here we are going to press a few keys one after the other, but when a finger hits a key it should remain in the same position until all the other keys are also pressed.
CTRL+ALT+V
Use the above shortcut after copying the contents & this shall right away take you to the ‘Paste Special’ dialog box as shown below.
Paste Special Using the Accelerator Way
Those who are not comfortable with their fingers holding multiple keys at the might time can fancy this technique. It requires pressing the keys on the keyboard one after the other in a required sequence.
ALT+E+S
Press the above keys one after the other, after taking the finger off the previous key before hitting the next and the ‘Paste Special’ dialog box shall appear at once.
Paste Special – Transpose Pasting
Now that we have elaborated on the different ways of getting into the ‘Paste Special’ dialog box, let us explore its features starting with the ‘Transpose Pasting’. For those who remember The Matrix (not the Keanu Reaves one, but the one from Mathematics) it is the same thing but with copied contents.
The copied contents if listed one below the other horizontally shall be transformed one after the other sideways after being pasted in the destination cell when this option is invoked! The converse also holds good for this feature.
Once the destination cell for pasting is clicked upon, get into the ‘Paste Special’ dialog box using any of the above-listed techniques & tick the Transpose checkbox as shown above. Click OK.
The copied cells from column D are successfully pasted across row 63!
Paste Special – Skip Blanks
Pastes the content from the copied range skipping those which are blank. Copy the cells in column H & select the adjacent cells in column G as shown below.
Open the ‘Paste Special’ dialog box & tick the Skip blanks option. Click OK.
Only the numbers in rows 59 & 61 are replaced whereas that in row 60 remains untouched.
Conclusion
Eager to know more about the rest of the ‘Paste Special’ features, go on & read this article then! Hope now you would be familiar with summoning the ‘Paste Special’. For more such tips and tricks on using MS Excel, please do look into our space QuickExcel. Until then, Cheers!