←Module 7
Lesson

Doing More with PivotTables

Learn how to perform deeper analysis with PivotTables by filtering, grouping, and refreshing summarized data.

Video

Watch the lesson video, then complete the reading and challenge.

Lesson Notes

Read through the key concepts before you try the challenge.

Filtering PivotTables

PivotTables allow you to filter data to focus on specific information. You can filter by fields such as Salesperson, Region, or Month to quickly analyze subsets of your dataset.

To filter a PivotTable, drag a field such as Salesperson into the Filters area of the PivotTable Fields panel.

Adding a field to the PivotTable filter area

Filtering Multiple Items

You can filter multiple values from a PivotTable field. Select the filter dropdown, enable Select Multiple Items, and choose the items you want to analyze.

Selecting multiple filter items in PivotTable
Unchecking filter options in PivotTable

Using Slicers for Visual Filtering

Slicers provide a visual way to filter PivotTable data. Instead of dropdown menus, slicers use clickable buttons that allow you to filter data quickly.

To insert a slicer, select the PivotTable and go to PivotTable Analyze → Insert Slicer.

Insert slicer button in Excel
Slicer interface in Excel

Filtering with Slicers

You can click individual buttons in the slicer to filter the PivotTable instantly. Hold Ctrl to select multiple items.

Selecting slicer filters
Filtering data with slicers

Creating PivotCharts

PivotCharts allow you to visualize PivotTable data in a chart format. When the PivotTable changes, the chart updates automatically.

Select the PivotTable and go to Insert → PivotChart.

Insert PivotChart menu
PivotChart creation dialog

Interacting with PivotCharts

PivotCharts remain connected to their PivotTables. When filters or slicers change, the chart updates automatically.

PivotChart appearing in Excel
PivotChart switching views

Final Result

After applying filters, slicers, and charts, you can quickly analyze patterns in your dataset. PivotTables and PivotCharts together provide powerful tools for summarizing and visualizing data.

Final PivotTable and PivotChart result

Knowledge Check

What does a Slicer do in a PivotTable?

Practice File

Download this file and follow along with the lesson.

Download Practice Workbook

Challenge

Apply what you've learned in this lesson.

  1. Open the practice workbook.
  2. Create a PivotTable from the dataset.
  3. Add Salesperson to Filters.
  4. Filter the PivotTable to show only two salespeople.
  5. Insert a Slicer for Salesperson.
  6. Use the slicer to change which salespeople appear.
  7. Create a PivotChart to visualize the sales totals.
What-If Analysis →