Creating PivotTables

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:

  1. Drag a field from the Field List into the Report Filter area. In this example, we'll use the Salesperson field.
    Adding a Report FilterAdding a Report Filter
  2. 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.
  3. 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 FilterUsing a Report Filter
  4. Click OK. The PivotTable will adjust to reflect the changes.
    The updated PivotTableThe updated PivotTable

Slicers

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:

  1. Select any cell in your PivotTable. The Options tab will appear on the Ribbon.
  2. From the Options tab, click the Insert Slicer command. A dialog box will appear.
    The Insert Slicer commandThe Insert Slicer command
  3. Select the desired field. In this example, we'll select Salesperson. Then click OK.
    Selecting a fieldSelecting a field
  4. 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 itemsA 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.

Ctrl-clicking to select multiple itemsCtrl-clicking to select multiple items