Mid Lakeshore Area Chapter
International Association of Administrative Professionals



Tech Tips

by Jean Philippe Bagel

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:

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!