Excel Tip of the Week: Paste Special

This week’s topic is Paste Special.

I really should have covered this one a long time ago, but better late than never. I’ve actually hinted at this feature a couple times, most notably in the “Linked Picture” post, but also in the “Pasting into PowerPoint and Word” post.

This is one of the most essential features in Excel and allows you to do a number of things with your data. There is actually too much to cover in one post, so today I’ll be showing you 1) Paste Special: Values 2) Paste Special: Formats 3) Paste Special: Formulas.

All Paste Special options can be accessed by clicking on the “Home” menu and then in the “Clipboard” group clicking the “Paste” button (not the big clipboard icon). From there you can hover your mouse over the various icons to see what each one does.

For Mac users it is basically the same. After you click “Paste” you have to then click “Paste Special” and a dialog box will pop up showing you all your options.

1) Paste Special Values – This feature allows you to copy cells which contain formulas and then paste them as their values. This erases the formula and “hard-codes” the values into a cell. The keyboard shortcut is Alt → H → V → V. The icon looks like this:

2) Paste Special Formats – This feature allows you to copy a cell’s formatting to another cell. Things such as font color, borders, background color, and boldness will be copied over to a cell, but will not change the content of the cell. The keyboard shortcut is Alt → H → V → R. The icon looks like this:

3) Paste Special Formulas – This is sort of the opposite of Paste Special Formats, it allows you copy a cell’s formula and then paste it elsewhere without bringing any formatting with it. Thus you are able to copy a formula somewhere without messing up any formatting already in place. The keyboard shortcut is Alt → H → V → F. The icon looks like this:

You can download a spreadsheet here and try out each of these features. As always, I highly encourage you to take advantage of the example spreadsheet since it is the best way to learn Excel. I’m trying something new this time and placing the instructions into the spreadsheet instead of into the post. Let me know if you like this or not.