Use pivot tables in Excel for stronger data analysis. This Excel pivot table tutorial also helps you summarize data.
Sometimes you may want focus on just a certain section of your data. Filters can be used to narrow down the data in your PivotTable, allowing you to view only the information you need.
To add a filter:
In our example, we'll filter out certain salespeople to determine how they affect the total sales.
- Drag a field from the Field List to the Filters area. In this example, we'll use the Salesperson field.
Adding a field to the Filters area
- The filter will appear above the PivotTable. Click the drop-down arrow, then check the box next to Select Multiple Items.
Checking the box for Select Multiple Items
- Uncheck the box for any items you don't want to include in the PivotTable. In our example, we'll uncheck the boxes for a few different salespeople, then click OK.
Choosing data to filter and clicking OK
- The PivotTable will adjust to reflect the changes.
The updated PivotTable
Slicers make filtering data in PivotTables even easier. Slicers are basically just filters, but they're easier and faster to use, allowing you to instantly pivot your data. If you frequently filter your PivotTables, you may want to consider using slicers instead of filters.
To add a slicer:
- Select any cell in the PivotTable.
- From the Analyze tab, click the Insert Slicer command.
Clicking the Insert Slicer command
- A dialog box will appear. Select the desired field. In our example, we'll select Salesperson, then click OK.
Choosing a field and clicking OK
- The slicer will appear next to the PivotTable. Each selected item will be highlighted in blue. In the example below, the slicer contains a list of all salespeople, and six of them are currently selected.
The inserted slicer
- Just like filters, only selected items are used in the PivotTable. When you select or deselect items, the PivotTable will instantly reflect the changes. Try selecting different items to see how they affect the PivotTable. Press and hold the Ctrl key on your keyboard to select multiple items from a slicer.
Selecting items from the slicer
You can also click the Filter icon in the top-right corner to select all items from the slicer at once.