How to use Paste Special Effectively in Excel

By
There might be question in your mind: “What is the difference between Paste and Paste Special?” If you really had this question then you are reading perfect blog.

This blog will help you to answer your question. Also we will see how we can use each and every option from Paste Special to reduce our time and efforts. 

We can say that Paste Special is subset of Paste. Both the Paste and Paste Special does the same thing (to paste the data) but “Paste Special” paste the data in a special way. So let’s see what these special options available in it are.

YouTube Video



Where we can find “Paste Special”?

There are two ways by which we can reach up to Paste Special options. But for this we have to copy the data first which we want to paste. After we copy the data we can follow any of the below steps:

1. Click on the small downward arrow below “Paste” command in Clipboard group under Home Tab, we will see one option called as “Paste Special”.



2. Right click on any cell in excel worksheet where we want to paste the data. Excel mini toolbar will open. Then expand Paste Special option by clicking on Paste Special. Select Paste Special option from expanded selection.



One window named as “Paste Special” will appear if we follow any of the above steps. It looks like below.



Now, we will proceed further to see how we can use each and every option from paste special effectively. This post may take quite longer time to read but it is very useful and you can save a lot of time in future while working on big data in excel.


1. Paste Special: All

It will paste all the copied data as it is without changing any formatting and style.

For example, if we want to paste the data from original location to desired location without changing any formatting then we can use the “All” option from Paste Special. We will get the result in the Desired Location column as shown in below image.
 


Note: Here data, background color, font color everything is copied to the desired location.

2. Paste Special: Formula

If we want to copy only formulas from original location to desired location to desired location then we can use this option.

For example, in below image we have used a formula to add 1st value and 2nd value in column “Sum at Original Location”.


Copy the data from this cell and select “formula” option from Paste Special. We will get the result as shown in column “Sum using Paste Special Formula”.



Note: Here only formula is pasted which we can see in the Formula Bar, yellow color in cell background is not pasted.



3. Paste Special: Values

If we want to copy just values from original location to desired location then we can use this option.

For example, in below image we have used a formula to add 1st value and 2nd value in column “Sum at Original Location”.


Copy the data from this cell and select “Values” option from Paste Special. We will get the result as shown in column “Sum using Paste Special Formula”.



Note: Here only value “9” is pasted which we can see in the Formula Bar. Yellow color in cell background or formula to add two values is not pasted.

4. Paste Special: Formats

If we want to use same format as that of original location to desired location then we can use this option.

For example, in below image we have used a formula to add 1st value and 2nd value in column “Sum at Original Location”.


Copy the data from this cell and select “Formats” option from Paste Special. We will get the result as shown in column “Sum using Paste Special Formula”.



Note: Here only format (i.e. yellow color from cell background) is pasted, formula to add two values or value of sum is not pasted.
 

5. Paste Special: Comments

If we want to use same comments from original location to desired location then we can use this option.

For example we have inserted a comment “This cell contains alphabet A” at Original Location.


Copy the data from this cell and select “Comments” option from paste Special. We will get the result at desired location as shown in below image.



 

Note: Here only comment (“This cell contains alphabet A”) is pasted, alphabet A and yellow cell background color from original location is not pasted.


6. Paste Special: Validation

If we want to use same validation on desired location which is available at original location then we can use this option.

For example, in below image we have created validation list of 04 characters A, B, C and D.


Copy the data from this cell and select “Validation” option from paste Special. We will get the result at desired location as shown in below image.


Note: Here only validation list is pasted, yellow color from cell background is not pasted.

7. Paste Special: All using source theme

If we apply a theme to our data which is on Sheet 1 and we want to paste these data on sheet 2 by keeping same theme as that of sheet 1, then we can use the option “All using source theme” from Paste Special.


8. Paste Special: All except borders

This option will paste the data from original location to desired location but without borders (i.e. Borders will not be available for the data we paste in desired location.)

In below example we have copied the data from column Original Location and used paste special option “All except borders”. We can see that all the data is copied as it is but without borders.



Note: Here only borders are not pasted, cell background colors, font colors are pasted as it is.

9. Paste Special: Column Widths

If we want to copy the data from original location to desired location by keeping same column width as that of original location, then we can use Column Widths option from Paste Special.

For example in below image first we will set the width of cell “B2” to “25” and width of cell “C2” to “15”.

Now, we will copy the cell the “B2”.



Now we will select cell “C2” as our desired location and choose option “Column Widths” from Paste Special. As a result we can see that the width of cell “C2” is now changed to “25” i.e. similar to width of cell “B2”.



Note: Here only column width will paste, data from original location will not paste on desired location.


