Overview:  This blog entry discusses using slicers within Excel 2010 to filter the data being presented, with an emphasis on choosing between traditional filters & slicers. 

Level:  Basic understanding of an Excel PivotTable is helpful.

Slicing and Dicing a Set of Data

The phrase “slice and dice” is used a lot when referring to the analysis of data.  The Slicing part refers to filtering your data to focus on just a subset – essentially the “where” statement.  Dicing the data refers to selecting which attributes we’re grouping the data by.  For instance, if we have a PivotTable showing Internet Sales for 2006 and 2007 by Country and by State, then we have sliced the data by year and diced it by geography.

What is a Slicer in Excel 2010?

Excel 2010 introduces slicers as an alternative to traditional PivotTable filters (i.e., the Report Filter and the Row/Column Filters). 

Following is an example of how slicers are presented in Excel:

                                  The above screen shot uses publicly available data I originally downloaded from the UK Guardian Data Store.

If you’re not familiar with slicers, take a quick look at the following before reading on: 

• Easy (And Even Fun!) Data Exploration:  Introducing Excel 2010 Slicers, and
• Interacting with Slicers

Connecting Slicers to One or More PivotTables

Connecting a slicer to more than one Table is the greatest benefit of slicers & what makes them better than traditional PivotTable filters (i.e., the Report Filter and the Row/Column Filters).  Slicers are extremely useful if you create a dashboard page and you want one click to affect multiple items on the page.  There’s a catch though:  the multiple PivotTables must use the same underlying data connection. 

---> Hint! Using PowerPivot to mashup, or consolidate, your data from different sources can be of real benefit.

      

If you are displaying (and slicing on) a PivotChart, there must be a PivotTable “underneath” the PivotChart.  In the Book Sales Dashboard screen shot shown above, I placed the PivotTables which support the two charts on a hidden worksheet.

Choosing Between Slicers & Traditional PivotTable Filters

Technically, this is not an either/or decision.  Slicers can be used in conjunction with standard PivotTable filter functionality (i.e., the Report Filter, Column, and Row Filters). 

A Slicer may work better if:

• You want to control filtering of > 1 PivotTable with one click.  Since traditional filters are associated with one PivotTable only, slicers really have the potential to make Excel dashboard far more functional.
• You want to display the same field in the slicer as well as on the columns or rows.  The ability to slice & dice by the same field is not allowed by the Report Filter.
• Seeing the items currently selected directly on the worksheet is helpful (i.e., the current state of the filter), as opposed to “Multiple Items” in the Report Filter, or just a small icon which can easily be overlooked.
• Flexibility for placement, sizing, or formatting of the slicer is of importance.
• You only have 2-4 different fields to filter on (more than that can just take up so much space).

      

A traditional Report Filter may be better if:

• Space on the page is at a premium. Whereas slicers take up quite a bit of space, Report Filters show their selections via a compact drop-down menu.
• Your user base is familiar with standard PivotTable functionality.
• You have more than 4 or 5 different fields to filter on.
• The user interaction needs to be a hierarchical tree (ex: Country > State > City).  A Report Filter will show these in a tree structure, whereas a slicer will split them up into separate (but related) slicers.

        

A traditional Row/Column Filter may be the best choice if:

• You need Top 10, Label Filters, or Value Filters which are part of traditional PivotTable filters.  (Slicers and Report Filters work filter on attributes only.)
• Space on the page is at a premium. Whereas slicers take up quite a bit of space, Row/Column filters are embedded within the PivotTable itself.
• Your user base is familiar with standard PivotTable functionality.
• You have more than 4 or 5 different fields to filter on.
• The user interaction needs to be a hierarchical tree (ex: Country > State > City). A Row/Column Filter will show these in a tree structure, whereas a slicer will split them up into separate (but related) slicers.

      

Slicers in the PowerPivot Field List

A PivotTable that is associated to a PowerPivot data source has two additional Slicers panes available that the standard Excel PivotTable doesn’t have.

The “Slicers Vertical” pane in the PowerPivot Field List:

    

The “Slicers Horizontal” pane in the PowerPivot Field List:

    

When you add a slicer to the PowerPivot Field List (either the Vertical or Horizontal shown above), Excel automatically creates a rectangle outside of the slicer.  If you move the slicer around on the workbook, outside of the rectangle, the slicer will still work – however, it disappears from being shown in either of the slicer panes.  Basically it becomes a stand-alone slicer in this situation.

A stand-alone slicer used with a standard PivotTable Field List (i.e., not associated to PowerPivot):

     

 

Finding More Information

The Basics:

Excel 2010 Help – Use Slicers to Filter PivotTable Data

Excel Blog - Easy (And Even Fun!) Data Exploration: Introducing Excel 2010 Slicers

Excel Blog – Interacting with Slicers

More Advanced:

MSDN – How to Highlight Table Rows Based on Slicer Selection (a great technique if you’re ready for something a bit more advanced)

Javier Guillen’s Blog – PowerPivot Slicers

Jen Stirrup’s Blog – Excel Slicers and Good Interface Design