Filtering PivotTable data in Google Sheets - Dashboard Guide

Dashboard Mode - Filtering PivotTable data

Goal

See total sales by product category and region, and quickly filter the data to focus on specific regions or categories.

Sample Data

Order ID	Product Category	Region	Sales
1001	Electronics	East	250
1002	Furniture	West	450
1003	Electronics	West	300
1004	Clothing	East	150
1005	Clothing	West	200
1006	Furniture	East	500
1007	Electronics	East	400

Dashboard Components

• Pivot Table: Summarizes total sales by Product Category (rows) and Region (columns). Shows sales sums.
• Filter Slicer for Region: Lets user select one or multiple regions to filter the pivot table data.
• Filter Slicer for Product Category: Lets user select one or multiple product categories to filter the pivot table data.
• Formula for Total Sales: =GETPIVOTDATA("Sales", A3) where A3 is the grand total cell of the pivot table. This shows the grand total sales from the pivot table.

Dashboard Layout

+---------------------------+---------------------------+
| Filter: Region            | Filter: Product Category   |
+---------------------------+---------------------------+
|                           Pivot Table                  |
|                                                       |
|                                                       |
+-------------------------------------------------------+
| Total Sales: $2,250                                    |
+-------------------------------------------------------+

Interactivity

The Region and Product Category filter slicers let you pick which data to show in the pivot table. When you select a region or category, the pivot table updates to show only matching sales. The total sales formula updates automatically to reflect the filtered data.

Self Check

If you add a filter to select only the East region, which components update?

• The pivot table will show sales only for the East region.
• The total sales formula will update to show the sum of sales for East region only.
• The product category filter slicer remains available to further filter categories within East.

Key Result

Pivot table showing sales by product category and region with interactive filters for region and category.