Bài 3: PivotTables Mastery

1. What is a PivotTable?

PivotTable là công cụ powerful nhất trong Excel để:

📊 Summarize large datasets trong seconds
🔄 Slice and dice data từ nhiều góc độ
📈 Create dynamic reports không cần formulas
🎯 Answer business questions nhanh chóng

Before vs After PivotTable

Without PivotTable (Manual approach):

excel

1=SUMIFS(Revenue, Region, "North", Product, "Laptop")
2=SUMIFS(Revenue, Region, "North", Product, "Phone")
3=SUMIFS(Revenue, Region, "South", Product, "Laptop")
4... (100+ formulas for each combination)

With PivotTable (2 clicks):

Text

1Drag Region → Rows
2Drag Product → Columns  
3Drag Revenue → Values
4Done! ✅

2. Creating Your First PivotTable

2.1 Source Data Requirements

✅ Good Data Structure:

Text

1A          B         C          D
21  │ Date     │ Region  │ Product  │ Revenue │
32  │ 1/1/2026 │ North   │ Laptop   │ 15000   │
43  │ 1/2/2026 │ South   │ Phone    │ 8000    │
54  │ 1/3/2026 │ North   │ Tablet   │ 6000    │

Requirements:

✅ Headers in first row
✅ No empty rows or columns
✅ No merged cells
✅ Consistent data types per column
✅ Convert to Table first (Ctrl + T)

2.2 Create PivotTable Steps

Select data or click inside data range
Insert → PivotTable (or Alt + N + V)
Choose location: New Worksheet (recommended)
Click OK

Text

1┌─────────────────────────────────────────────────────────────┐
2│                  PivotTable Fields Panel                    │
3├─────────────────────────────────────────────────────────────┤
4│  ☑ Date                                                     │
5│  ☑ Region                                                   │
6│  ☑ Product                                                  │
7│  ☑ Revenue                                                  │
8│  ☑ Quantity                                                 │
9├─────────────────────────────────────────────────────────────┤
10│  Drag fields to areas below:                                │
11│  ┌──────────────┐  ┌──────────────┐                        │
12│  │   Filters    │  │   Columns    │                        │
13│  └──────────────┘  └──────────────┘                        │
14│  ┌──────────────┐  ┌──────────────┐                        │
15│  │    Rows      │  │   Values     │                        │
16│  └──────────────┘  └──────────────┘                        │
17└─────────────────────────────────────────────────────────────┘

2.3 Field Areas Explained

Area	Purpose	Example
Rows	Categories down	Region, Product
Columns	Categories across	Year, Quarter
Values	Numbers to calculate	Sum of Revenue
Filters	Filter entire report	Product = "Laptop"

3. PivotTable Layouts

3.1 Basic Layout: Single Dimension

Revenue by Region:

Text

1Drag: Region → Rows, Revenue → Values
2 
3Result:
4           Sum of Revenue
5North      $45,000
6South      $38,000
7East       $42,000
8West       $35,000
9Grand Total $160,000

3.2 Cross-tabulation: Two Dimensions

Revenue by Region AND Product:

Text

1Drag: Region → Rows, Product → Columns, Revenue → Values
2 
3Result:
4          Laptop    Phone    Tablet   Grand Total
5North     $25,000   $12,000  $8,000   $45,000
6South     $18,000   $15,000  $5,000   $38,000
7East      $20,000   $14,000  $8,000   $42,000
8West      $15,000   $10,000  $10,000  $35,000
9Grand Total $78,000  $51,000  $31,000  $160,000

3.3 Hierarchical Rows

Revenue by Region → Product (nested):

Text

1Drag: Region → Rows, Product → Rows (below Region)
2 
3Result:
4▼ North                    $45,000
5    Laptop                 $25,000
6    Phone                  $12,000
7    Tablet                 $8,000
8▼ South                    $38,000
9    Laptop                 $18,000
10    Phone                  $15,000
11    Tablet                 $5,000
12...

3.4 Report Filter

Add Filter for specific analysis:

Text

1Drag: Year → Filters
2 
3┌─────────────────────────┐
4│ Year  │ (All)      ▼   │
5└─────────────────────────┘
6 
7Click dropdown → Select "2026" → Only 2026 data shown

4. Value Field Settings

4.1 Summarization Types

Right-click value → Value Field Settings

Type	Use Case
Sum	Total revenue, quantity
Count	Number of transactions
Average	Average order value
Max/Min	Highest/lowest value
StdDev	Variability analysis

4.2 Show Values As

Powerful calculations without formulas:

Option	Result
% of Grand Total	Each cell as % of total
% of Column Total	% within each column
% of Row Total	% within each row
% of Parent Row	% of parent category
Running Total	Cumulative sum
Rank	Position (1st, 2nd, etc.)
Difference From	Variance from base

Example: % of Grand Total

Text

