Creating a PivotTable in Google Sheets - Dashboard Building Guide

Dashboard Mode - Creating a PivotTable

Goal

Find total sales by product category and region to see which areas perform best.

Sample Data

Order ID	Product	Category	Region	Sales
1001	Chair	Furniture	East	250
1002	Desk	Furniture	West	450
1003	Pen	Office Supplies	East	30
1004	Notebook	Office Supplies	West	70
1005	Monitor	Technology	East	300
1006	Keyboard	Technology	West	150
1007	Chair	Furniture	East	200

Dashboard Components

• PivotTable: Shows total sales by Category (rows) and Region (columns).
  Formula: Use Google Sheets menu Data > Pivot table with:
  • Rows: Category
  • Columns: Region
  • Values: SUM of Sales
• Total Sales KPI: Displays total sales overall.
  Formula: =SUM(E2:E8) (assuming sales are in E2:E8)
• East Region Sales KPI: Shows total sales for East region.
  Formula: =SUMIF(D2:D8, "East", E2:E8)
• West Region Sales KPI: Shows total sales for West region.
  Formula: =SUMIF(D2:D8, "West", E2:E8)

Dashboard Layout

+-----------------------+-----------------------+
|      Total Sales      |   PivotTable (Sales   |
|        KPI            | by Category & Region) |
+-----------------------+-----------------------+
| East Region Sales KPI | West Region Sales KPI |
+-----------------------+-----------------------+

Interactivity

Add a filter for Category above the PivotTable. When you select a category, the PivotTable and all KPIs update to show sales only for that category.

Steps:

• Insert a filter dropdown for Category.
• Connect filter to PivotTable and KPI formulas using FILTER or QUERY functions if needed.
• When user picks a category, all numbers refresh to match that choice.

Self Check

If you add a filter to show only Furniture category, which components update?

• PivotTable will show sales only for Furniture by region.
• Total Sales KPI will show sum of Furniture sales.
• East and West Region Sales KPIs will show sales for Furniture in their regions.

Key Result

Dashboard shows total and regional sales by product category using a PivotTable and KPIs.