Filtering PivotTable data in Excel - Dashboard Guide

Dashboard Mode - Filtering PivotTable data

Dashboard Goal

See sales data summarized by product and region, and quickly filter to focus on specific regions or products using a PivotTable filter.

Sample Data

Date	Region	Product	Sales
2024-01-05	East	Apples	120
2024-01-07	West	Apples	150
2024-01-10	East	Oranges	200
2024-01-12	West	Oranges	180
2024-01-15	North	Apples	90
2024-01-18	North	Oranges	110
2024-01-20	East	Bananas	130
2024-01-22	West	Bananas	160

Dashboard Components

• PivotTable: Summarizes total sales by Product (rows) and Region (columns).
  Formula: Use Excel's PivotTable feature with Sales as values, Product as rows, and Region as columns.
  Example output:
  

  Product	East	West	North	Total
  Apples	120	150	90	360
  Oranges	200	180	110	490
  Bananas	130	160	0	290
  Total	450	490	200	1140

• Region Filter: A filter on the PivotTable to select one or more regions to display.
  Example: Selecting only East and West updates the PivotTable to show sales only for those regions.
• Product Filter: A filter on the PivotTable to select one or more products.
  Example: Selecting only Apples and Bananas updates the PivotTable to show sales only for those products.

Dashboard Layout

+-----------------------+-----------------------+
|       Filters         |       Filters         |
|  Region Filter        |  Product Filter       |
+-----------------------+-----------------------+
|                                               |
|               PivotTable Summary               |
|                                               |
+-----------------------------------------------+

Interactivity

The Region Filter and Product Filter control which rows and columns appear in the PivotTable. When you select or deselect items in these filters, the PivotTable updates automatically to show only the selected data.

For example, if you select only East in the Region Filter, the PivotTable will hide sales from West and North. Similarly, selecting only Oranges in the Product Filter will show sales only for Oranges.

Self Check

If you add a filter to show only the North region, which products will show sales in the PivotTable?

Answer: Apples (90) and Oranges (110) will show sales. Bananas will show 0 or be hidden if filtered out.

Key Result

PivotTable dashboard showing sales by product and region with filters to focus on specific regions or products.