Spy Journal 3.0 Filtering the web for you

Ok so that title was a little contrived – and only because my wife bought the classic Jules Verne book yesterday along with Mark Twain’s Tom Sawyer, Louisa Alcott’s Little Woman and Jack London’s Call of the Wild.

This article has a bunch of cool Microsoft Excel related content from all around the world.

10 worst Microsoft Excel practices. Michiel has created this list, but I certainly wouldn’t rank them the same way.

My Top three would be:

• Linked spreadsheet files (workbooks)
• Merged Cells
• Combining reports and data tables on the same worksheet – Michiel’s number 9. The other two don’t even make his list.

Allen Wyatt has a great article providing some VBA code to detect errors in conditional formatting formulas. This is a very practical solution to a very real problem.

Doug Klippert has posted some code from Peter Beach an Excel MVP that will create a list in Excel of all the folders on a drive and their sizes.

Andrew Engwirda has added some cool chart tools to his free Excel Tools. (News flash – he might come back to work for me! – Stay tuned)

Dick’s Daily Dose of Excel has two interesting posts (well lots actually but two I picked out) – the first written is on displaying image galleries in the ribbon written by Ron de Bruin, and the second titled Elle’s birthday.

Joseph has a very good explanation written for the new Excel 2007 tables of how not to break your summing formulas. The same concepts apply in Excel 2003 for the auto sum function using the Sigma button the toolbar.

Oh and that’s a photo of Kitty pretending to be interested in spreadsheeting techniques. Kitty is a business analyst working for Jethro.

I had an interesting question about conditional formatting posed in the comments by Stephen.

In a new sheet, I am trying to make a whole row turn red, green or amber depending on the value of one cell in that row, so I can easily see which jobs we have won, lost or are pending. Any 'IF' conditional formula I write gets thrown out by Excel. What am I doing wrong?

I promised him an answer so here it is.

For this exercise I am making some assumptions.

• You are using Excel 2007 format Excel spreadsheet (.xlsx or .xlsm). These instructions will not work in detail for Excel 2003, though the concept is similar.
• That there are 3 conditions we  are looking for. Of course Excel 2007 allows more than 3 conditions so you can add more if you need. (One of the improvements on Excel 2003 that only allowed 3 rules)
• That the entire row is needed to be coloured. If you need a smaller section than change the formulas accordingly.
• That the entire worksheet needs this formatting. If you need a smaller section than change the formulas accordingly.
• That the conditional formats are going to be based on a cell that returns a specific result based on some other rule.

Excel has made numerous changes in its conditional formatting between 2003 and 2007. Most of the new features I think are great, though there are a lot of problems with backward compatibility.

One thing I discovered this week is a trap for the unwary.

In Excel 2003 it was easy to create a conditional format for a cell, and then copy that cell or even just the formatting for that cell to other cells, and the conditional formatting would be copied also. Of course it pays to make sure that you correctly apply absolute and relative cell references in the conditional formatting if using formulas. And using paste special or the format painter you could copy just the formatting from once cell to another - including the conditional formatting.

Jean Philippe Bagel works for Microsoft. He has written some great tips for Excel 2007.

Of course the right click on the sheet tab navigation buttons will work in older versions of Excel and is a great way of navigating rapidly through large workbooks. Navipane provides a great alternative that is not as limited as Excels built in display of available sheet tabs.

Excel 2007 has some very advanced and easy to use conditional formatting and Jean explains one very well. However please be very careful with using this feature in any Excel 2007 workbooks that need to be viewed or edited on a Excel 2003 or lower machine. The conditional formatting in 2007 inst backward compatible. In fact it is quite different in both how it works and how it displays to previous versions of Excel.