Search Not Just Numbers

Tuesday, 24 June 2014

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!

Excel Expert Course

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".


  1. Taking pictures has grown to be very affordable in addition to reachable intended for louis vuitton replica. Having completely new unwanted cameras the online world in addition to low cost photography finalizing application all people are now able to have the funds for to build pics practice in addition to post these individuals. Taking pictures has had a fun filled flip within the last a long time. While using the proliferation connected with low cost although quality unwanted cameras along side while using the accessibility to excessive bandwidth Internet access in addition to cost-free or maybe low cost strong photography finalizing application all people can pay for to build in addition to post some sort of pretty much endless volume of a digital pics. Unwanted cameras are classified as the primary and the majority significant ingredient letting all people as a superior shooter. Unwanted cameras offer a several strengths above the older picture structured surveillance cameras of which make it possible for all people to employ in addition to gain good taking pictures ability. Unwanted cameras encourage the speedy observing connected with pics considered within the dslr camera modest LCD tv screen. That in return for makes for strengthening this photograph formula in addition to doing corrections then and there an issue that seemed to be unattainable previous to. Unwanted cameras likewise make it possible for acquiring pics cost-free by any means. Compared to picture structured surveillance cameras of which expected this high-priced progress on the issues unwanted cameras build pics such as computer system records. The one price tag connected with a digital pics would be the storage devices meant for these individuals although having recent marketing charges of which selling price is exceedingly all around fake louis vuitton bags. This mix off the ability to promptly examine ones pics in order to likewise carry several pics since you including cost-free delivers all people web site process approximately desired as a way to strengthen the taking pictures ability.

  2. It has been from a very long time that we are serving as a leading suppliers of the designer inspired Hermes Designer Replica Handbagsand our product which includes handbags, shoes, wallets, leather goods andReplica Rolex are spread all over. his is the perfect place to buy Replica Handbags inspired by different designers. We have Replica of handbags by Chanel, Dior, LV, Mulberry, Miu Miu, Prada and many other design houses.Tiffany Inspired Jewelry, Replica Jewelry - Checkout our great collection of tiffany inspired jewelry, Discount Replica Designer Handbagsjewelry, louis vuitton replica, LV handbags, fashion handbags and many more. Buy online. Enjoy ShoppingReplica Handbags UK!.

  3. Then there's the scene in "Revenging hermes replica Angel" in which Crichton is once again dead, and the clone of Scorpius in his head is trying to help him fight his way back to life. Talking to Scorpie, Crichton makes gucci outlet a reference to another famous prada replica TV sci-fi hero: "Kirk was a fiction. I'm real," he says.