10. Paste Special: Formulas and number formats

If we want to copy just formula with number formats then we can use this option.

For example, we have to calculate sum of two values which are listed as shown in below image. The values are having $ sign which shows it has “Accounting Number Format”. We have applied a formula to calculate sum of 1st value and 2nd value in yellow highlighted cell.


Now if we want to copy both “Formula to calculate sum” as well as “Accounting Number Format” for all the remaining values, then we can use “Formulas and number formats” option from Paste Special. 

Just copy the yellow highlighted cell. Select entire range where we want to paste our data. Now, select “Formulas and number formats” option from Paste Special. The resulted values are highlighted in blue in below image.



Note: Here only formula and number format is copied from original location to desired location. Yellow cell background color is not pasted. Blue color in above image is just for your easy reference.

11. Paste Special: Values and number formats

If we want to copy just values and number formats then we can use this option.

Here we will take same example as given above. We have applied a formula to calculate sum of 1st value and 2nd value in yellow highlighted cell.



Now if we want to copy both “Value” as well as “Accounting Number Format” for all the remaining cells, then we can use Values and number formats option from Paste Special.

Just copy the yellow highlighted cell. Select entire range where we want to paste our data and select “Values and number formats” option from Paste Special. The resulted values are highlighted in blue in below image.



Note: In result, we can see only one value i.e. “9” in blue highlighted cells. The reason behind this is this option will not paste along with number format, only value along with number format will get pasted.


12. Paste Special: All merging conditional formats

This option will enable only when we have applied conditional formatting to our data. Without conditional formatting this option is disabled in excel.

For example, we have a data with conditional formatting applied to it and we want to copy both data as well as conditional formatting simultaneously to a desired location, then we can use this option from Paste Special.


13. Paste Special: None

This option will not perform any mathematical operation. It will paste the data as it is at desired location.

For example if we want to copy number “3” and paste it in place of values listed in column then select and copy the number “3”.


Now select the range of values and from Paste Special options select “None”. This will give us result as shown in below table with blue highlighted cells.



14. Paste Special: Add

If we want to add specific value to a range of values, then we can use this option.

For example we have numbers from 1 to 7 listed in a single column. And we want to add “3” to all these numbers.

Select and copy the value which we want to add (i.e. “3” in this case). Now select all the values where we want to add and select option “Add” from paste special. The resulted addition values we can see in blue highlighted cells.


15. Paste Special: Subtract

If we want to subtract specific value from a range of values, then we can use this option.

For example we have numbers from 1 to 7 listed in a single column. And we want to subtract “3” from all these numbers.

Now Select and copy the value which we want to subtract (i.e. “3” in this case). Now select all the values where we want to subtract and select option “subtract” from paste special. The resulted subtraction values we can see in blue highlighted cells.

  

16. Paste Special: Multiply

If we want to multiply specific value to a range of specific values, then we can use this option.

For example we have numbers from 1 to 7 listed in a single column. And we want to multiply these numbers by “3”.

Now Select and copy the value which we want to multiply (i.e. “3” in this case). Now select all the values where we want to multiply and select option “multiply” from paste special. The resulted multiplication values we can see in blue highlighted cells.

17. Paste Special: Divide

If we want to divide specific value from a range of specific value, then we can use this option.

For example we have numbers from 1 to 7 listed in a single column. And we want to divide these numbers by “3”.


Now Select and copy the value which we want to divide (i.e. “3” in this case). Now select all the values where we want to divide and select option “divide” from paste special. The resulted division values we can see in blue highlighted cells.

  

18. Paste Special: Skip Blanks

If we want to copy data skipping blank cells from a copied cell range containing blank cells then we can use this option.

For example, we want to copy range of values which contains blank cells to a desired location.

Non blank values are highlighted with blue color.


Now select and copy entire range of values which we want to copy. Then select the range of values on desired location where we want to paste these values and select option Skip Blanks from Paste Special.

As a result, only non-blank values will get pasted on desired location. It means, values are pasted on desired location by skipping blanks.


19. Paste Special: Transpose

It converts data from rows to columns and vice versa.

For example, we have numbers listed from 1 to 6 in range “A1:A6”. If we want to paste these numbers in rows, then we can use “Transpose” option from Paste Special.

For these follow below steps:
a. Select and copy the data listed in column.
b. Select the desired location where we want to paste the data.
c. Select “Transpose” option from Paste Special.

As a result, we can see the data from columns is now converted into rows.

 

20. Paste Special: Paste Link

It creates linked copy of the data listed on original location and paste it on desired location.

