Excel Series: Paste Special

The Specialist’s tool

If you’ve ever had to work in Excel, you’ve (hopefully) used Copy and Paste. Once something is on your spreadsheet, you never have to add it again. Just use copy and paste to duplicate! But, if you want to up the ante, you have to learn how to use Paste Special too.

/peɪst/

noun

1. a thick, soft, moist substance typically produced by mixing dry ingredients with a liquid

“blend onions, sugar, and oil to a paste”

What’s so special about Paste Special?

A normal copy and paste, will create an exact duplicate of the cell that was copied (source cell). In other words, this means it will take:

• The content
• Including the formulas
• And what it looks like

Into the next cell (destination cell).

On the other hand, if you only wish to take certain elements of the cell across, that is where Paste Special can really work for you. The image of the washi tapes represent this tool – the pieces of tape look similar, but there is an element that makes it different.

The below screenshot indicates all the options you will find:

All

When selecting “All”, this is the equivalent to a normal copy and paste.

Formulas

This allows the user to only copy the formula contained in the source cell. It will consider the use of absolute, mixed and relative references ($-signs) when pasting the information.

This is handy when you have pre-formatted your destination cells and do not want the source cell’s format to duplicate in them.

Values

The values option copies the result of a formula in the source cell and pastes only the answer in the destination cell. Consequently, the underlying formula falls away. This will also keep the format of the destination cell.

This option is useful when the result of the formula should no longer change, or the initial result should be compared to a variation of the formula.

Formats

This will act similarly to the format painter tool where it will copy only the format from the source cell, without overwriting the contents of the destination cell.

This option is very useful when a large range of cells, or cells in different places need to be reformatted.

Comments

This will copy any cell comment from the source cell, and apply it to the destination cell(s). in other words, it will not overwrite the contents of the destination cell, but merely add a cell comment to it.

Validation

The previous blog post on drop-downs alluded to the use of pasting to copy data validation onto another cell. Now, here you see where to find it!

Other options

On the right-hand side of the dialogue, there are a number of variations relating to the formatting of a specific cell. So, pick and choose the options you require.

Operations

This is a very powerful function. It allows the user to copy the source cell, and then either Add, Subtract, Multiply, or Divide it to the destination cell.

Using these operations in conjunction with the Paste section (i.e. paste as value / formula), will go a long way towards improving your efficiencies in Excel.

Transpose

The last nifty option allows the user to change the direction of the text – i.e. change data that is currently in a row to move into a column.

This is a great tool to be aware of when manipulating data. However, remember that your destination cells cannot fall within the same range as your source cells.

So how do I access it?

Before you try to access it, note that you must have copied a cell into the clipboard for it to become available. There are many ways to do the same thing in Excel, and using paste is no different. A range of images have therefore been included below to compare the techniques.

The options include using:

• Your mouse (right click)
• The Home ribbon
• A Ctrl shortcut
• An Alt shortcut

You may either select one of the pictures that represent the formulas / values / formats options, or you can access it via the dialogue as shown in the screenshot above.

In conclusion

If you would like to learn more about Paste Special with some practical examples, join us at our next Excel Intermediate course. Alternatively, contact us to book a corporate package for your team today.

Lastly, don’t forget to share this article with your colleagues and drop us a line in the comment section below.