Slicers! A better way to filter Pivot Tables (Excel 2010)

If you use pivot tables, then you already know they are one of the best features in Excel. The good news is that Excel 2010 actually makes using pivot tables even better with their creation of the slicer. Sounds like a Ron Popeil commericial doesn't it?  It slices, it dices.....................

Anyway, in earlier versions of Excel, you could filter pivot tables but the field you filtering had to be in the pivot table and the drop-down box would just display the text "multiple fields" so no one at a glance knew what the heck you were filtering and displaying in your pivot table.

The slicers are essentially dialog boxes containing all the field names that remain on the screen. You select the ones you want and the data in the pivot table adjusts to what is specified.

Here are the steps

• Click in your pivot table

• Click on the contextual Options tab
• Select Insert>Slicer
• Click Insert Slicer...

A dialog box of all the field names that comprise your data (not just the data in the current pivot table) displays

• Select your field (s)
• In this example, I selected multiple fields -Ship Via and Customer Name

• Click OK

The selected slicers display on the spreadsheet

• Click on the fields that you want to see.

 To select multiple fields in Ship Via for instance, select the first one and then hold down the control key to select the next.     

In my example, I selected Fed Ex, Parcel Post and UPS from the  Ship Via slicer and I selected the Backyard Cycle Shop as the customer I was interested in.

The pivot table now displays only data  for that one customer if one of those 3 specified carriers were used.

How cool is that.  The slicers stay there on the page and can be moved around so now you can clearly see that is being filtered and displayed in the pivot table. Think how useful this could be on a dashboard.