Filter Data using Slicers
Slicers are one-click filtering controls that narrow down the data shown in PivotTables and PivotCharts. Slicers can be used interactively to display data changes when you apply filters. For example, you can create a PivotTable report or chart that shows sales by year, and then add a Slicer that represents promotions. The Slicer is added as an extra control in the PivotTable or chart, and lets you quickly select criteria and instantly show the changes. You could also embed the breakdown by promotion in the report itself, by including the field in the row or column heading, but Slicers do not add extra rows to the table, only provide an interactive view into the data.
Slicers are also explained in Excel Online Help.
Note
Slicers that are controlled by PowerPivot are laid out by an internal algorithm and are readjusted back to that layout when the UI is refreshed. If you make changes to the layout of PowerPivot slicers, those changes might be lost when the worksheet is refreshed. To avoid this behavior, drag the slicer out of the PowerPivot slicer zone, and then PowerPivot will not control the slicer layout.
Adding and Working with Slicers in PowerPivot
To add and use a Slicer
In the Excel window, click anywhere in a PivotTable to display the PowerPivot Field List.
In the PowerPivot Field List, drag the field that you want to filter by into the Slicers Vertical area or the Slicers Horizontal area.
The Slicer appears in the workbook with the Slicer items displayed. By default, when a new Slicer is added, all of the Slicer items are selected.
Click an item in the Slicer to apply a filter to the data shown in the PivotTable.
You can select multiple items within the Slicer for display in the PivotTable by holding down the CTRL key while you click each of them. To clear the Slicer and display all data, click the Clear button in the top right corner of the Slicer.
Note
Alternatively, you can add a Slicer by using the ribbon in the Excel window. Under Table Tools, on the Options tab, in the Sort and Filter group, click Insert Slicer, select the checkboxes next to the fields that you want to filter by, and then click OK. If you use this method, you don't get the benefit of automatic Slicer layout.
PowerPivot arranges the layout of the Slicers automatically. When you add a Slicer to the Slicers Horizontal area of the PowerPivot Field List, the Slicer is placed in the horizontal zone beside the PivotTable. When you add a Slicer to the Slicers Vertical area, the Slicer is placed in the vertical zone. The placement of the Slicer is for convenience only, and does not affect the selection of the data. These zones contain only the Slicers that are managed by PowerPivot for Excel. If you drag a Slicer completely outside of the zone, PowerPivot will stop managing that Slicer and it will be as if you inserted it by using the Insert Slicer button on the Excel ribbon. Conversely, if you drag a Slicer that you added by using the Insert Slicer button into one of the PowerPivot zones, PowerPivot will start to manage that Slicer.
To rearrange the order of the Slicers in a workbook
- In the PowerPivot Field List, right-click the field name in the Slicers Vertical area or the Slicers Horizontal area, and select Move Up, Move Down, Move to Beginning,or Move to End to change the order.
To resize and reposition Slicers
In the Excel window, click anywhere in a PivotTable to display the PowerPivot Field List.
If any Slicers have been added by using PowerPivot, a grey border will appear around the Slicer zones beside the PivotTable.
Click and drag the grey border around a Slicer zone to resize or reposition it.
Most changes that you want to make to an individual Slicer can be made by adjusting the border. If you want Slicers to be side-by-side, drag them next to each other and they will snap inside the grey border.
To remove a slicer
- In the PowerPivot Field List, right click the Slicer in the Slicers Vertical area or the Slicers Horizontal area, and click Remove Field.
Important
If you add a PowerPivot Slicer to a PivotTable or PivotChart, the Slicer cannot be disconnected by using the standard Excel PivotTable connections option. Disconnecting a PowerPivot Slicer in this way can cause unpredictable behavior; therefore, PowerPivot for Excel will automatically reconnect it.