How to Use Paste Special Command in Excel (5 Suitable Ways)

We are all familiar with Cut/Copy and Paste commands. They’ve been around since the early days of word processing, and they are universal across almost every Windows application. By pressing “Ctrl-V” or clicking “Paste”, we can insert the contents of the clipboard. This is how the basic paste command works. Besides this basic paste approach, Excel also provides a variety of special methods to paste the clipboard. Today, I’d like to give an introduction on the Paste Special command in Excel and how can it be used in real life.


Download Practice Workbook

Download the working files from the link below.


Introduction to Paste Special Command in Excel

Paste Special is a feature that gives you more control over deciding how the content is displayed. For example, text copied from a web page often retains much of or all of the HTML formatting. By using Paste Special, you can choose the paste unformatted text. With Paste Special, you can copy not only data but also the source column’s width into the target columns. You can even use Paste Special to perform calculations like addition, subtraction, multiplication, and division. They are still other powerful ways to use Excel’s Paste Special Command.

5 Useful Ways to Use Paste Special Command in Excel

Here I am considering a dataset of Student Information. The dataset has 5 columns and 6 rows ranging from B4 to F9 indicating Student Name. Test 1, Test 2, Total, and Remarks respectively.

Dataset to paste special commands in excel


1. Copy Source Column’s Width to Apply Paste Special Commands in Excel

When you copy data into a new column, the target column’s width doesn’t automatically adjust to accommodate the new values. With Paste Special, you can copy the source column’s (Column B) width to the target column (Column C) by doing as follows.

Steps:

  • Select source data (Cell F5 to F9 in Dataset sheet ) and press Ctrl + C to copy the selected values to the clipboard.
  • Select target cell C5.
  • Right-click and then move the cursor over Paste Special, the paste drop-down will appear. At this time, click the Column Widths (in red square).

Copy source column width to paste special commands in excel


2. Copy Source Cell’s Formula to Insert Paste Special Commands in Excel

The range C5:D9 contains data on which I want to perform addition for each row of this range and data in E5:E9 will contain corresponding results of these calculations. The formula in cell E5 is

=C5+D5

In order to sum C6 and D6, we all know that the formula should be

=C6+D6

Here I am going to describe the procedure.

Steps: 

  • Select the E6 cell.
  • Then Goto the Home tab.
  • After that, Select the Paste option.
  • Hence, Select the Formulas(F).

Read More: How to Copy Multiple Cells to Another Sheet in Excel (9 Methods)


3. Paste Special Command in Excel to Transpose & Reconstruct Data

Suppose that we have data about 5 students in range from B4 to F9 and we want to transpose the data so that I can data displayed as what shows in range B8:F11. This can be done by following the below steps.

Steps:

  • Select range B4 to F9 and then click Ctrl + C to copy data into the clipboard.
  • Click cell B11 and then Right-click. Click Transpose in the paste drop-down.Transpose to paste special commands in excel
  • You will find the result just like the picture given below.

Transpose to paste special commands in excel

Read More: Excel VBA to Copy Data from Another Workbook without Opening


Similar Readings


4. Copy Results to Different Parts of Worksheet to Use Paste Special Commands in Excel

Here I am going to show how to copy results to different parts of worksheet by Paste Special Command in Excel. I will show the procedure step by step.

Steps: 

  • First, Copy the data from the dataset ranging from E5 to E9.
  • Then open another sheet called Copying Result only.
  • After that, Select cell C5 and Right-click on it.
  • Select the Paste Specials and Select the Paste Values option. Click on the first option shown below.

Read More: Macro to Copy and Paste from One Worksheet to Another (15 Methods)


5. Perform Calculation with Paste Special Command in Excel

In this portion of this article, I am going to show the average of all students in our dataset by performing calculation with Paste Special Command in Excel. now I am going to describe the process step by step with necessary illustrations.

Steps: 

  • First, Copy and Paste the total numbers in the Average column just like the picture given below.
  • Select cell E2 and Click Ctrl + C.

The lower left part of Figure 5 shows the results and you can see that we indeed performed division on original data shows in the upper left part of Figure 5. You can also perform other calculations such as addition, subtraction, and multiplication.

Performing Calculation to paste special commands in excel

  • Select range D5:D9, right-click, and then open Paste Special dialog box.

  • Choose Divide and click OK.

Performing Calculation to paste special commands in excel

  • You will find the result just like the picture shown below.


Things to Remember

  • To use Paste Special Commands, don’t use the Ctrl+V shortcut key to paste.

Conclusion

I have tried to describe the how to use Paste Special Command in Excel in this article with 5 suitable ways. I hope this will increase your Excel skill. If you have any kind of query, feel free to ask me in the comment section.

Related Articles

Zhiping Yan

Zhiping Yan

I am from China and this photo was taken in a classical garden. There are many similar gardens in China, attracting a lot of visitors every year, especially in spring and summer. I was major in Biotechnology. But I took a job as a SAS programmer because I prefer programming. Besides SAS, I also learned Excel VBA in my spare time. It is fantastic to be able to manipulate data, files and even to interact with the internet via programming. This will save me a lot of time. I am keen to learn new things.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo