Excel Tip: Copy and Paste - avoiding unwanted baggage

First of all, it was great to see those of you that made it to last week's launch event for the ICAEW's Twenty Principles for Good Spreadsheet Practice. I hope to have a video of some of the highlights of the event to show you soon.

This week, I want to highlight an approach to copying and pasting that means you only bring across what you want, and don't bring across any nasty surprises for later.

Have you ever opened a spreadsheet and been asked if you want to update links that you didn't think were there?

...or ended up with a selection of different font sizes and formats in a range of data?

These are both signs of unwanted baggage dragged across during a copy and paste exercise, but can be easily avoided.

If you simply copy and paste, this brings across all formats and formulae from the source cells along with the contents, causing the two problems highlighted above.

Where the source cells have formulae that make reference to cells on other tabs of the source workbook, the pasted formulae will include external links back to the source spreadsheet. This causes the prompts about external links mentioned above. And, unless this is what was intended, a far greater problem is that the content of these cells will now update with changes to the source spreadsheet!

The multiple formats in a spreadsheet tend to come from pasting data from various sources with different formats.

Both can be easily avoided by some of the Paste Special options.

You can choose to bring over just the contents of the cells (no formats or formulae) by selecting to Paste Values , indicated by the clipboard showing the numbers 123. Note that the results of formulae are copied across as if they had been entered in the cell.

If you do want the formulae to be copied across, but not the formats, you can select Paste Formulas (the clipboard with fx on it).

You can also copy just the formats (leaving existing cell contents intact) by selecting Paste Formats (the clipboard with  a percentage sign and a paintbrush).

Finally, if you want to copy the cell contents and formats (but leave the formulae behind), you can select Paste Values and then Paste Formats.

There, you can now travel light when you're copying and pasting!



If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".