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:
- Select the range of values we want to represent (from B2 to B8 here).
- 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.
- ClickÂ Conditional FormattingÂ again, and then choose theÂ Manage Rulescommand.
- Click theÂ Edit RuleÂ button.
- 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 Colorcommand. 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 theInsertÂ 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 chooseGroup 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!