Jean Philippe Bagel: Office Hours: Hidden gems in Excel 2007

Jean Philippe Bagel is the group manager for Office Developer Documentation, which spans a wide array of subjects, ranging from VBA topics to SharePoint Developer content. Before this, he created the MSDN Online Office Developer Center , where we still publish all of our content. At home, he spends time with his wife and two delightful kids. In his free time, he builds LEGO sets, plays a little XBOX (only when the kids sleep) and reads voraciously.

Sometimes love at first sight lasts for years. This week's columnist offers new and interesting ways of using Excel. J.P. loves Excel like no one we know, so sit up straight and pay attention to what he has to say. Then watch your efficiency speed up (while having some fun too).

Over the years of working for Microsoft, I've gotten to see incredible use of our products and be the de facto technical support contact for family and friends. This column provides me with an opportunity to share broadly some of the solutions I've given, as well as exposing some of the features others have taught me, and which I think are truly hidden gems in various applications of the Microsoft Office System.

I started at Microsoft as a localizer (translator) in Excel, and in my current job I spend a lot of my time in this application, so it's only fitting I share Excel tips in this column. Today, let's review how to quickly navigate in an Excel workbook, display an icon set for a True or False condition, and group dates in a PivotTable report.

So many worksheets, so little space

Have you ever had an Excel workbook with multiple sheets, those sheets having names other than the proverbial Sheet1, Sheet2, and Sheet3? My wife's business has many great examples of this, where business data is being kept in various workbooks, where tabs are corresponding to months, from January to December:

Rather quickly, you get to the point where you no longer can see all the tabs in one view. Of course, you can use the built-in tabs navigation buttons, and go the next, previous, first, or last sheet, but wouldn't it be great if there was a way to see all the sheets and be able to click on the one you want to work on? Well, there is one, and it has been there since Excel 4 (I think). Simply right-click the tabs navigation buttons and a floating list of all the worksheets in the workbook will appear, as pictured below. It is especially useful when sheet names are long. I use this time and time again, and find it the best way to navigate complex spreadsheets. Now seems just the perfect time to also remind you of two great keyboard shortcuts: CTRL+PageUp activates the previous sheet in your workbook, while CTRL+PageDown activates the next one.

Condition your list — gently and automatically

For this next gem, I want to thank Mike, a colleague which ran into this a few days ago, which reminded me to include it in this column. 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?

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.

Date grouping in PivotTable reports

For the final gem in this column, we are going to explore PivotTable reports, and particularly grouping by dates. A few weeks ago, I was sifting through some Office Online metrics data for the Developer Help topics. Nine months after launch, the data is starting to be really interesting. Every day, our system captures the following data: the help topic, the number of page views, and the date, as shown in the excerpt below. Of course the complete sheet is tens of thousands of rows, listing every day since November 2006.

My goal was to represent this data, sorted by month and help topic, where Excel aggregated hits. I succeeded, by at first reinventing the wheel, breaking down the information from the Date field into a Year column, then a Month one. My colleague Dearbhla in Ireland confirmed my hunch that Excel could handle this in a much easier way! And this is not new to Excel 2007, either!

First I created a simple PivotTable, clicking PivotTable, from the Tables chunk on the Insert tab. This will display an empty PivotTable, with the PivotTable Field List pane open. From there:

• I dragged and dropped Title in the Row Labels area.
• I dragged and dropped Hits in the Values area (where Excel automatically chose to perform a sum).
• I dragged and dropped Date in the Column Labels area.

As this stage, the PivotTable sorted the data by topic, and by day, as exposed below. This was a step in the right direction, but hardly exposing actionable data. As you can see, some days some help topics never get read, and with every day represented, there is data exposed all the way to the JY column. This is where grouping by month becomes extraordinarily handy and easy.

All that is needed to obtain a logical grouping by month is to place the cursor on one of the days, then choose the Options tab in the PivotTable Tools group, then choose Group Selection, which brings up the Grouping dialog. In the By list, select Months and Years, then OK. The PivotTable is now ideally aggregated, as pictured below:

The subject of PivotTables would warrant many more columns, and this is true as well for many other Excel features we could expose. I remember vividly how I got started in Excel with version 2.1c, writing XLM macros. To this day, Excel remains my favorite application, and when there's too much data to analyze, there's always Access to help out!