(This article first appeared as an Office Hours column)

Last week I introduced you to J.P. Bagel, one of our serious Excel experts, and showed you one of his tips: How to see ALL the tabs of your many worksheets — a particularly good tip for complex workbooks.

Today’s tip from J.P. (which I believe he refers to as “real magic”) takes what seems to be a complicated topic (conditional formatting rules) and shows you how to make friends with it.

Condition your list — gently and automatically

“How many of you use spreadsheets to keep track of … 'things' (for lack of a better word)? To use a very simple scenario, let's pretend we collect attendance to an event, and that you simply enter an "X” next to the name of the person you know will attend the event, as pictured below on the left. Excel 2007 provides great new conditional formatting rules, which will allow automatic display of nicely formatted icon sets. Would our previous list not look better, as pictured below, on the right?

List of attendeesConditional formatting with Excel 2007

Let's see how we get there. First you'll need to remember that Excel is a spreadsheet, and that it thrives on dealing and understanding numbers. We should therefore think about representing attendances using values as opposed to text. It's not that complicated … Let's just say that someone attending the event will now be represented by a 1 (as opposed to an "X") in the Will Attend column. Someone not coming will be represented with a zero. Still with me?

Now for the real magic, let's follow those 5 easy steps:

  1. Select the range of values we want to represent (from B2 to B8 here).
  2. Click Conditional Formatting on the Home tab, then choose Icon Sets, then pick 3 Symbols (Uncircled) in the gallery of options. They're represented by a green check box, a yellow exclamation point and a red "X". At this point, you have both the values (0 or 1) and the icon represented on the sheet.
  3. Click Conditional Formatting again, and then choose the Manage Rules command.
  4. Click the Edit Rule button.
  5. Check the Show Icon Only check box, click Apply, then click OK. Voilà!

Where Excel 2007 shines even more is that you can now filter this list by color. With the active selection being anywhere in the list, click the Data tab, then the Filter button. As you can see each column header now includes a drop-down arrow indicating it is filtered. Click the filter on the Will attend column, and then choose the Filter by Color command. See how you can now filter by green check boxes, showing only the people who will attend the event."

Example of conditional formatting

Who knew conditional formatting could be so…so…shiny and friendly?

Next week, for our final installment of J.P.’s Excel 2007 tips (unless I can persuade him to offer up some more): Date grouping in PivotTable reports.

— Annik