Spy Journal 3.0 Filtering the web for you

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)

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

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

I have been using the HTC Mozart for a few months now. Fair to say its the best phone I have ever used in a number of ways.

Best hardware I have every used. Simple easy to use external hard and soft buttons, ordinary 6.5mm stereo headphone jack, micro USB charger - uses both a power adaptor (supplied) or via a USB port.

Best Phone Software - EVER. This kills the previous mobile 6.1, 6.5 interfaces, and the previous HTC TouchFlo software. It is smooth, sexy, responsive and packed with features. The only draw back is its missing copy and paste.

Best ever applications. While there are plenty of stupid ones (I love the Chuck Norris Facts App) there are very good useful apps, including YouTube, Flickr Manager, Facebook, etc.

The built in maps software is fast with amazing fine grained detail.

I have setup access to two separate exchange server email accounts and a POP3 account. The calendar integrates with them and allows separate colours to differentiate.

The following pages were useful in setting up accounts:

Set up or delete an email account

Setting up your Windows Live ID and email accounts

I have had a lot of articles, news and other tech information filling up my browser and its time to de-clutter before Christmas.

Microsoft Excel

INDEX -MATCH - a much better lookup function combination than using VLOOKUP or HLOOKUP. Charley Kyd writes a very good instructive post on using this combination. We use this a lot where the failings of VLOOKUP make it impossible to use.

MSDN announces the new developer roadmap for Excel and VBA. Interesting reading and has lots of useful links.

Power Pivot is an amazing AddIn for Excel 2010. I have written about Power Pivot before. Dick Kusleika has written a simple function to determine whether an Excel Workbook contains power pivot data.

Microsoft Outlook

Conditional formatting and highlighting your emails based on content, subject, sender etc.

Roy MacLean asks some interesting questions about storing data in Outlook. I love Outlook and think it is a very good tool, however there are lots of limitations with using it. Mind you I don't see any other email client out there that can do everything that Outlook can do or even half as well in most cases. Still Roy has some good points.

Outlook 2010 has some pretty sweet features. Here are some of the new ones.

Use the Outlook Social Connector with Facebook, LinkedIn, MySpace and Windows Live

Today, we are announcing that you can use the Outlook Social Connector with Facebook and Windows Live. Our partners LinkedIn and MySpace are also releasing updates for their providers. All of the latest providers appear on the provider page.

Download the social connector software

Download each of the social connectors

Choosing the right communication modality with the contact card