Why PivotTables summarize large datasets in Excel - Dashboard Impact

Dashboard Mode - Why PivotTables summarize large datasets

Goal

Understand how PivotTables help summarize and analyze large sales data quickly.

Sample Data

Order ID	Region	Salesperson	Product	Units Sold	Unit Price	Sale Date
1001	East	Alice	Widget	10	15	2024-01-05
1002	West	Bob	Gadget	5	20	2024-01-06
1003	East	Alice	Gizmo	7	25	2024-01-07
1004	South	Charlie	Widget	3	15	2024-01-08
1005	West	Bob	Gizmo	8	25	2024-01-09
1006	East	Diana	Gadget	6	20	2024-01-10
1007	South	Charlie	Gizmo	4	25	2024-01-11

Dashboard Components

• KPI Card: Total Sales Amount
  Formula: =SUMPRODUCT(E2:E8,F2:F8)
  Explanation: Multiplies Units Sold by Unit Price for each row, then sums all to get total sales.
• PivotTable: Sales by Region and Product
  Rows: Region
  Columns: Product
  Values: Sum of Sales Amount (Units Sold * Unit Price)
  Explanation: Quickly summarizes total sales per region and product.
• PivotTable Formula for Sales Amount: Add a calculated field in PivotTable:
  =Units Sold * Unit Price
• Chart: Bar Chart of Sales by Region
  Data source: PivotTable summary of sales by region.
  Visualizes which region has highest sales.

Dashboard Layout

+----------------------+----------------------+
|      KPI Card        |      Bar Chart       |
|  Total Sales Amount  |  Sales by Region     |
+----------------------+----------------------+
|                  PivotTable Summary               |
|          Sales by Region and Product               |
+---------------------------------------------------+

Interactivity

Use a slicer connected to the PivotTable for Salesperson. Selecting a salesperson filters the PivotTable and updates the bar chart and KPI card to show sales only for that person.

Self Check

If you add a filter for Region = East, which components update?

• The PivotTable will show sales only for the East region.
• The bar chart will update to show sales for East only.
• The KPI card will recalculate total sales for East region.

Key Result

Dashboard shows total sales and sales breakdown by region and product using PivotTables and charts.