Home › Courses › Pivot Tables Masterclass
🆓 Free Course

Pivot Tables Masterclass

Slice, dice, and summarise any dataset in minutes. Master Pivot Tables, Pivot Charts, Slicers, Timelines, and Calculated Fields — the most powerful data tool in Excel.

📚 6 Modules
🎯 24 Lessons
⏱️ ~7 Hours
📊 Intermediate Level
🔑 Prerequisite: Excel Basics
Start Learning Free  → Take Beginner Course First
🔄
FREE — No signup needed
  • 24 structured lessons
  • 6 practical modules
  • Real-world dataset practice
  • Practice tasks per lesson
  • Pivot Charts and Slicers
  • Calculated Fields
  • Final project — Sales Dashboard
  • Lifetime free access
Start Course Now →

What You Will Learn

By the end of this course, you will turn raw data into powerful reports in minutes.

Create a Pivot Table from any dataset in under 60 seconds
Group dates by Month, Quarter, and Year automatically
Use Value Field Settings — SUM, COUNT, AVERAGE, % of Total
Show data as Running Total, % of Grand Total, and Rank
Add Slicers and Timelines for interactive one-click filtering
Build Pivot Charts that update automatically with slicers
Create Calculated Fields — Profit Margin, Commission, Growth %
Connect one Slicer to multiple Pivot Tables simultaneously
Use GETPIVOTDATA to build KPI cards in a dashboard
Combine data from multiple tables using the Data Model
Build a complete interactive Sales Dashboard
Apply best practices to keep Pivot Table files fast and clean

Course Modules

Click any module to expand. Click any lesson for full details.

Your Progress 0 / 0 lessons
0% Complete
1
Introduction to Pivot Tables
4 Lessons · Foundation
▼
▶
What is a Pivot Table and Why Use It?
Power of Pivot Tables, real-world use cases, Pivot Tables vs formulas.
▶
Preparing Your Data for a Pivot Table
5 rules of clean source data, converting to Table, fixing common data problems.
▶
Creating Your First Pivot Table
Insert Pivot Table, the 4 areas — Rows, Columns, Values, Filters — build your first report.
▶
The Pivot Table Field List Explained
Drag and drop fields, multiple fields in one area, nested rows, rearranging layout.
2
Sorting, Filtering & Grouping
4 Lessons · Data Control
▼
▶
Sorting Pivot Table Data
Sort by values, A-Z, custom sort order, and manual drag sorting.
▶
Filtering — Label, Value & Report Filters
Label filters, value filters, Top 10 filter, page-level report filter.
▶
Grouping Dates — Month, Quarter, Year
Automatically group daily dates into time periods with one right-click.
▶
Grouping Numbers into Ranges
Group numeric data into age bands, salary ranges, score brackets.
3
Value Field Settings & Show Values As
4 Lessons · Analysis Power
▼
▶
Value Field Settings — SUM, COUNT, AVERAGE, MAX
All 11 summary functions, multiple value functions, number formatting that persists.
▶
Show Values As — % of Total
% of Grand Total, % of Row Total, % of Column Total, % of Parent Total.
▶
Running Total, Rank & Difference From
Cumulative running totals, rank items 1–N, month-over-month difference.
▶
Calculated Fields & Calculated Items
Add custom formulas inside the Pivot Table — Profit Margin, Commission, Growth %.
4
Slicers, Timelines & Design
4 Lessons · Interactive Reports
▼
▶
Slicers — Visual Interactive Filters
Insert slicers, single/multi-select, formatting and styling slicer panels.
▶
Timelines — Filter by Date Visually
Insert Timeline, filter by Day/Month/Quarter/Year with sliding selector.
▶
Connecting Slicers to Multiple Pivot Tables
Report Connections — one slicer controls all Pivot Tables simultaneously.
▶
Pivot Table Design, Layout & Formatting
Compact vs Outline vs Tabular, styles, subtotals, grand totals, repeat labels.
5
Pivot Charts
4 Lessons · Visualisation
▼
▶
Creating a Pivot Chart from a Pivot Table
Insert chart from Pivot Table, the Pivot Chart–Table link, move to own sheet.
▶
Formatting Pivot Charts — Title, Colours, Labels
Titles, data labels, series colours, remove field buttons, clean professional look.
▶
Combo Charts — Bar + Line Together
Actual as bars, Target as line, secondary axis for different value scales.
▶
Dynamic Dashboard with Pivot Chart + Slicer
One slicer filters both Pivot Chart and Pivot Table in one click.
6
Advanced Pivot Table Techniques
4 Lessons · Pro Level
▼
▶
GETPIVOTDATA — Extract Values with Formulas
Pull specific values from a Pivot Table into KPI cards and dashboard cells.
▶
Refreshing Pivot Tables & Changing Data Source
Manual refresh, auto-refresh on open, change source range when data grows.
▶
Pivot Table from Multiple Tables — Data Model
Combine 2 separate tables into one Pivot Table using relationships.
▶
Best Practices & Performance Tips
File size, Pivot Cache, when to use Pivot Tables vs SUMIFS, top best practices.

