Zhiping Yan
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.
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).
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
In order to sum C6 and D6, we all know that the formula should be
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.
- You will find the result just like the picture given below.
Read More: Excel VBA to Copy Data from Another Workbook without Opening
Similar Readings
- How to Copy Excluding Hidden Rows in Excel (4 Easy Methods)
- [Fixed]: Right Click Copy and Paste Not Working in Excel (11 Solutions)
- Difference Between Paste and Paste Special in Excel
- How to Copy Alternate Rows in Excel (4 Ways)
- Excel VBA to Copy Only Values to Destination (Macro, UDF, and UserForm)
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.
- Select range D5:D9, right-click, and then open Paste Special dialog box.
- Choose Divide and 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.
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.