Linked copy means, if we change anything in original data then same changes will reflect in data pasted on desired location.

For example, I have listed few Excel topics which I have covered in my blog “Excel Tricks with Tejraj” in below table.



Now If I want to create a linked copy of this table on desired location then I can use “Paste Link” option from Paste Special. This will look like:


Now, we will change the data in original content as highlighted with yellow. The same changes we can see reflected in data with Paste Link.



This was detailed information about Paste Special. I hope you will use these options and save your time and reduce efforts and become a Smart Worker!!!


Microsoft Certified Excel Expert, Enthusiastic to teach excel tricks and basic excel concepts which will help to save time and efforts in our daily office work.

Comments

Post a Comment

Related Topics..

Fill Up, Down, Right & Left in Excel

By
Image
Excel has many great tricks which can help us to save our time while working on big data. In this blog post we are going to see one of those tricks where we can fill the data to the upwards of the selected cells, to the downwards of the selected cells, to the right of the selected cells and to the left of the selected cells. There are many situations where we may need to copy the data from selected cell to the upward, downward, left and right cells. But how can we do so?? In excel we can do so with the help of “Fill” command. This command we can found in: Home Tab --> Editing Group --> Fill Command. Let’s take an example. We have inserted a text “Tejraj” in a cell “D5”. Now we will see how to fill this text from cell D5 to Up, Down, Right & Left. Fill Down: 1. Let’s assume that we have to fill the data from cells D5 to D10. For this, select the Cell Range D5:D10. Keep in mind that the data from 1st cell of the selected Cell Range will fill down in entire selection. 2. Select ...
Read more

Excel Shortcuts Ctrl A to Ctrl Z

By
Image
Use of keyboard shortcuts while working in excel will be a great help for us. It will save a lot of time. In this post we are going to see Excel Keyboard Shortcuts for alphabets A to Z while using Ctrl key. Below table will give you a brief idea for each shortcut and its functionality: Now, we will see in detail how each of above listed shortcut combinations will work: Ctrl + A :  It will select all content present on the worksheet. If active cell is in the table then it will select entire table. Ctrl + B :  It will convert selected text into Bold . Ctrl + C : It will copy the selected content. The content which is copied can be seen in the Clipboard. Ctrl + D :  It will fill down the content from upper cell. For example we have inserted a number “1” in the cell A1. If we select the cell range from A1:A5 and press Ctrl + D, then this number “1” will automatically fill down in the entire cell range . ...
Read more

Select all cells highlighted with specific color at a time

By
Image
Many times we apply colors to cells in excel sheet which will help us to identify important cells just by looking at the data. But what if we use multiple colors to highlight data and later on want to select all the cells which are highlighted with specific color only???  YouTube Video: For example, we have listed some values in excel sheet and highlighted few cells with red color and few cells with yellow color. Now, we want to select all cells which are highlighted with only yellow color at a time. But how????  Don't worry... We will provide you a fantastic solution for this!! Follow below steps:  1. Select entire data. 2. Press Ctrl + F. "Find and Replace" window will appear. 3. Click on Options button. 4. "Find and Replace" window will expand with some additional options. Click on Format button. 5. "Find Format" window will appear. Click on Fill tab and select yellow color. You will s...
Read more

How to Remove Duplicate Entries from Multiple Columns in Excel

By
Image
In our last blog post, we have learned to remove duplicates entries in excel . In this blog post, we will go little advanced to remove the duplicate entries from multiple columns. Removing Duplicates Entries from 02 Columns: We will learn to remove duplicate entries from the data as shown in below image. The entries which are duplicate in both the “Fruits” column and “Price/Kg” column are highlighted with different colors. To remove duplicate entries in these 02 columns, follow the steps provided below: 1. Select the data from which we want to remove the duplicates. 2. Go to “Data” tab and under “Data Tools” we can find command named as “Remove Duplicates” as highlighted in below image. 3. Once we click on “Remove Duplicate” command, one dialog box with same name “Remove Duplicate” will appear on our excel screen. 4. If we check the checkbox for “My Data has headers” then the header row from our selection will be excluded automatically and under “Columns” section we will see the name o...
Read more

Excel Pivot Table

By
Image
Pivot Table in excel is a very powerful feature which helps us to to organize and summarize the data in very effective manner, that too without using any complex excel formula or functions. In this blog post we will see how to create Pivot Table in Excel. Below is the dummy sales data for last three years (2018, 2019 & 2020). In this sales data employees A, B, C & D sales various electronic components like Resistor, Capacitor, Inductor, Diodes & LED in all South, East, North & West region of India. Creating Pivot Table: Now we will see how to create Pivot Table in Excel. Pivot Table feature is available in Insert Tab. Pivot Table command is available in "Insert Tab" under "Tables Group" as shown in below image. Once you click on Pivot Table command, “Create Pivot Table” dialog box will appear on screen. This is like as shown in below image. This “Create Pivot Table” dialog box is very important to create Pivot Table. There are two important options a...
Read more