🏆 Bonus: Quick Reference Shortcuts

Essential Pivot Table actions at a glance.

Insert Pivot TableAlt + N + V
Refresh Pivot TableAlt + F5
Refresh ALL Pivot TablesCtrl + Alt + F5
Group selected itemsAlt + Shift + →
Ungroup selected itemsAlt + Shift + ←
Show / Hide Field ListAlt + JT + L
Insert SlicerAlt + JT + SF
Insert TimelineAlt + JT + SI
Insert Pivot ChartAlt + JT + C
Value Field SettingsDouble-click value header
Select entire Pivot TableCtrl + A
Clear all filtersAlt + JT + E

⚠️ Bonus: Common Pivot Table Mistakes

Avoid these mistakes that trip up even experienced Excel users.

1

Source data has blank rows or merged cells

A single blank row splits your dataset. Merged cells break grouping. Remove all blank rows and unmerge all cells before creating a Pivot Table.

2

Not converting source data to an Excel Table

Plain ranges do not auto-expand when you add new rows. Convert to Table (Ctrl+T) — new rows are included automatically on Refresh without changing the source range.

3

Forgetting to Refresh after data changes

Pivot Tables do NOT update automatically. Right-click → Refresh after every data change. Or set: PivotTable Options → Data → Refresh when opening file.

4

Dates not grouping — stored as text

Dates only group if they are real Excel dates. If grouping is greyed out, dates are text. Fix with: Data → Text to Columns → Date format.

5

Numbers showing Count instead of Sum

If your number column contains even one text value, Excel defaults to COUNT. Fix: Value Field Settings → Summarise by → Sum. Then fix the text value in source data.

6

Formatting cells directly instead of via Value Field Settings

Direct cell formatting (Ctrl+1) resets every time you Refresh. Always apply number formatting through Value Field Settings → Number Format — it persists.

7

Using Calculated Fields for % of Total

Calculated Fields calculate per-cell, not against the grand total. Use Show Values As → % of Grand Total instead — gives correct percentages.

8

Not naming Pivot Tables before using GETPIVOTDATA

Default name 'PivotTable1' changes when you recreate the table. Name them first: PivotTable Analyze → PivotTable Name → type meaningful name.

🎓 Final Project

Build a complete interactive dashboard using only Pivot Tables.

Interactive Sales Performance Dashboard

Build a complete, interactive Sales Dashboard from a 500-row raw dataset using Pivot Tables, Pivot Charts, and Slicers — no SUMIFS or helper columns allowed.

Your dashboard must include:

  • Source data converted to Excel Table named SalesData
  • Pivot Table 1 — Total Sales by Region with % of Grand Total column
  • Pivot Table 2 — Monthly Sales Trend with dates grouped by Month and Year
  • Pivot Table 3 — Top 10 Products by Sales using Value Filter
  • Pivot Table 4 — Sales Rep performance with Running Total column
  • Calculated Field — Profit Margin % = Profit / Revenue
  • Pivot Chart 1 — Column chart from PT1 showing regional breakdown
  • Pivot Chart 2 — Line chart from PT2 showing monthly trend
  • Combo chart — Actual Sales bars + Target line on secondary axis
  • Region Slicer connected to ALL 4 Pivot Tables simultaneously
  • Year Timeline connected to monthly trend Pivot Table
  • 3 KPI cards using GETPIVOTDATA — Total Sales, Total Deals, Top Region
  • Clean dashboard sheet — gridlines hidden, professional layout

Completion criteria: One click on the Region Slicer must update all 4 tables and all charts instantly. Dashboard must be presentable to a manager without any explanation.

Submit Your Project  →

Lesson Title