Use pivot tables in Excel for stronger data analysis. This Excel pivot table tutorial also helps you summarize data.
Using report filters
Sometimes you may want focus on a portion of the data and filter out everything else. In our example, we'll focus on certain salespeople to see how they affect the total sales.
To add a report filter:
- Drag a field from the Field List into the Report Filter area. In this example, we'll use the Salesperson field.
Adding a Report Filter
- The report filter appears above the PivotTable. Click the drop-down arrow on the right side of the filter to view the list of items.
- Select the item you want to view. If you want to select more than one item, place a check mark next to Select Multiple Items, then click OK. In the example below, we are selecting four salespeople.
Using a Report Filter
- Click OK. The PivotTable will adjust to reflect the changes.
The updated PivotTable
Slicers were introduced in Excel 2010 to make filtering data easier and more interactive. They're basically just report filters, but they're more interactive and faster to use because they let you quickly select items and instantly see the result. If you filter your PivotTables a lot, you might want to use slicers instead of report filters.
To add a slicer:
- Select any cell in your PivotTable. The Options tab will appear on the Ribbon.
- From the Options tab, click the Insert Slicer command. A dialog box will appear.
The Insert Slicer command
- Select the desired field. In this example, we'll select Salesperson. Then click OK.
Selecting a field
- The slicer will appear next to the PivotTable. Each item selected will be highlighted in blue. In the example below, the slicer contains a list of the different salespeople, and four of them are currently selected.
A slicer with four selected items
Using the slicer:
Just like with report filters, only the 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.
- To select a single item, click it.
- To select multiple items, hold down the Control (Ctrl) key on your keyboard, then click each item you want.
- You can also select multiple items by clicking and dragging the mouse. This is useful if the desired items are adjacent to one another, or if you want to select all of the items.
- To deselect an item, hold down the Control (Ctrl) key on your keyboard, then click the item.
Ctrl-clicking to select multiple items