1Original:                      As % of Grand Total:
2          Laptop   Phone               Laptop   Phone
3North     $25,000  $12,000     North   15.6%    7.5%
4South     $18,000  $15,000     South   11.3%    9.4%

4.3 Multiple Value Fields

Show Revenue AND Quantity:

Text

1Drag: Revenue → Values, Quantity → Values
2 
3Result:
4          Sum of Revenue    Sum of Quantity
5North     $45,000          150
6South     $38,000          120

5. Grouping Data

5.1 Date Grouping

Group dates by Month, Quarter, Year:

Right-click any date in PivotTable
Select Group...
Choose: Months, Quarters, Years

Text

1Before Grouping:           After Grouping:
21/1/2026   $1,000          ▼ 2026
31/2/2026   $1,500             ▼ Q1
41/3/2026   $800                  Jan   $8,500
5...                              Feb   $12,000
6                                 Mar   $9,800
7                              ▼ Q2
8                                 Apr   $11,000
9                                 ...

5.2 Number Grouping

Group revenue into ranges:

Right-click any number
Group by: Starting at 0, Ending at 10000, By 1000

Text

1Revenue Range    Count
20 - 1000         45
31001 - 2000      38
42001 - 3000      22
5...

5.3 Custom Grouping

Group products into categories:

Select items to group (Ctrl + Click)
Right-click → Group
Name the group

Text

1▼ Electronics
2    Laptop
3    Phone
4    Tablet
5▼ Furniture
6    Desk
7    Chair

6. Filtering & Slicers

6.1 Field Filters

Click dropdown arrow on Row/Column labels:

Text

1┌─────────────────────────────┐
2│ ☑ Select All               │
3│ ☑ Laptop                   │
4│ ☐ Phone    ← Uncheck       │
5│ ☑ Tablet                   │
6│ ─────────────────────────  │
7│ Label Filters  ▸           │
8│ Value Filters  ▸           │
9└─────────────────────────────┘

Value Filters:

Top 10 items
Greater than $10,000
Between $5,000 and$ 15,000

6.2 Slicers (Visual Filters)

Insert → Slicer (or Alt + N + SF)

Text

1┌─────────────────┐  ┌─────────────────┐
2│    Region       │  │    Product      │
3├─────────────────┤  ├─────────────────┤
4│ [North] [South] │  │ [Laptop]        │
5│ [East]  [West]  │  │ [Phone] [Tablet]│
6└─────────────────┘  └─────────────────┘
7 
8Click "North" → PivotTable shows only North data
9Ctrl + Click → Multiple selections

Slicer Benefits:

✅ Visual, intuitive filtering
✅ Connect to multiple PivotTables
✅ Great for dashboards
✅ Users can filter without training

6.3 Timeline Slicer

For date filtering (Insert → Timeline):

Text

1┌────────────────────────────────────────────────────────────┐
2│  2026                                                      │
3│  [Jan] [Feb] [Mar] [Apr] [May] [Jun] [Jul] [Aug] ...      │
4│  ←─────────────[=======]─────────────→                    │
5│              Feb - Apr selected                            │
6└────────────────────────────────────────────────────────────┘

7. PivotCharts

7.1 Create PivotChart

Click inside PivotTable
Insert → PivotChart (or Alt + N + SZ + C)
Choose chart type

Text

1PivotTable ←→ PivotChart
2   Linked: Change one, other updates automatically

7.2 Best Chart Types

Data Type	Recommended Chart
Comparison	Column, Bar
Trend over time	Line
Part of whole	Pie, Donut
Distribution	Histogram
Relationship	Scatter

7.3 PivotChart Features

Interactive Elements:

Text

1┌─────────────────────────────────────────────────────┐
2│  [Region ▼] [Product ▼]  ← Field buttons (filter)  │
3│                                                     │
4│    ████                                            │
5│    ████  ███                                       │
6│    ████  ███  ██                                   │
7│   ──────────────                                   │
8│   North South East                                  │
9└─────────────────────────────────────────────────────┘

Tips:

Right-click chart → Hide Field Buttons (cleaner look)
Connect multiple charts to same slicer
Format chart separately from PivotTable

8. Advanced PivotTable Techniques

8.1 Calculated Fields

Create new calculations inside PivotTable:

PivotTable Analyze → Fields, Items & Sets → Calculated Field

Text

1Name: Profit_Margin
2Formula: =Profit/Revenue
3 
4Result: New "Profit Margin" field appears in field list

Common Calculated Fields:

excel

1Profit = Revenue - Cost
2Margin = Profit / Revenue
3Average_Price = Revenue / Quantity
4YoY_Growth = (This_Year - Last_Year) / Last_Year

8.2 GetPivotData Function

Reference PivotTable values in formulas:

excel

1=GETPIVOTDATA("Revenue", $A$3, "Region", "North", "Product", "Laptop")

Or simply click on PivotTable cell - Excel auto-creates GETPIVOTDATA

