Paste options in Excel
In Excel, copying and pasting are one of the most commonly used actions. When we refer to copy and paste, the first thing that comes to mind is CTRL + C (to copy) and CTRL + V (to paste). But do you know copying and pasting operations are not limited to this operation, and there is much more scope to do?
This tutorial covers all about pasting operations in Excel, including the Excel Paste Special feature and other helpful Paste shortcuts.
What is Paste Special option?
We all copy a cell in Excel and paste it into another cell. What happens after that?
Excel copies not only the text of a cell but also the formatting, including the font style, font type, font colour and other options.
There are certain situations where we only want to copy the contents of the cells excluding the formatting, or we want to duplicate only the formatting of the cells excluding the cell values, or to copy only the embedded formula. What to do in such cases as the simple copy-paste mechanism won't work?
For such cases, Excel has introduced the Paste Special Functionality that provides various pasting options. There are various predefined options available in Paste Special Window. For instance, you can only paste the formulas, or the values or the comments of any particular cell.
How to Access the Excel Paste Special feature?
There are various methods using which you can access the Paste Special option.
Select and copy the cells for which you want to apply the paste special option. Once copies you can use any of the below given method to access the Excel paste special feature:
Method 1: Using the Ribbon
Method 2 : Using Keyboard Shortcut
Use the Excel Paste Special Shortcut - Alt + E + S + V.
Method 3: Using Right-click Menu
Right Click on the cell where you want to paste, and then select paste special.
Paste Options in the Paste Special window
As shown above, you will have the specified window. Excel provides various options to paste selectively. For example, you can choose to only paste formulas, or values, or formats, or comments, etc.
To use these options, all you need to do is to go to the specified and copy the values (press CTRL + C). Select the cells where you want to paste them and open the Excel Paste special window. From the resulting dialogue box, select the desired option. Below given is the table explaining all the options available in the Paste Special window:
Paste Special Operation Options
Operations option is an exclusive feature of Paste Special. It enables the user to perform the required operations quickly by directly changing the cell contents without applying any Excel formula.
For instance, let's suppose you are given a dataset carrying numbers in millions, and you are asked to convert the given number into billions. The first approach that will come to you is to create an Excel formula where you will divide the given number by 1000. Though you can perform the same operation using the Paste special options as well.
Below given are the steps:
Paste special is not limited to divide operation. You can choose any operations for your Excel data using the above steps. Go for it and give it a try now!
Additional Paste Special Options
Besides the above options, Excel Paste special provides two more features in its dialogue window.
Let's cover both the options using examples.
While copying and pasting data on Excel collectively, many times we paste blank cells as well. And later we waste time by deleting.
With Paste special we can skip those blank cells directly by using the Skip Blanks option. This option skips copying and pasting the blanks.
For example, if you want to split your data into 2 columns and you want to combine it in a single column follow the below steps:
We often transpose our data in Excel. What if we transpose the data easily before pasting it to new cells? As the name suggests, this paste special option transposes the data whenever you paste it into your Excel worksheet.
For example, if you want to transpose your data from horizontal orientation to vertical follow the below steps:
Excel Paste Special Shortcuts
Once you start using Excel Paste Special feature upgrading the skills will be essential. The better way to speed up the process and save time is by using a few keyboard shortcuts.
Following is the list of some shortcuts key that you can use while working with the paste special option:
While it may be difficult to remember all these formulas in a single go, it's a good approach to use them consciously a couple of times, and then it will quickly come to you like the back of your hand.