PivotTables and PivotCharts are fantastic Excel features that allow you to analyze data. Sometimes you need an easy way to manipulate only specific data and view it. This Trainer Tip will show you how to filter data in PivotTables and PivotCharts using Slicers.
Though you can filter data in a PivotTable using filters and by using the drop zones in the PowerPivot Field List pane, it may not be easy to understand what data you are looking for when multiple filters have been applied. Microsoft Excel provides Slicers to filter data with ease.
A Slicer is a filtering tool that provides you with options to include only the required elements in a PivotTable or PivotChart. Using Slicers, you can add and remove elements from a PivotTable or PivotChart so that data can be compared and evaluated from different perspectives. You can even create more than one slicer for a PivotTable or PivotChart. Slicers can be placed either on the same worksheet that contains the PivotTable or on a different worksheet.
Here’s how to do it:
- Insert a PivotTable or PivotChart into your spreadsheet.
- Select the PivotTable and in the PivotTable Tools Options contextual tab, in the Sort & Filter group, click Insert Slicer.
- Or, if you are creating a PivotChart, select the PivotChart and in the PivotChart Tools Analyze contextual tab, in the Data group, click Insert Slicer.
- In the Insert Slicers dialog box, check the check boxes for fields which you want to display slicers.
- Click OK to close the Insert Slicers dialog box.
- In a Slicer, select a field to filter values by the field in a PivotTable or PivotChart.
- Hold down Ctrl and click additional fields to filter the PivotTable or PivotChart by multiple field values.
- Hold down Shift and click a field to filter the PivotTable or PivotChart by a set of consecutive field values.
- If you’d like to delete the Slicer from the spreadsheet, just select it and then use the Delete key on your keyboard.
Here’s a picture of a PivotTable displaying data based on fields selected in slicers:
