Spy Journal 3.0 Filtering the web for you

As Office 2010 becomes more prevalent in the workplace (most of our clients either have or will be soon updating to it) so the use of the ribbon has become more natural and intuitive for people. It is certainly much more intuitive than the old menu driven interface.

Play a fun game called Ribbon Hero 2 that allows you to learn the fastest and best ways to do various common tasks in the various Office Apps. My kids are using this to learn the interface and some of these common tasks. I have even learnt some things in the 5 minutes I have spent playing it so far! Download Ribbon Hero 2.

Take the first step in growing your Excel Skills. Microsoft have created a series of learning videos that assist new users to Excel to grow their skills

Learn how to create your own Excel Macros with a free training course. Ideal for the entry level person who wants to explore macro writing for the first time. Note this doesn't create efficient code, but its a start to understanding how the VBA code interacts with Excel.

PowerPivot for Excel - if you have looked or used PowerPivot (See my review of Power Pivot) than this page has some good links for additional resources.

I have had a bunch of pretty cool Excel things to post up – and finally got around to clearing my flagged items and browser windows.

Conditional Formatting

I have written a couple of articles on Conditional formatting in Excel 2007 with lots of readers comments and requests for help. They are the two most read articles on this site.

• Conditional formatting in Excel 2007 - entire row colours
• Excel 2007 Conditional Formatting

I was very interested then to come across this article on Joseph’s site by Amit Velingkar where he shows you how to change the automatic colour ranges that are used in Excel 2007 for conditional formatting. He even includes some VBA code for this.

When I wrote the how to use VLOOKUP and HLOOKUP article a few weeks ago I hinted at writing how to use the COLUMN, ROW, MATCH, INDEX and OFFSET functions to enhance the use of the VLOOKUP and HLOOKUP functions. Today we will look at the use of MATCH.

First of all here is a common scenario.

You create a table of say 4 columns, and then in another sheet you create a VLOOKUP function that returns results from the 4th column. EG =VLOOKUP(lookup_value,table_range,4,FALSE) where the 4 relates to the 4th column. This works great until one day you (or your colleagues) delete the 3rd column in the table, or insert another column in the middle of the table. Now your VLOOKUP fails as the 4th column either doesn’t exist, or is now actually the 5th column. In this case the VLOOKUP formula you created is just not flexible enough to handle the changes.

The following explanation builds on the dynamic range name process defined in my article written back in 2004. You will need to create a dynamic range name (in this example called table_header) that works on the header row of the table you are performing the VLOOKUP on.

In Excel 2007 Use the Name Manager from the Formula tab. In Excel 2003 and earlier use Edit Insert Name Define. In both versions CTRL F3 is the keyboard short cut.

I realised that when I wrote the ISNA function article that I had never written an explanation of VLOOKUP. I want to write up explanations for a umber of other functions in the future like COLUMN, ROW, MATCH and INDEX followed by OFFSET. All these make lots of sense when you use them with the VLOOKUP function. So I though it best to start with this function. I will also say that everything I write about VLOOKUP here applies to HLOOKUP as well. the only difference is the orientation, that is VLOOKUP looks across columns from left to right, and HLOOKUP looks down rows from top to bottom.

The built in Excel Help is very good at helping with this function. However it doesn't point out many of the pitfalls than occur in common use.