OzGrid

Lesson 8 - Excel Paste Special

< Back to Search results

 Category: [Excel,Excel_Charts]  Demo Available 

EXCEL PASTE SPECIAL

 

Lesson 8 - Pasting Using Paste Special

PASTE SPECIAL

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, Nonewould 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 option, 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:

Lesson 1 - Excel Fundamentals
Lesson 2 - Starting Excel and Excel Workbooks
Lesson 3 - Excel Toolbars and Task Panes
Lesson 4 - Excel Worksheets
Lesson 5 - Excel Cells and Navigating a Worksheet
Lesson 6 - Excel Cut/Copying and Pasting Data
Lesson 7 - Excel Copying with the Fill Handle

 

See also:

Lesson 9 - Excel Insert Command
Lesson 10 - Excel's default options
Lesson 11 - Excel's Undo and Redo
Lesson 12 - Excel's Format Painter
Lesson 13 - Excel's Dates and Times
Lesson 14 - Excel's Custom Formats
Lesson 15 - Excel Formulas
Lesson 16 - Excel Cell References
Lesson 17 - Excel: Avoid Typing
Lesson 18 - Excel Formulae Arguments & Syntax
Lesson 19 - Excel Autosum Formula
Lesson 20 - Excel Auto Calculate
Lesson 21 - Excel's Insert Function
Lesson 22 - Excel's Useful Functions
Lesson 23 - Excel's Named Ranges
Lesson 24 - Excel's Constants and the Paste Name Dialog
Lesson 25 - Excel's Calculations
Lesson 26 - Excel Comments Cell
Lesson 27 - Excel Find and Replace
Lesson - 28 - Clear Excel Cell Contents
Lesson 29 - Effective Excel Printing 1
Lesson 30 - Effective Excel Printing 2
Lesson 31 - Sorting in Excel
Lesson 32 - Hide/Show Row/Columns in Excel
Lesson 33 - Auto-Formats in Excel
Lesson 34 - Creating a Basic Excel Spreadsheet
Lesson 35 - Excel Charting Lesson: The Basic Excel Spreadsheet
Lesson 36 - Excel Worksheet Protection
Lesson 37 - Excel IF Formula Nesting
Lesson 38 - Excel Function Now/Today Formulas

 

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.


Gallery



stars (0 Reviews)