All About Paste Special Feature in Excel

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.

Copy and Paste Demonstration

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:

Paste Special Navigation

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.

Right Click Paste Special Navigation

All the above ways would open the ‘Paste Special’ dialog box as can be seen from the screenshot below:

Paste Special Dialog Box

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.

Paste Options in Paste Special Dialogbox

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.

Paste Special Format Demonstration

‘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.

Operation Options Paste Special Dialogbox

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.

Multiply Operation - Paste Special Dialogbox

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.

Skip Blank in Paste Special Dialogbox

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).

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:

Paste Link in Paste Special Dialog Box

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.

Leave a Comment