What is and how to use paste special command in Excel

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.

Introduction

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).
    Paste Special Command in Excel Image 1

    Figure 1

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.

Paste Special Command in Excel Image 2

Figure 2

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

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.
    Paste Special Command in Excel Image 3

    Figure 3

     

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


Similar Readings


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.

Paste Special Command in Excel Image 4

Figure 4

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

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.

Paste Special Command in Excel Image 5

Figure 5 [click on the image to get an enlarged view]

Download working file

Download the working files from the link below.


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

ExcelDemy
Logo