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 and how can it be used in real life.
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.
Case 1 Copy source column’s width into target column
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 (Cell B2) and press Ctrl + C to copy the selected values to the clipboard.
- Select target cell.
- 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).
Case 2 Copy source cell’s formula to different cells
The range B3:C6 contains data on which I want to perform addition for each row of this range and data in D3:D6 will contain corresponding results of these calculations. The formula in cell D3 is “=B3+C3”. In order to sum B4 and C4, we all know that the formula should be “=B3+C4”. The formula is different from rows to rows. Obviously, it is time-consuming work if there are a lot of rows in the data range. Is there an easy way to automatically write the right formula for each row? This can also be done with Paste Special. Here shows another place where you can find paste drop-down. On the Home tab, Click Paste Option, the paste drop-down will appear as bellows. By clicking on Formulas (F), the formula instead of values will be copied into the target cell. In cell D4, you can see that the formula is “=B4+C4” and that’s exactly what we want.
Case 3 Transpose to reconstruct data
Suppose that we have data about 4 students in range B2:E6 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 below steps:
- Select range B2:C6 and then click Ctrl + C to copy data into the clipboard.
- Click cell B8 and then right-click. Click Transpose (in the blue square of Figure 1) in paste drop-down.
- 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)
Case 4 Copy/Transpose results of calculations to different parts of worksheet
Let’s continue to use the above example on illustrating how to copy the results of calculations to different parts of a worksheet. By clicking Values (V) (in the grey square of Figure 1) in the paste drop-down, we can paste only results instead of formulas. You can also find this option in Figure 2. The third way to complete this task is to use the Paste Special dialog box which can be prompted by clicking Paste Special (in the light green square of Figure 1 or Figure 2) in the Paste drop-down. In the Paste Special dialog box, choose Values and then click OK. Look at Figure 4, values in the range F3:F6 were exactly copied from range D3:d6. And if you tick Transpose before clicking OK, the values will also be transposed, just range C8:F8 shows.
Case 5 Perform calculation with Paste Special command
Suppose that there are a lot of numbers and we need to divide them by 5, how can we do it in an easy way? The upper left part of Figure 5 shows data on which we want to perform division. We also placed 5 in cell D2. The following steps will show you how to perform the calculation with Paste Special command.
- Select cell D2 and click Ctrl + C.
- Select range B3:B8, right-click, and then open Paste Special dialog box.
- Choose Divide and click OK.
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.
Download working file
Download the working files from the link below.