• Buffer
  • Sharebar
  • Buffer
jethro's picture

Pasting in Office 2013

This also applies to Office 2007 and 2010. Based on my original post Feb 2004 relating to Office 2003 and prior.

Most people know how to use copy and paste in Office. Or do they? Right click a selected item(s) and copy, then right click the destination and paste.

That is definitely the slow way. Keyboard people know about Ctrl+C and Ctrl+V for Copy and Paste. (or CTRL+Insert / Shift +Insert)

imageimageBut office has long had a Paste Special command that exposes a whole bunch more options for the pasting side of this command.

New Office, (2007, 2010, 2013) uses the Paste button in the ribbon to provide access (though there is still keyboard access with ALT+E+S).

Once you have something in the clipboard with the copy command, clicking the little arrow below Paste Icon in the ribbon gives you a lot more options. Each office application is slightly different  as to what you get.

Word has less options than Excel. Powerpoint and Outlook, Live Writer and Publisher etc. all use this feature differently. However each of them allow you to strip metadata (formatting etc.) from the actual text and just paste the text. This extremely useful when copying text from a web page, PDF file or some other heavily formatted document.

imageHovering your mouse over any of the icons will give you a tool tip identifying it as per the example on the right.

And clicking the Paste Special link at the bottom brings up the traditional dialog box.

Ill take you through the main ones for Excel.

  • Paste Special Formulas Use this when you want to copy a formula but don't want to change the editing on the target cell(s)
  • Paste Special Values Use this when you want to convert a selection (or single cell) from formulas or links to just the current calculated value. Full resolution of formulas to maximum decimal places will occur even if formatting doesn't show it. This is useful to cut links from external files, replace temporary formulas with actual results etc.
  • Paste Special Links Use this to quickly paste the link to an external spreadsheet by copying from that sheet and paste links into the target cell(s)
  • Paste Operation - Multiply, Add, Divide, Subtract These are very powerful tools. Try this:
    Find a selection of formulas (eg sums at the bottom of a range). Enter 0 (zero) in a blank cell then copy that cell.
    Select the range you want to alter and Paste Special Operation Multiply. (You may want to click Formulas as well so as to not change target cell(s) formatting).
    This will add to your existing formula *0 (and any required brackets) and the result will become zero. This can be used in all sorts of ways, - eg dividing numbers by 1,000 to change $ to $'000 etc.
  • Paste Special Transpose Use this to alter the orientation of a selection of cells. Copy a column and turn it into a row and vice versa.

You can also combine options from each section as per the example below.


jethro's picture

Photography stuff - tools and reviews

Some of the cool things I have found lately that are photography related.

Tool Trader II by Meanest Indian


Microsoft Producer for PowerPoint provides users with many powerful new features that make it easier to synchronize audio, video, slides, and images to create engaging and effective rich-media presentations. Producer gives content and media professionals, as well as everyday PowerPoint users a host of new content authoring features.

  • Compfight – a flickr searching tool – excellent for finding photos for use in blog posts. Its where I found the picture in this article of the tools.

Local Camera Groups and Clubs (for me)


jethro's picture

Office 2007 and 2010 Sundry Tips and Hints

There is just so much going on with the whole change over from Office 2007 to 2010, and for those of you still stuck in 2003, even more important for you to start convincing your organisations of the need to go straight to 2010.

We have been running the beta 2010 here solidly an all our main machines here for 2 months now, with some individual test machines previous to that. Unfortunately now we have to have test and development environments in both 2003 and 2007 so that has doubled our required number of Virtual Machines.

Following are some of the latest news and bits and pieces as well as some other MS Office related information I have come across recently.

Excel 2007 and 2010

Excel Conditional formatting. Conditional formatting in 2007 is one of the main reasons people come to Spyjournal.biz. These two articles are the single most hit articles of the thousands here with over 50,000 views between them:

Amit Velingkar has written conditional formatting rules simplified while Dick Kusleika has written some code to list out all the conditional formatting rules in a worksheet

jethro's picture

Weekend Web Roundup

There’s been a lot of things happening this week. Not the least being the possibility of a swine flu pandemic. Read on for a scientific explanation of swine flu.

IMGP7128-800 General Microsoft News

I’m pleased to share that the RC is on track for April 30th for  download by MSDN and TechNet subscribers. Broader, public availability will begin on May 5th. Link for rest of article.