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.

This is a cool function that can assist you to make easy to use adjustable tables and charts.
Using the example of a mortgage, I used the mortgage template spreadsheet in Excel (under New Worksheet) to create a mortgage example. I then added a graph of the closing balance.

So now we have a nice looking chart that shows the closing balance of our mortgage over time.
Lets say we want to add the ability to easily change the additional payments and see graphically what that will do to our loan timeline.

From the Forms toolbar select the spin button. Then click and drag your mouse where you want it on your sheet . You can resize it later if necessary.

Now right click the control and select format control. Set it up as follows.

Now you can scroll up and down and as you do the monthly loan payments changes by $10 a time, and the loan balance adjusts accordingly. Obviously you can make these settings what ever you desire for any application where you need to rapidly change 1 (or more) variable and see the results.

Today I had an email request:

I wasn't sure how to post, or join so I'm sending my question to you this way. I cannot figure out how to remove the #N/A error in LOOKUP. If the cells are blank it returns the #N/A error - I'd like to return either a blank or 0 instead. How do I do that? Her is my formula. (I'm sure you can tell by my formula I'm a rookie at this.) Any help is appreciated. Thanks!

=SUM((LOOKUP(B6,{1,2,3,4,5},{40,30,20,10,0}))+(LOOKUP(C6,{1,2,3,4,5},{40,30,20,10,0})+(LOOKUP(D6,{1,2,3,4,5},{40,30,20,10,0})+(LOOKUP(E6,{1,2,3,4,5},{40,30,20,10,0})+(LOOKUP(F6,{1,2,3,4,5},{40,30,20,10,0}))))))

I have written a post on this before when I explained the ISNA function.

In this case I will break down the first component of the formula and show you how to insert it.

First of all understand that because this formula is summing components that are lookups, if any one of them returns an error than the overall sum will be an error even if the rest is not an error.

So to fix =LOOKUP(B6,{1,2,3,4,5},{40,30,20,10,0}) when it displays #N/A (eg if B6 is less than 1) we would use this function:

=IF(ISNA(LOOKUP(B6,{1,2,3,4,5},{40,30,20,10,0}))=TRUE,0,LOOKUP(B6,{1,2,3,4,5},{40,30,20,10,0}))

The same concept could be used for each lookup component. In this case the formula would become very involved so there might be easier ways to do it. Sometimes breaking the individual components our into individual cells and then summing them is a way to give more visible results – and easily see which component is causing the error.

So Lois – i hope that helps – and thanks for liking us on Facebook!

One of our readers Daniel sent in an email a while back (actually 6 months ago sorry Daniel) about a detailed post he had written about how to Use Excel to find the Best Used Car Deals.

I wont repeat the whole article, but get you to go over to his site, read it and try it out and comment on how you found it to work for you. I will post a couple of snippets here for you as teasers.

A used car can have any combination year and mileage. Both of these factors contribute to depreciation and make it very difficult to price-compare cars. Should you buy a 2011 model with 10,000 miles, a 2005 with 50,000 or a 2007 with 15,000 on the odometer? This guide shows you how to use Excel to find the “sweet spot” in the used car market for any given model, where depreciation and mileage come together to give you the best deal.

You’ll learn how to quickly scrape data from hundreds of used car listings on the Web, plot it in Excel and discover which model years are the best deal for a given mileage count. Instead of comparing between a handful of models, you’ll be able to see the whole market on your computer screen. This strategy also reveals which cars are the most overpriced and exposes information about how dealers price cars. This project will take an hour or two to complete, but save you thousands of dollars on a used car purchase.

Read the rest of his very detailed article with links, screenshots, a sample spreadsheet to download and try it yourself. Thanks Daniel.