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:
- Select the data set (click any cell in the data set and press [Ctrl]+[Shift]+8).
- Click the Insert tab.
- Click PivotTable in the Tables group, and click OK.
- Drag the Category field to the Row Labels list.
- 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:
- Click inside the pivot table.
- Click the contextual Options tab.
- Click Insert Slice in the Sort & Filter group.
- In the resulting dialog, check the filtering field. In this case, that's Category.
- 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!