It’s amazing what a simple command of Copy and Paste can achieve when it comes to Excel. Dig beneath the surface and there is so much more than you would think.  Most people do the CTRL+C – CTRL+V, thing most days. But, did you know there are like over 15 variation to the copy and paste function..

You can copy and paste data itself, OR copy and paste data whilst manipulating it at the same time!  These little gems are accessed in the Paste Special Option in Excel access via the Home Ribbon and the Clipboard Tab In Excel 2007.

It really is a super Excel function, so sit back and get on the copy and paste train with me…..Let’s go over the basics first…

1. Copy And Paste ALL

This copies and pastes everything you selected.  You can easily use the CTR+C and CTRL+V to acheive this as well and I find it much faster.

2. Paste Special Formulas

This is where only the formulas of the selected cells are copied and pasted, any rules on relative and absolute references will still apply.  So if you copied the formula below from*** to** the absolute cell referencing still applies to it.

3. Paste Special Values

Only the value of the original cell is copied and pasted, for example if the original cell had a formula of =9+10, the copied cell will contain the result of the formula, 19 rather than the formula.  Any further changes that are made to the original cells will not be transferred to the copied and pasted cell.

4. Paste Comments

Only comments from the original cell will be copied and pasted.  No other cells contents.

5. Paste Validation

Only the validation rules are copied from cells and pasted.  These data validation rules will restrict the way that data can be entered into cells for example restricting cells to text rather than numbers.  Useful if you are creating a form and want to  replicate some cell validations to another area.

6. Paste All Except Borders

Everything will be copied and pasted form the original cell except for any border formatting.

7. Paste Column Widths

The width of the columns will be copied and pasted form your original cells.

8.Paste Formulas And Number Formats

Here only the formula and formatting of the original cell is copied and pasted.  This is great if you are copying some formulas from  one set of cells to another. the formats and the actual formulas are copied.

9. Paste Operation

This paste special will specify which mathematical operations, if any, you to copy to apply to the copied data.

10. Skip Blanks

This paste special avoids replacing values in your paste area when blank cell occur in the copy area  i.e. means that blank cells when pasted will not wipeout exsisting data.

11. Paste Special Transpose

This changes the columns of copied data to rows and vice versa.  So if the copied information is contained in rows across the spreadsheet then the pasted information will appear in two columns down the sheet.  Read HERE for more information on transposing data.

12. Paste Link

This is  a great option that creates a link between the cells- formula or data in the original to the destination cell.

Let’s now get onto the supercharged options in this great command.

You can actually manipulate your data whilst copying it.

These options are in the ‘Operation’ Section of Paste Special.

13. Paste Special Add

With this little gem you can select a rectangular range of cells hit Edit- Copy (to the clipboard), the use Edit- Paste Special – Add to add these values to another range of cells. Quick Example? Why not?

I have a set of prices for products in A2 to D2.  I want to add to all figures a standard increase of 2.50

In a blank cell, enter the amount of the increase. In this example, 2.50 was entered in cell F2

1. Copy the cell which contains the increase amount.
2. Select the cells which contain the amounts that you want to increase. Here, cells A2:D2 are selected.
3. On the menu bar, click Edit | Paste Special
4. Click Values, and click Add, then click OK.
5. Each of the selected numbers is automatically increased by 2.50

14. Paste Special Subtract

This is the same principle as the above but we can subtract easy.

Try the same example but instead of hitting Edit- Values- Add, use Edit- Values- Subtract.

15. Paste Special Multiply

This is the same principle as the above but we can multiply easy.

Try the same example but instead of hitting Edit- Values- Add, use Edit- Values- Multiply.

16.Paste Special Divide

This is the same principle as the above but we can divide easy.

Try the same example but instead of hitting Edit- Values- Add, use Edit- Values- Divide.

Paste Web Pages Into Word?

I bet you do this a lot, paste Web Pages into Word?, Yes?, me too!.sometimes there are problems with it thought right? Sometimes web pages are light font or text on a dark background.  If you copy this into Word then you wont see much as it is pasted as white text. We can see how the Paste Special Box again can help us here.

By default Word want to copy Webpages in HTML.  This will include the white or light text formatting.  If you select

1. Paste Special
2. Unformatted text- just the text is copied, into whatever you default font in Word is.  nice.

If you copy pictures from a Webpage ( we all do it!) or indeed from an email then we just right click on the picture and hit copy.  You may have noticed in doing this that graphics can come in many formats, GIF, PNG.BMP etc .

You might want to have a smaller image than the original or larger one downloaded to your computer.  BMP are notorious for being MASSIVE files but you do not need any fancy converter program to make these files more manageable. All you need to do is use the Paste Special.

Try pasting the graphics files in the different formats and see the difference it makes to the size of your files.

So what have we learnt here?.   The Paste Special is a massively powerful command, with many uses.  Why not let me know what your favorite pasting function is.