PivotTable layouts in Excel - Dashboard Guide

Dashboard Mode - PivotTable layouts

Dashboard Goal

Understand how different PivotTable layouts show sales data by Region and Product Category to analyze total sales and quantity sold.

Sample Data

Region	Product Category	Sales	Quantity
East	Furniture	1200	10
East	Office Supplies	800	20
West	Furniture	1500	15
West	Technology	2000	8
South	Office Supplies	700	12
South	Technology	1800	7
North	Furniture	1300	9
North	Technology	2200	11

Dashboard Components

• PivotTable 1: Compact Layout
  Shows Region and Product Category in one column with indents.
  Fields: Rows = Region, Product Category; Values = Sum of Sales, Sum of Quantity
  Formula: Use Excel PivotTable with layout set to Compact Form
• PivotTable 2: Outline Layout
  Shows Region and Product Category in separate columns.
  Fields: Rows = Region, Product Category; Values = Sum of Sales, Sum of Quantity
  Formula: Use Excel PivotTable with layout set to Outline Form
• PivotTable 3: Tabular Layout
  Shows Region and Product Category in separate columns with repeated labels.
  Fields: Rows = Region, Product Category; Values = Sum of Sales, Sum of Quantity
  Formula: Use Excel PivotTable with layout set to Tabular Form and 'Repeat All Item Labels' enabled

Dashboard Layout

+----------------------+----------------------+----------------------+
|   PivotTable 1       |   PivotTable 2       |   PivotTable 3       |
|   (Compact Layout)    |   (Outline Layout)   |   (Tabular Layout)   |
+----------------------+----------------------+----------------------+ 

Interactivity

All PivotTables share the same data source. Applying a filter on Region or Product Category in any PivotTable updates all three tables to show filtered results. This helps compare how layouts display the same filtered data.

Self Check

If you filter the Region to show only 'East', which PivotTable layouts will update their rows and values? What differences do you notice in how the filtered data is displayed across the three layouts?

Key Result

Compare sales and quantity by Region and Product Category using three PivotTable layouts: Compact, Outline, and Tabular.