I remember like it was yesterday. Snowflakes were falling out of a lead gray sky to the delight or annoyance of passersby when I got the call. A voice, you know the kind that had maybe seen one too many frustrations as of late, told me that, while they appreciated the spreadsheet, they really needed the details and not just the totals. I sat back in my chair, confused. My report into the murky depths of the thing men call Koha had all the details when I sent it. What had happened? Had something happened to the report between the time I sent it and when they received it and why? Was I going to have to redo the whole thing? Most importantly, when was this attempt to make the story like the start of an old detective story going to stop?
After some investigation, it turns out the problem wasn’t some two bit crook, hardened criminal or even someone in a rubber computer gremlin mask ala Scooby Doo. The details, in fact, had been there the whole time. The problem was, well, me. Or at least the fact that I’d forgotten to expand the subtotals on the spreadsheet.
In Excel, there are some visual cues to know if your spreadsheet is using the subtotal function. To the left of your spreadsheet data, instead of being just the normal row count, there will be a gray area with a number of + or - signs in boxes down the middle and numbers in small boxes across the top. Usually it’s 1-3 at the top but there can be more. Another hint can be the fact that the row numbers skip, though that can also just indicate rows hidden with the “hide” command.
- If you’re seeing + signs, the subtotals are collapsed so you’re only seeing the totals. If the – signs are displaying, the subtotals are expanded to show the details.
- You can expand or collapse individual subtotals by clicking on the individual + or – signs. Clicking on a + sign expands the subtotal to show the details as well while the - sign hides the details and only shows the subtotal.
- You can expand or collapse the all subtotals of that level by clicking on one of the numbers in the boxes at the top of the area. Clicking on the number 1 will show only the grand total, the right most number (in this case 3) will show all of the details. The number or numbers between will show the various levels of subtotals.
In this case, I’d left the spreadsheet on level 2 so the subtotals displayed but the full details were still hidden. Oops!