While working with Excel or any other Microsoft Utility, the most used function is copying the data and pasting it to some other location. But this is not simply limited to copying the data using Ctrl+C and pasting it using Ctrl+V. There are a lot more things that a paste function in excel does. These additional features are named as ‘Paste Special’ functions in Excel. In this blog, we would unlock all the paste special functions that Microsoft has provided in Excel.
Also, at the end of this blog, we would learn some of the most used Paste Special keyboard shortcuts.
Firstly, let us navigate to the location where you can find the Paste Special Function in Excel.
Some Basics About Paste Special Excel Function
Refer to the below image. The cell B2 contains the text ‘Excel Unlocked’ with the following property: Font Color – ‘Red’, Text is bold, Cell Fill Color – ‘Yellow’, Cell has a thick border.
Now, select the cell press Ctrl+C to copy and select any other cell and press Ctrl+V. What did you notice? The excel copies everything – the text, the formatting, and even the formula.
But what if you just want to paste the text ‘Excel Unlocked’ without the formatting.
The Paste Special Excel Function will be useful to do this.
Ways to Navigate Paste Special Feature in Excel
You can navigate to the ‘Paste Special’ function using various paths.
First – Using the ‘Paste Special’ option on the ribbon. This is the first option in the ‘Home’ Tab, under the group ‘Clipboard’. Refer to the below image:
Also Read: Paste Special Method in VBA
Second – You can use the keyboard shortcut Alt+H+V+S to use the available Paste Special functions. Even the Alt+E+S keyboard shortcut does the same
Third – Another keyboard shortcut – Ctrl+Alt+V (this one is the most used and common way to access the Paste Special features in Excel)
Fourth – Right-click on the cell where you want to paste and there you would find the ‘Paste Special’ option button.
All the above ways would open the ‘Paste Special’ dialog box as can be seen from the screenshot below:
Let us now learn each of the options and its functionality.
Paste Special Dialog – ‘Paste Section’
The first section of this dialog box is the ‘Paste’ section. Under this section, you can find multiple radio buttons. These radio buttons enable you to do selective pasting, like pasting only the formulas, or only the comment or the values, etc.
Also Read: PasteSpecial VBA – Other Examples
A simple example – Suppose, cell B2 contains the text ‘Excel Unlocked’ with the following property: Font Color – ‘Red’, Text is bold, Cell Fill Color – ‘Yellow’, Cell has a thick border.
To copy only the cell format, copy cell B2, then select the destination cell (D4) and open the ‘Paste Special’ dialog box (using any of the above ways). In there, select the radio button ‘Formats’ and click ‘OK.
As a result, the excel would only copy the cell format (i.e. font, color, borders, etc.) and not the text or the formula.
‘Operation’ Section – Paste Special Dialog
The next section of this dialog box is the ‘Operation’ section. It enables us to perform the mathematical operations of add, subtract, divide, and multiply without the aid of any formula.
For example, you have certain numbers written in column A and you want to multiple all these numbers by 1000. One was is to give a formula like A1*1000. However, you can also use the paste special operation functions to achieve this task. No need to write any formula.
Following steps will help you:
Firstly, write 1000 in any of the cells in your workbook. I have written it in cell C1. And copy this cell C1.
Now, select the range of cells that contains the numbers (to which you want to multiple 1000) and open the ‘Paste Special’ dialog box using keyboard shortcut Ctrl+Alt+V. You may use any other method as well.
In the operation section of this dialog box, select the operation that you want to perform and click on OK. As I want to multiply the value 1000 to the numbers in the selected cell range, I have selected the radio button ‘Multiple’.
As a result, the excel would multiple the values in column A with the value in cell C1 i.e. 1000.
Similarly, you can also perform the operations of addition, subtraction, and division.
Two Additional Options
In addition to the ‘Paste’ and ‘Operations’ Paste special features, there are two more options – ‘Skip Blanks’ and ‘Transpose’.
Let us look at each of these options.
Skip Blanks – The skip blanks checkbox skips the blank cells in the copied range while pasting it. Refer to the below demonstration.
As you can see from the above demonstration, the excel ignored the blank cells A2 and A6 in the selection, and therefore, did not override the content in the cells B2 and B6 respectively.
Transpose – This feature enables us to change the orientation of the cell content (Columns to row and vice versa).
Paste Link Button
The ‘Paste Link’ button is an unrecognized feature amongst many excel users. But its utility has a very great scope.
The ‘Paste Link’ button in the Paste Special dialog box pastes the copied cells and also creates a link to the original cell. Follow the below steps:
Important Paste Special Shortcuts in Excel
Below are the most important paste special keyboard shortcuts that you should know. They will come in use now and then.
Pasting Only the Values : Alt+E+S+V+Enter
Pasting Only the Comments : Alt+E+S+C+Enter
Column Width Same as Copied Cells Column Width : Alt+E+S+W+Enter
This brings us to the end of this blog. Share your views and comments in the comment section below.