Creating a PivotTable in Excel - Dashboard Building Guide

Dashboard Mode - Creating a PivotTable

Dashboard Goal

Understand total sales by product category and region to identify best performing areas.

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	120
1007	Chair	Furniture	East	200

Dashboard Components

• PivotTable: Summarizes total sales by Category (rows) and Region (columns). Shows sum of sales values.
• Grand Total: Shows total sales across all categories and regions.

PivotTable Setup Steps:

1. Select data range A1:E8.
2. Insert > PivotTable.
3. Place Category in Rows area.
4. Place Region in Columns area.
5. Place Sales in Values area (set to Sum).

PivotTable Result Example:

Category	East	West	Grand Total
Furniture	650	450	1100
Office Supplies	30	70	100
Technology	300	120	420
Grand Total	980	640	1620

Dashboard Layout

+-----------------------------+
|         PivotTable           |
| +---------+-------+-------+ |
| |Category | East  | West  | |
| +---------+-------+-------+ |
| |Furniture|  650  |  450  | |
| |Office   |  30   |  70   | |
| |Supplies |       |       | |
| |Technology| 300  |  120  | |
| +---------+-------+-------+ |
| |Grand Total| 980 |  640  | |
| +---------+-------+-------+ |
+-----------------------------+

Interactivity

Add a slicer for Region to filter the PivotTable by East or West. Selecting a region updates the sales totals to show only that region's data.

Add a slicer for Category to filter sales by product category. This updates the rows shown in the PivotTable.

Self Check

If you add a filter to show only Region = East, which sales totals update?

• The PivotTable columns for East will show sales values.
• The West column and its totals will disappear or show zero.
• Grand totals will update to reflect only East region sales.

Key Result

PivotTable showing total sales by product category and region with slicers for filtering.