Exploring Font Colors and Fill Colors in Excel

By
Image
Many times we use colors in our excel files to make our work attractive. We can apply colors to the texts as well as to the cells.  If we want to change the color of text available in cells then we use the various color options available from “font color” command. Similarly, if we want to change the color of cells itself then we use the various color options available in “fill color” command. But have you ever wondered that what are names of different colors available which we can apply to texts and cells?? YouTube Video In this post we will see details about this.  The color options which are available in “font color” and “fill color” commands are as below: Fill Color: Font Color: If we compare above two images, we can see these color options are categorized into two categories. 1. Theme Colors 2. Standard Colors Theme Colors: There are total 60 different colors available in (06 rows x 10 columns) in Theme Colors category  (These colors w...
Read more

Insert Multiple Rows in Excel Based On Cell Value

By
Image
In this blog post we will learn to Insert Multiple Rows in excel based on cell value with the help of VBA Code. For this we have prepared dummy data as shown in below image. In this data we have listed few “Products”, their “Quantities” and their “Unit Price”. Now we want to insert multiple rows based on their “Quantities”. For example, “Product 1” has 04 quantities so we have to add 04 rows below the Product 1, “Product 2” has 02 quantities so we have to add 02 rows below the Product 2 and so on. To avoid wasting our time by manually inserting rows one by one, we can use VBA code which will do our work within seconds. To do this, just follow the below steps: 1. Add “Developer Tab” on the Ribbon if it is not already added. If you are not aware about how to add Developer Tab on the Ribbon then click here . Once added on the Ribbon, “Developer Tab” will looks like as highlighted with red in below image. 2. Now, click on the “Visual Basic” command as highlighted in below image. 3. Once ...
Read more

Doughnut Chart in Excel

By
Image
Previously we have learned about creating “ Column Charts in Excel ”, “ Bar Charts in Excel ” and “ Pie Charts in Excel ”. Now, in this blog post we will learn to create “Doughnut Chart in Excel”. We will take very simple example to create “Doughnut Chart in Excel”. We have listed 05 products: Product A, Product B, Product C, Product D and Product E along with their costs. Now we have to convert this data into Doughnut Chart for better data visualization. Just follow below steps: 1. Select the above data which we want to convert into Doughnut Chart. 2. Go to “Insert” Tab, Select Insert Pie or Doughnut Chart command under “Charts” group. 3. We will get further options for selecting Insert Pie or Doughnut Chart . Out of these appeared option we have to select “Doughnut” option as shown in below image. 4. We can see that “Doughnut Chart” is now created for our selected data. Changing Chart Style: To change the formatting of our Doughnut Chart, click on the existing “Doughnut Chart” and t...
Read more

Conditional Formatting:Highlighting Above Average and Below Average Values

By
Image
This blog post is continuation to our series of posts on “Conditional Formatting”. In this blog post, we will learn about highlighting values which are “Above Average” and “Below Average” of the selected values. To learn this post in an easy way, I have randomly listed few numbers as shown in below image. We will learn how to highlight the numbers which are above the average of all the selected numbers and which are below the average of all the selected numbers. For your easy understanding I have already calculated average value of all these numbers numbers (Which is 65). This will help us to easily cross check our output. Highlighting Above Average Values: Select all the values from which we wish to highlight the “Above Average Values”. Go to Conditional Formatting (which is available in Home Tab under Styles group) and select “Above Average” option as shown in below image. “Above Average” dialog box will appear on screen. Select the Formatting style from available list and hit OK but...
Read more

Bold, Italic and Underline Commands from Font Group

By
Image
Bold, Italic and Underline… These three commands are very basic but very important and frequently used in excel. These commands can be found under Font Group. YouTube Video: Let us have detailed description about these commands..!                                              Bold : If we want to change our text into Bold format, then we can use this command. Keyboard shortcut to use this command is Ctrl+B. In below image we have inserted a sample text in cell A1. If we click on the Bold icon from font group or use keyboard shortcut Ctrl+B, then the text from cell A1 will convert into Bold as shown in below image. Italic : If we want to convert our text into Italic format, then we can use this command. Keyboard shortcut to use this command is Ctrl+I. In below image we have inserted a sample text in cell A1. If we click on the Italic icon...
Read more