20/9/2013

The Slicer: Cutting Pivot Table Data to Size

 

By Abbott Katz, London-based Excel instructor and freelance writer, author of Excel 2010 Made Simple and spreadsheetjournalism.com.

The difference between a mistake and a shortcoming is in the packaging. If your math teacher tells you 2 plus 2 is 3, it's a mistake; if she tells you it's 4 but expounds her little equation in Sanskrit, it's a shortcoming. (And if you actually speak Sanskrit my analogy exhibits a shortcoming all of its own, doesn't it?)

And so it is with Excel's pivot table filter, an efficient and understated way to isolate and display a set of records from a larger whole that meet a specified criterion, while excluding from view the records that don't. Thus, for example, if you've been entrusted with a spreadsheet tracking campaign contributions by political parties (say in the UK), the filter will let you present only Labour or only Conservative contributions.
Of course, filters aren't confined to pivot tabling. You're all familiar with this button:

Screen_shot_2013-09-19_at_3.32.52_PM.png

Which when clicked, enables the user to extract a subset of records from its superset of data, e.g.

Screen_shot_2013-09-19_at_3.34.15_PM.png

Excel's pivot table Report Filter (called the Page field in an earlier life, a reference that suffered from shortcomings of its own) works similarly, if not quite identically, affording the user a means for pinpointing specific records from a position above the pivot table, as it were. Consider this unremarkable collection of data, which you can download here.

Screen_shot_2013-09-19_at_3.38.31_PM.png
 

Move the data through a pivot table and assign the fields thusly:

Report Filter: Name

Values: Test Score.

Clicking the Report Filter's down arrow unrolls the list of student names; the next click reports any selected student's score, e.g.:

Screen_shot_2013-09-19_at_3.39.49_PM.png

(Note the default mathematical operation, sum.)

That's surely straightforward, as it's meant to be (imagine 50,000 student test scores cascading down the spreadsheet, for example, instead of our diminutive ten, and the feature begins to make more sense).

That's all well and good, but in the interests of feature richness, Excel's 2007 release enlarged the filter's grasp, empowering it to return multiple entries. Click the Filter's Select Multiple Items option and proceed to tick the desired boxes e.g.:

Screen_shot_2013-09-19_at_3.40.46_PM.png

And you'll wind up with something like this, alas:

Screen_shot_2013-09-19_at_3.41.35_PM.png

The test scores - in the plural - are here indeed summed, but the (Multiple Items) entry in the Report Filter field is Excel's way of stating that you can't know exactly who those items, or students, are - and that's a… shortcoming.

And Microsoft knew it. So by the time release 2010 begged your attention a footnote of sorts attached itself to the Report Filter - the Slicer:

Screen_shot_2013-09-19_at_3.43.00_PM.png

The Slicer is an easy-to-use, curiously free-standing window on the contents of the Report Filter. In our case, if we drag Names away from its Report Filter berth and click PivotTable Tools > Options > Insert Slicer and tick Names, OK, in the Insert Slicers (note the plural reference; more on that later) dialog box, you'll conjure this tableau:

Screen_shot_2013-09-19_at_3.44.22_PM.png

Note the Slicer has inherited the previous Ed/Jane determinations, but in any case you've likely already understood the essential workings of the feature. Click any name, and the Slicer registers that student's score in the Values area; and you can earmark multiple selections by keying the ageless Ctrl-click tandem for non-adjacent names, or Shift-click to nominate contiguous ones (that is, Shift-clicking the first and last items in the span of names).

The Slicer's advantage, then, is in the first instance informational: parachute it upon the data and the user, and everyone else can glimpse precisely those items in the field designated for reporting. And because it isn't locked into place you can drag the Slicer about on the worksheet, and even copy and/or move it to a companion worksheet (just click a Slicer border in order to select it and apply either the good old Ctrl-C or Ctrl-X strategy), where it can continue to finesse the data (you can also delete a Slicer by selecting it by one of its boundaries and pressing Delete).

What the Slicer (and, for that matter, the standard Report Filter) won't do is enable the view below, one about which a student asked me just the other day:

Screen_shot_2013-09-19_at_3.45.23_PM.png

in which the names of the students feature squarely in the pivot table (it should be added incidentally that the Grand Total above is likely irrelevant; you're not likely to care about the total points a filtered set of classmates accumulate, but you might be concerned to determine their average, for example). If you want to realise the above view, you'll need to tow the Names field from the Report Filter into the Row Labels area. Once there, however, the Slicer can continue to sift the names.

On the other hand, one shouldn't get too carried away over that capability, because you can do much the same without the Slicer, by clicking the filter down arrow shadowing the Row Labels header:

Screen_shot_2013-09-19_at_3.46.15_PM.png

Now while it appears as if the Slicer was initiated into Excel's feature set in order to relieve the presentational problem we described earlier, the Slicer also offers up a couple of other substantive assets that could serve the user well. It's not a terribly well-practiced stratagem, but you can stack two fields worth of data into the Report Filter area. For example, download this large collection of school assessments conducted by OFSTED, the UK agency charged with inspecting the nation's educational establishments:

The workbook enables a good many interesting permutations, enabling the journalist to break out several types of assessment scores by type of school, school phase (i.e., primary or secondary), geographical positioning, parliamentary authority, and the like. For starters, then, we could dust off a pivot table bearing this initial structure:

Values:  Quality of Teaching (by Average)

Report Filter: Local Authority (education)

Type of Establishment

Now here's the problem. By clicking a particular Local Authority, say the London borough of Barnet, the accompanying filter field - Local Authority - does not automatically confine its data to those kinds of Authorities represented in Barnet. To illustrate: If I leave Barnet in place, and click the Foundation Special School establishment (look here for an explanation of this school type), the Values area yields:

Screen_shot_2013-09-19_at_3.49.53_PM.png

Nothing, and that's because there are no Foundation Special Schools in Barnet; and as such, one might have supposed the double-Report Filter above would have clued the user about that absence, or pre-empted the complication, but it doesn't.

But select the Local authority and Type of establishment Slicers instead and click Barnet in the former, and you'll see:

Screen_shot_2013-09-19_at_3.51.44_PM.png

That's more like it. We see that a selection in one Slicer has a conditioning effect on the other. The Foundation Special School establishment type is dimmed, reflecting that type's absence in Barnet. Click the Birmingham Local authority, by contrast, and Foundation Special School makes itself available. It's a cool and useful feature.

And there's one more generic filter tip you'll want to know about. If you return to the conventional Report Filter mode by dragging a field into its confines and see to it that the field shows (All) - that is, you haven't selected any particular field item - then click PivotTable Tools > Options > Options (in the PivotTable button group) Show Report Filter Pages…, and click OK in the Show Report Filter Pages window (which asks you to decide between filter fields, in the event you've thrown two or more of them into the Report Filter area). Once you've made your choices, Excel immediately turns out a new worksheet for each item in the filtered field - a series of mini-reports compiled around whatever fields you've tossed into the Values area. Thus, if I frame a Pivot Table with these constituents:

Report Filter: Government office region (make sure it shows (All))

Values: Quality of Teaching  (set, say to Summarize Values by Average)

and execute the Show Report Filter Pages routine, a parade of new spreadsheet tabs should expand the workbook, e.g.,

Screen_shot_2013-09-19_at_3.52.41_PM.png

with each - reporting the average Teaching score per region.

I don't know about you, but I've been so inspired by all this I'm having a slice for dinner tonight - of pizza, that is.

Comments