Lesson 8 - Excel Paste Special

Category: [Excel,Excel_Charts] Demo Available

# EXCEL PASTE SPECIAL

## Lesson 8 - Pasting Using Paste Special

### PASTE SPECIAL

###

### Gallery

By default, when you Copy and Paste the content of any cell(s), Excel will **Paste** all cell formatting. We can, however, use what is called **Paste Special **to nominate the attributes of the copied data we wish to Paste.

To do this, again **Copy** the cell(s) in any of the above methods (except Drag and Drop). Now select your destination cell and go to **Home>Clipboard>Paste>Paste Special** or right click and select **Paste Special** from the Shortcut menu. This will display the **Paste Special** dialogue box. In this dialog box under the heading **Paste**, there are different options that can be applied, the default is **All**, which is exactly the same as using any of the **Paste** methods described above.

The other options are:

**Formulas**

This option would apply only if the cell we copied contained a formula. What this means is instead of the formatting and other attributes of the cell being Pasted, only the formula itself will be Pasted. To put this into some sort of context, imagine the cell containing any formula to be copied where the background color of the cell is bright yellow, using **Formulas** would not Paste the background color of the cell, just the formula.

**Values**

Again, imagine a cell being copied that contained a formula where the result of that formula was the number 20 (or any other number). Choosing the **Values** option, would mean that we would only be **Pasting** the result of the formula into the destination cell and not the formula itself.

**Formats**

Using this option means you will not be **Pasting** the contents of the cell, but only the formatting. Again, imagine a cell with a bright yellow background containing the number 100 (or any other data). On selecting this option, the destination cell would end up having a bright yellow background, but not the number 100.

**Comments**

This option applies to **Cell Comments** which will be covered in a later lesson.

**Validation**

This option applies to **Cell Validation** which will be covered in the Level 2 course. All except borders

Means all the cell contents and formatting excluding borders would be **Pasted**. We will be looking at borders later.

**Column Widths**

Means no content or formatting will be **Pasted**, except for the width of the column that the data was copied from. Formulas and Number Formats

Using this option will **Paste** only formulas and all number formatting options. Number formatting will be covered later.

**Values and Number Formats**

This will **Paste** only values and all number formatting attributes.

The next part of the dialog box has a heading **Operation**. In order to demonstrate the options under this heading, type the number **2** into cell **A1** and the number **10** in cell **A2**. Copy cell **A1**, then select cell **A2** and again right click and select **Paste Special**.

Under the heading **Operation** there are five options to choose from:

**None**

This is exactly as the name implies and means **None** of the options under the heading operation will be applied. Even if this option and all the other options under Operation are not checked, which is the default, **None**would still apply.

**Add**

Select the option **Add** and click **OK**. You will notice that Excel adds the copied number (in this case 2) to the value of the destination cell (which in this case is 10) to end up with a total of 12. In other words it adds a copied number to the destination cell.

**Subtract**

Again, to see this repeat the coping of cell **A1** and again select cell **A2** and right click and select **Paste** **Special**. This time, select **Subtract** and cell **A2** will be have the number 2 subtracted from its value.

**Multiply**

This works in exactly the same way as **Add** and **Subtract**, except obviously it **Multiplies** the destination cell by the value of the copied data.

**Divide**

This works in exactly the same way as **Add, Subtract** and **Multiply**, except obviously it **Divides** the destination cell by the value of the copied data.

The other two options work in the following way:

Type any number into cell **A1**, any number in cell **A2**, leave cell A3 blank and again type any numbers in cells **A4** and **A5**. Now in cell **B1** to **B5**, type any numbers so that all cells are filled. Now select cell **A1** holding down your left mouse button, drag down until cells **A1** to **A5** are highlighted. Right click anywhere within **A1:A5** and select **Copy**, select cells B1 to B5 in the same way, right click and select **Paste Special**. Select the bottom option **Skip Blanks** and click **OK**. What you will notice, is that Excel did not **Paste** the empty cell of **A3** over the top of the value in **B3**. In other words, as the name implies it **skipped the blanks**.

While you still have five values in cells **B1:B5**, highlight these cells, right click and select **Copy**, then select cell **D1** choose **Paste Special** and select the option **Transpose**. Click **OK**. You will notice that Excel will have Pasted your rows of values into columns. In other words, instead of cells **D1 to D5** having the values Pasted into them, you should have **D1 to H1**. **Paste Link**

The very last option is the **Paste Link** button located at the bottom right of the **Paste** **Special** dialogue box. To see this work, type any value in cell **A1**, Copy this cell then select any blank cell, right click, choose **Paste Special **and click **Paste Link**. You should notice that your destination cell will be showing the value, the same as your copied cell. If you look in your **Formula Bar** (located under your toolbars) you will see Excel has placed what is known as an **Absolute Cell Reference Formula**, ie; =$A$1. It is the dollar symbols that have made the reference absolute. We will be discussing **Absolute** and **Relative** references in detail in a later lesson.

The last method of **Copying** and **Pasting** data is to select the cell you wish to **Copy**, right click on any cell border and holding down the right mouse button drag to any destination cell, then release the mouse button. Excel will display another Shortcut menu, giving you various options. Some of these options are the same as in the **Paste Special** dialogue box so we will only look at the last four options of this Shortcut menu, the **Shift Down and Copy** and the **Shift Right and Copy.**

Again, type any value in cell **A1**, then type any value in cell **D1**. Select cell **A1** and right click on any cell border with the right white arrow showing. Holding down your right mouse button, drag **A1** to cell **D1**, then release the mouse button and select **Shift Down and Copy**. You will notice that Excel shifts down the value of cell **D1** and places it into cell **D2** before it Pastes in the data from cell **A1**. Delete the contents cell **D2**.

Again, select cell **A1**, right click on any cell border, select cell **D1** again and release the right mouse button. This time select **Shift Right and Copy**. This has now done the same as the **Shift Down and Copy o**ption, except as the name implies, it has shifted the data in cell **D1** to the right before pasting in the contents of cell **A1**.

Go back to:

See also:

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets

Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft.

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.