Paste Special Function

PASTE SPECIAL :
Copy Paste data is the most performed action in Excel. Normally we use simple copy-paste, but “Paste” option have its special options / controls, which will be the most helpful option in our routine calculations, if understand carefully.

Below are the items of “Paste Special” Dialogue Box, which we will understand in this blog.



Mainly the above dialogue box contents three parts : 

• Paste: This option enables you to paste selectively. For example, you can paste only formula, value, formats, comments, column width etc.
• Operations: This options enable you to perform actions like addition, subtraction, multiplication and division etc..
• Special Options such as Skip Blanks & Transpose: These are the most effective options of paste special, which allows you to skip the blank cells and rearrange the data while pasting the data.

“PASTE” OPTIONS:
 

Paste option	Action
All	Pastes all cell contents and formatting of the copied data.
Formulas	Pastes only formulas of the copied data as entered in the formula bar.
Values	Pastes only the values of the copied data as displayed in the cells.
Formats	Pastes only cell formatting of the copied data.
Comments	Pastes only comments attached to the copied cell.
Validation	Pastes data validation rules for the copied cells to the paste area.
All using Source theme	Pastes all cell contents in the document theme formatting that is applied to the copied data.
All except borders	Pastes all cell contents and formatting applied to the copied cell except borders.
Column widths	Pastes the width of the copied columns to another columns
Formulas and number formats	Pastes only formulas and all number format option from the copied cells.
Values and number formats	Pastes only values and all number format options from the copied cells.

Table : 

	A	B	C	D
1	Product	Price	Quantity	Total Value
2	Pencils	10	100	1000
3	Pens	30	100	3000
4	Eraser	10	200	2000
5	Book	100	50	5000
6	Sketch Pen	50	300	15000

Example_1 : Paste special “Value”

Steps :• Copy the data range A1:D1
• Keep the cursor at A10 where you want to paste the value
• Right Click & select the option “Paste Special” – Value”

Result 

	A	B	C	D
10	Product	Price	Quantity	Total Value

 

Example_2 : Paste special “Formats”
Steps :
• Copy the data range A1:D1
• Keep the cursor at A10 where you want to paste the formatting
• Right Click & select the option “Paste Special” – Formats”

Result 

	A	B	C	D
10

Example_3 : Paste special “Comments”
Input : 

In the above table, Cell “A2” contents the comment as “Hello”

Steps :• Copy the cell A2
• Keep the cursor at the Cell “A10”, where you want to paste the Comment
• Right Click & select the option “Paste Special” – comments”

Result

 

Example_4 : Paste special “Validation”
Steps :

• Copy the data range A1:D6, where we have already applied the validation for the list as below: “Pencils, Pens, Eraser, Book, Sketchpen”
• Keep the cursor at G2, where you want to paste the validation
• Right Click & select the option “Paste Special” – validation”

Result : You can now choose the item in Cell “G2” by picking the items from the validation list as below as shown in the below result.

  





Example_5 : Paste special “Column Width”
Input : We have increased the Column width of A as 28.25 points :



Steps :

• Copy the column A,
• Right Click on column G, for which you want to set the column width
• Select the option “Paste Special” – Column widths”

Result :



“OPERATIONS” OPTIONS:

Operation	Action
None	Specifies that no mathematical operation will be applied to the copied data.
Add	Specifies that the copied data will be added to the data in the destination cell or range of cells.
Subtract	Specifies that the copied data will be subtracted from the data in the destination cell or range of cells.
Multiply	Specifies that the copied data will be multiplied with the data in the destination cell or range of cells.
Divide	Specifies that the copied data will be divided by the data in the destination cell or range of cells.
Skip blanks	Avoids replacing values in your paste area when blank cells occur in the copy area when you select this check box.
Transpose	Changes columns of copied data to rows, and vice versa when you select this check box.
Paste Link	Links the pasted data on the active worksheet to the copied data.

Example_1 : Paste special “Operations” – “Add” : Performs the “Add” operation on the selected data.

Input: I have entered a value 200 in Cell D2



Steps :

• Copy the Cell D2,
• Select the data Range D5:D9
• Right Click & select the option “Paste Special” – “Add” as below

Result : Value of data range D5:D9 has been increased by 200.


• Same way other options like Subtract, Multiply, Divide will work.

SPECIAL OPTIONS SUCH AS “SKIP BLANKS & TRANSPOSE”:
SKIP BLANKS : This options allow you skip the blank cells while pasting the data.

Input : 

 









Steps :
Copy the data range B2:B5
Select the data range C2:C5
Right click – Paste Special – select the option “Skip blanks”

Result :


 
TRANSPOSE : This options allow you rearrange the cells while pasting the data.
Input :



Steps :
Copy the data range A1:B5
Keep the cursor at Cell A8
Right click – Paste Special – select the option “Transpose”

Result : 

Shortcuts Keys 

1. To Paste Values only : Alt+E+S+V + Enter.

2. To Paste Formatting only : Alt+E+S+T + Enter.

3. To Paste Formulas only : Alt+E+S+F + Enter.

4. To Paste Comments only : Alt+E+S+C + Enter.

5. To Paste Validation only : Alt+E+S+N + Enter.

6. Set Column width : Alt+E+S+W + Enter.

7. Transposed : Alt+E+S+T + Enter. (You can also use Transpose option by selecting any operation & paste options. For that, you have to select the underlined text below the respective options of Operations and paste)