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:

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.