8.3 Refresh & Data Source

Refresh PivotTable:

Manual: Right-click → Refresh (or Alt + F5)
All PivotTables: Data → Refresh All

Change Data Source:

PivotTable Analyze → Change Data Source
Useful when data range expands

8.4 PivotTable Options

Right-click → PivotTable Options:

Text

1Display:
2  ☑ Show expand/collapse buttons
3  ☑ Display field captions
4  
5Layout:
6  ☐ Merge and center cells with labels
7  ☑ Preserve cell formatting on update
8  
9Totals:
10  ☑ Show grand totals for rows
11  ☑ Show grand totals for columns

9. Dashboard Building

9.1 Dashboard Structure

Text

1┌─────────────────────────────────────────────────────────────┐
2│                    SALES DASHBOARD 2026                     │
3├──────────────────────┬──────────────────────────────────────┤
4│     SLICERS          │         KEY METRICS                  │
5│  ┌────────────────┐  │  ┌─────────┐ ┌─────────┐ ┌────────┐ │
6│  │    Region      │  │  │ Revenue │ │ Orders  │ │ AOV    │ │
7│  │[N][S][E][W]    │  │  │ $160K   │ │ 1,234   │ │ $130   │ │
8│  └────────────────┘  │  └─────────┘ └─────────┘ └────────┘ │
9│  ┌────────────────┐  ├──────────────────────────────────────┤
10│  │    Product     │  │         REVENUE BY REGION            │
11│  │[Laptop]        │  │    ████████████████████  North       │
12│  │[Phone][Tablet] │  │    ███████████████      South       │
13│  └────────────────┘  │    ██████████           East        │
14│  ┌────────────────┐  │    ████████             West        │
15│  │   Timeline     │  ├──────────────────────────────────────┤
16│  │ Jan-Mar 2026   │  │         TREND OVER TIME              │
17│  └────────────────┘  │    📈 Line chart                     │
18└──────────────────────┴──────────────────────────────────────┘

9.2 Connect Slicers to Multiple PivotTables

Right-click Slicer
Report Connections
Check all PivotTables to connect

Text

1One slicer click → All charts update simultaneously

9.3 KPI Cards with Formulas

Get values from PivotTable:

excel

1# Total Revenue
2=GETPIVOTDATA("Revenue", PivotTable1)
3 
4# vs Previous Period
5=GETPIVOTDATA("Revenue", PT!$A$3) - GETPIVOTDATA("Revenue", PT_LastYear!$A$3)
6 
7# % Change
8=(Current - Previous) / Previous

10. Hands-on Project: Sales Dashboard

10.1 Sample Data

Download hoặc tạo data với 500+ rows:

OrderID	Date	Region	Product	Category	Quantity	Revenue	Cost
001	1/5/2026	North	Laptop	Electronics	2	3000	2000
...	...	...	...	...	...	...	...

10.2 Create Analysis

PivotTable 1: Revenue by Region

Rows: Region
Values: Sum of Revenue

PivotTable 2: Revenue by Product

Rows: Product
Values: Sum of Revenue

PivotTable 3: Monthly Trend

Rows: Date (grouped by Month)
Values: Sum of Revenue

PivotTable 4: Top 10 Products

Rows: Product
Values: Sum of Revenue
Filter: Top 10 by Revenue

10.3 Build Dashboard

Create new sheet "Dashboard"
Copy PivotCharts to Dashboard
Add 3 Slicers: Region, Category, Timeline
Connect all slicers to all PivotTables
Add KPI cards with GETPIVOTDATA
Format and align for presentation

10.4 Expected Output

Text

1Deliverables:
2✅ 4 PivotTables with different views
3✅ 4 PivotCharts (Column, Pie, Line, Bar)
4✅ 3 Interactive Slicers
5✅ KPI summary cards
6✅ Professional formatting

11. Bài tập về nhà

Bài 1: Basic PivotTable

Tạo PivotTable từ sales data:

Revenue by Region (rows)
Revenue by Product (columns)
Add % of Grand Total
Create matching PivotChart

Bài 2: Date Analysis

Group dates và analyze:

Monthly revenue trend
Quarterly comparison
Year-over-Year growth (if multi-year data)

Bài 3: Complete Dashboard

Build professional dashboard với:

4+ PivotCharts
Connected Slicers
Timeline filter
KPI cards
Conditional formatting

Summary

Trong bài này bạn đã học:

✅ Create và configure PivotTables
✅ Layout options (Rows, Columns, Filters, Values)
✅ Value Field Settings (%, Running Total, Rank)
✅ Grouping (Dates, Numbers, Custom)
✅ Slicers và Timeline filters
✅ PivotCharts và Dashboard building
✅ Calculated Fields và GETPIVOTDATA

Next: Bài 4 - Data Visualization - Charts, Conditional Formatting, Sparklines