Paste Special

Every data in any cell of your spreadsheet is composed of a few elements. These elements can be a value, a formula, a format, a note, and many others.  When you just copy one cell and paste it elsewhere using the standard CTRL C / CTRL V command you are basically copying ALL the elements of the original cell.

For example, cell B4 in the extract below has a specific value, format and a note related to “Production Delays”. If we copy that cell and paste it in the cell B9 for example, all the elements (including the note) will be copied and pasted as per image below:

Paste Special – Single Elements

In case you need only a single element of a cell, you can then use the “Paste Special” tool. So press CTRL Z to undo the previous action and let`s check how it works.

Pasting only Comments & Notes:

Let`s imagine we know in advance products F and G will suffer production delays. We can then:

1. Select cell B4 again.

2. Press CTRL C to copy all the elements of that cell.

3. Select the cells F and G (by holding the SHIFT key).

4. At the top left of your screen, expand the menu “Paste”. There you can then select which element you want to paste. To have all these options displayed in a single menu, click in the field “Paste Special” which is also accessible through the shortcut CTRL + ALT + V.

In the first section of this menu you can then define which specific element you are after. You can also perform mathematical operations with the values you copied, and you can also use some special features related to Skip Blanks, Transpose and Past Link as we will cover in the part II of this tutorial. To complete our specific example, we can then select the element “Comments and Notes” and then click on OK. Now products F and G have the same note as Product B, but their specific format remains the same.

Pasting only Formats:

We can also see in this table that some cells in this column C have a grey background colour and we should instead have the standard yellow colour displayed. So, follow the steps below:

1. Copy cell C4 (as a reference).

2. Select the range C5 to C12 and go back to the Paste Special menu.

3. There, select “Formats” and press ENTER. The same format is then applied to all cells you selected.

Using Paste Special for Values:

If we now press F2 over cell D6 we can see it`s linked to the cell D5 so they have the same result which is a price of $ 78 / unit. You can also see this formula in the “Formula Bar”. However, here we should have a value instead of a formula. To do that, we can then:

1. Select cell D6.

2. Press CTRL C to copy all elements of that cell.

3. Press CTRL ALT V to access the “Paste Special” menu and there select the option “Values” and then press ENTER. Now this cell became just an ordinary value, and you can confirm that in the formula bar which is now just showing the value 78. This cell also needs to have the same format as the cell above (D5), so copy that cell, select cell D6 again, press CTRL ALT V, select “Formats” this time and press ENTER.

Pasting only Formulas:

At the corner of this table we also have the function SUM () which sums up all the values related to revenues in column E. We need to insert a similar function in cell C14 (related to Volume Sold) but here you just need the formula itself and not the format. So, all you need to do is to copy that cell, then select cell C14, open the Paste Special Menu and then only choose to paste the element “Formulas” and press ENTER. This correct result will then be displayed in that cell.

Paste Special – Math Operations:

The Paste Special command is also useful to manage mathematical operations in Excel without the need to build any formulas in your spreadsheet. In this example, we have the reference number 10 and the base number 100 copied 4 times in the table below. The idea here is to perform the operations of Addition, Subtraction, Multiplication and Division using these 2 numbers.

Paste Special for Addition:

So, if you need to add the number 10 to the first number 100, you should then:

1. Copy all the elements of cell K8 by pressing CTRL C.

2. Select cell J3

3. Press CTRL AL T V to open the Paste Special menu and select the option “Values”.

4. Note that you if you press ENTER you would be basically replacing the number 100 by the number 10 which is not what you need. In this case the value 10 should be added to the number 100, so in the section “Operation” you should click on “Add” and then press ENTER. The result 110 is then returned in this cell.

Subtraction:

Let`s now subtract 10 from 100. Follow the simple steps below:

1. Copy cell K8 again.

2. Select cell J4.

3. Press CTRL ALT V, choose “Values” and for the field Operation choose “Subtract” this time and then press ENTER. Here you will get he result 90 in that cell.

Multiplication:

If you need to multiply the number 100 by the number 10 just use the same procedure. You will then get the result 1,000 in cell J5.

Division:

Repeat the exact same procedure in cell J6 to “divide” the number 100 by the number 10. Here you will get the result 10 in that cell.