Free Excel Course Part 5 — Pivot Tables | Slicers, Multi-Sheet Data, & Filter Pages
Welcome to Part 5 of our complete Excel training series! Having covered formatting in Part 1, functions in Part 2, advanced formulas in Part 3, and data visualization in Part 4, this module covers the primary data summarizing tool in Excel: Pivot Tables.
Pivot tables allow you to summarize, group, and analyze large datasets containing thousands of transaction rows in seconds—without writing complex conditional array formulas.
In this guide, we walk through building structured Pivot Tables, adding interactive button Slicers for executive dashboards, linking separate worksheet tables using Excel's native Data Model, and using the Report Filter Pages feature to generate formatted regional reports automatically.
| Course Module | Core Subject | Topics Included |
|---|---|---|
| Part 1 ✅ | Navigation & Formatting | 14 Topics |
| Part 2 ✅ | Intermediate Functions | 7 Topics |
| Part 3 ✅ | Advanced Formulas | 60 Topics |
| Part 4 ✅ | Charts & Graphs | 8 Topics |
| Part 5 — Active Module | Pivot Tables & Data Model | 4 Core Topics |
| Part 6 | Macros & VBA Automation | Advanced Module |
Summarize and group raw data tables without writing complex formulas
Pivot tables are ideal when analyzing extensive transaction logs where you need to aggregate figures by region, sales representative, product line, or date.
In the workbook practice tab, a hardware distribution dataset lists regional sales across multiple product lines. Using a Pivot Table, you can calculate total sales by product and region instantly.
A Pivot Table consists of 4 primary layout areas in the field list:
- Filters (Report Filter): Applies high-level filters to restrict data included across the entire report.
- Rows (Row Label): Displays selected categorical fields vertically down the left column.
- Columns (Column Label): Displays selected categorical fields horizontally across top headers.
- Values: Summarizes numeric fields (using SUM, COUNT, AVERAGE, MIN, or MAX) in the main table grid.
Pivot Table Setup Workflow:
- Select any cell inside the source data range.
- Go to Insert tab → click PivotTable → choose destination worksheet.
- Drag required fields into Rows, Columns, Filters, and Values boxes to build your summary report.
Adding interactive filtering controls for dashboard reporting
Slicers provide interactive visual filtering buttons for Pivot Tables and Pivot Charts. Instead of clicking standard dropdown filter menus, Slicers enable users to switch views across regions, years, or product categories with a single click.
Inserting Slicers Workflow:
- Select any cell inside the active Pivot Table.
- Go to PivotTable Analyze tab → Filter group → click Insert Slicer.
- Check the field boxes you wish to use as visual controls (e.g., Region, Product Category).
- Arrange slicer panels on the sheet. Click buttons to filter data dynamically, or hold Ctrl to select multiple buttons simultaneously.
Building relationships across separate tables using Excel's Data Model
When transaction logs live on one worksheet, customer master records on a second worksheet, and product pricing details on a third, traditional workflows require adding multiple VLOOKUP or XLOOKUP helper columns to consolidate data.
Excel's native Data Model feature allows you to link separate tables directly via key IDs (such as Customer ID or Product ID) to build a unified multi-table Pivot Table without helper columns.
Data Model Relationship Setup:
- Convert each data range into an official Excel Table (Ctrl + T) and assign table names (e.g.,
tblSales,tblProducts). - Insert a Pivot Table from
tblSales, ensuring you check the option: "Add this data to the Data Model". - Go to PivotTable Analyze tab → Relationships → click New.
- Select primary Table (
tblSales) and Related Table (tblProducts), defining the matching foreign key column (e.g., Product ID). - In the PivotTable Fields pane, switch to the All tab. You can now drag fields from both tables into the same Pivot Table layout.
Generating individual worksheet tabs automatically per category item
When you need to distribute individual reports to different department heads or regional managers, manually filtering and copying pivot tables into separate tabs is time-consuming.
Excel's Show Report Filter Pages feature automatically splits a master Pivot Table into individual formatted worksheet tabs for every item contained within a Report Filter field.
Report Filter Pages Setup Workflow:
- Build your base Pivot Table report layout.
- Place the target categorical field (e.g., Department or Region) into the Filters box.
- Go to PivotTable Analyze tab → click the arrow next to Options → select Show Report Filter Pages...
- Select the target filter field → click OK. Excel automatically generates separate worksheet tabs for each category value.
Download Practice Workbook — Free
Download the Part 5 practice workbook containing raw transaction tables, multi-table schema examples, and Pivot Table exercise tabs.
| Workbook Name | File Details | Download Link |
|---|---|---|
Excel Course Part 5-Pivot Tables.xlsx |
4 Pivot Table exercise tabs · Slicers · Multi-table Data Model · Filter Page automation. | ⬇ Download (.xlsx) |
Direct workbook download. No registration or email sign-up required.
⬇ Download Practice Workbook (.xlsx)Connect With Us
Have questions about Pivot Tables or Data Models? Reach out or subscribe below to get notified when Part 6 drops.
