In earlier versions of Microsoft Excel, you can use report filters to filter data in a PivotTable report, but it is not easy to see the current filtering state when you filter on multiple items. In Microsoft Excel 2010, you can use slicers to filter the data. Slicers provide buttons that you can click in order to filter PivotTable data. In addition to quick filtering, slicers also indicate the current filtering state, which makes it easy to see exactly what is shown in a filtered PivotTable report.
When you use a regular PivotTable report filter to filter on multiple items, the filter indicates only that multiple items are filtered, and you have to open a drop-down list to find the filtering details. However, a slicer clearly labels the filter that is applied and provides details so that you can easily understand the data that is displayed in the filtered PivotTable report.
Slicers are typically associated with the PivotTable in which they are created. However, you can also create standalone slicers that are referenced from Online Analytical Processing (OLAP) cube functions or that can be associated with any PivotTable at a later time.
One of the limitations of report filters was that they had a 1:1
relationship with the PivotTable they were filtering. If you wanted to apply
a filter to multiple PivotTables, you were out of luck; you’d have to
re-create that filter for each PivotTable. Now, you can connect slicers to
PivotTables, PivotCharts, and/or CUBE
functions to your heart’s content. Anything you do in the slicer will
conveniently apply to everything it’s connected to.
Inserting a slicer is easy:
From the PivotTable Options ribbon, click Insert Slicer (Figure 20-1).
The Insert Slicers dialog is shown. Select the fields you would like as slicers (Figure 20-2).
Note
If you have too many fields in your model that are making it hard to find what you’re looking for, you can filter the list by choosing to show only fields related to a single table using the drop-down list at the top of the Insert Slicers dialog.
Figure 20-3 shows the result of adding a slicer to the State attribute of the People table. This again highlights the power of PivotTables and PivotCharts as we are filtering ticket counts by using an attribute of the people who submitted the tickets. Just like any multiselect operation in Windows, you can Ctrl-click to select a noncontiguous range of items in your slicer.
You may wonder why we placed this section here instead of with the general PivotTable guidance earlier in this chapter. A key feature of slicers is the ability to connect to multiple PivotTables or PivotCharts, thus providing a unified experience when slicing our data. To complete this example, you’ll need to add an additional PivotTable.
Start by highlighting the cell where you’d like the new PivotTable to be inserted.
From the Insert ribbon, choose PivotTable (Figure 20-4).
The Create PivotTable dialog will appear, as shown in Figure 20-5. This dialog can be confusing and merits some explanation.
The top portion of this dialog allows you to define the data source for the PivotTable: either from a range of cells in this workbook, or an external data source. In your case, you are using your tabular cube, which already has an external connection, so let’s go with Use an external data source.
The bottom portion of this dialog deals with where the PivotTable will be inserted: either in a new worksheet or an existing worksheet. Because we selected the cell where we wanted the PivotTable before launching this dialog, the location of that cell is already selected for us.
Our next step is to Choose Connection... to define our external data source. This will launch the dialog shown in Figure 20-6. Notice that the highlighted connection already exists in this workbook. Let’s reuse that same connection for your new PivotTable.
After selecting your data source, you’ll see the connection in the Create PivotTable dialog as well as the location where you’d like your PivotTable placed, as shown in Figure 20-7. Go ahead and click OK to add the new PivotTable.
The new PivotTable is shown in its desired location, as shown in Figure 20-8. You can now reuse your basic PivotTable design skills to create an additional visualization of the data from our help desk model.
![Inserting a PivotTable in a specific cellComment [GM3]: Need figure title.](httpatomoreillycomsourceoreillyimages1715538.png)
Now that you have a second PivotTable in the workbook that is based on the same data as the original PivotTable and PivotChart, connect them to your State slicer. This enables you to filter all visualizations by a common set of filters using your slicers.
From the PivotTable Options ribbon, Select Insert Slicer → Slicer Connections (Figure 20-9).
This will launch the Slicer Connections dialog, as shown in Figure 20-10.
Select the State slicer and click OK.
We can test the new solution by filtering the slicer to Oregon. Figure 20-11 shows that your list of ticket creators was filtered correctly. Notice that the distinct ticket count of both PivotTables matches with a total of 40 tickets.
This allows you to visualize the data along multiple dimensions, showing the real power of your tabular cube. You can see ticket counts by product line filtered for a given set of states with visualization of the top ticket creators within that dataset.
But wait, there’s more! We are not limited to a single slicer when analyzing data. In Figure 20-12, we have repeated the process by adding an additional slicer limiting the results from a given state to a specific city. This example shows that Corvallis, Oregon has two customers who’ve submitted tickets against three product lines. Michelle and Sydney both submitted four tickets each and five of the eight tickets were for components.
This chapter built on the PivotTables from Chapter 19 by adding slices to visually filter our data. Slicers differ from normal PivotTable filters in their ability to connect to and cross-filter multiple PivotTables and PivotCharts. When multiple slicers exist connected to the same PivotTable such as State and City, they are also impacted by slice operations.