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

Get FREE Advanced Excel Exercises with Solutions!

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 to the Paste Special command in Excel and how you can use it in real life.

Table of Contents Expand

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 various paste options in Excel for 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. There are still other powerful ways to use Excel’s Paste Special command.

How to Use Paste Special Command in Excel: 5 Useful Methods

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.

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.

Select source data range F5:F9 >> press Ctrl + C to copy the selected values to the clipboard.
Select target cell C5 >> Right-click >> move the cursor over Paste Special >> click the Column Widths (in red square) in the Paste drop-down.

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.

Select the E6 cell >> Go to the Home tab >> Select the Paste option >> Select the Formulas(F).

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

Suppose that we have data about 5 students in range B4: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.

Select range B4 to F9 >> Click Ctrl + C to copy data into the clipboard.
Click cell B11 >> Go to Home >> Paste >> Click Transpose in the Paste drop-down.

You will find the result just like the picture given below.

4. Copy Results to Different Parts of Worksheet in Excel

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

Copy the data from range E5:E9 >> Open another sheet called Copying Result only >> Select cell C5 and Right-click on it.
Select the Paste Specials >> Select the Paste Values option >> Click on the first option shown below.

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 calculations with the Paste Special command in Excel. now I am going to describe the process step by step with the necessary illustrations.

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

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

Select range D5:D9 >> right-click to open the Paste Special dialog box.

Choose Divide >> click OK.

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.

Download Practice Workbook

Download the working files from the link below.

Special Pasting Commands.xlsx

Conclusion

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

Paste Special Command in Excel: Knowledge Hub

<< Go Back to Paste | Copy Paste in Excel | Learn Excel