Software

Slicers provide a quick way to filter an Excel pivot table

Filtering a pivot table isn't hard, but Excel 2010 slicers make it even easier.

Whether you're training users or creating custom applications, slicers (new to Excel 2010) are an easy way to filter data in multiple ways without any specialized setup or knowledge. To illustrate this new feature, we'll need a simple pivot table. We'll work with the pivot table shown below; it's based on the data that follows (which came from the Northwind database that comes with Access).

To create this simple pivot table, do the following:

  1. Select the data set (click any cell in the data set and press [Ctrl]+[Shift]+8).
  2. Click the Insert tab.
  3. Click PivotTable in the Tables group, and click OK.
  4. Drag the Category field to the Row Labels list.
  5. Drag the Units On Order field to the Values list.

With a quick glance, you can determine which categories have units on order and how many. You can filter this display by checking and unchecking items in the Rows Labels dropdown. It's easy enough, but for the untrained user it can be a bit confusing. Besides, it takes at least four clicks.

A slicer is a bit easier to use, so let's add one that lets the user filter the categories:

  1. Click inside the pivot table.
  2. Click the contextual Options tab.
  3. Click Insert Slice in the Sort & Filter group.
  4. In the resulting dialog, check the filtering field. In this case, that's Category.
  5. Click OK.

To use the new slicer, simply click one of the categories and Excel will filter the pivot table records, accordingly. You can also filter more than one category. Drag the mouse over contiguous categories or hold down [Ctrl] to select non-contiguous categories.

Slicers are easy enough to implement and use that you can quickly show users to add them and use them for quick and easy filtering. In addition, you can insert more than one!

The example Excel worksheet is also available.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

2 comments
FilipVW
FilipVW

Nice, but when I try to add a slicer to an existing pivot that gets the data from a SQL Server database, the button "Insert Slicer" is disabled ...

ssharkins
ssharkins

The data is linked to, and not just imported?