About this course
PivotTables are the most powerful general-purpose analysis tool in Excel — and the one most analysts use shallowly. This course, taught by Excel MVP Ken Puls, treats PivotTables as the front end of a real BI workflow: clean data in, the right aggregation rules, controlled formatting, interactive slicers, and a dashboard layer on top.
The course starts with what makes a “pivot-compliant” data set and how to use Power Query to enforce that shape. It then walks through every key feature of PivotTables — value-field formats, layouts, grouping, sorting, aggregations beyond SUM, calculated fields, slicers, timelines, and pivot charts. The final modules build a complete pivot-based dashboard and cover data refresh and the security implications of distributing pivot files.
What you’ll learn
- Shape source data so PivotTables produce correct, refreshable results
- Connect to data from ranges, Tables, databases, and the Data Model
- Use Power Query to clean, append, unpivot, flatten, and join data
- Switch between SUM, COUNT, AVERAGE, running totals, % of total, and rank aggregations
- Build slicers, timelines, and the Show Details feature
- Add calculated fields and calculated items
- Construct pivot-driven dashboards with conditional formatting
- Refresh, manage, and secure PivotTable files
Who this course is for
Anyone who builds reports in Excel and wants to use PivotTables seriously. Analysts moving toward Power BI who need a deep Power-Query-plus-pivots foundation.
About the instructor
Ken Puls, FCPA, FCMA, is an Excel MVP and co-author of M is for (DATA) MONKEY — the canonical book on Power Query. He runs the Excelguru training site and consultancy, and has taught Power Query and PivotTables to thousands of analysts.
What’s in this course
Get The Course Files
- Download The Files
Intro - The Value of PivotTables
- Business Intelligence Lifecycle
- Why You Care About PivotTables
- Creating Your First PivotTable
- DEMO - Creating Your First PivotTable
Pivot Compliant Data Sets
- Required Data Layout
- Connecting to Your Source Data
- Issues Using Excel Ranges
- Using Excel Tables
- Building PivotTables Against Excel Tables
- Why Excel Tables are Better Than Ranges
- Using Data Direct From Databases
- Sourcing Data Direct From Databases to PivotTables
- Sourcing Data Direct From Databases to a Table
- Sourcing Data Direct From Databases to the Data Model
Controlling PivotTable Look & Feel
- Value Field Formats
- Modifying Column Names & Number Formats
- Report Layouts
- Changing PivotTable Layouts
- Grouping Data
- Grouping Data in PivotTables
- PivotTable Styles
- Creating Custom PivotTable Styles
- Sorting PivotTables
- Sorting Methods in PivotTables
Creating Compliant Data Sets
- Using Power Query to Get and Transform Your Data
- Cleaning Up A Text File With Power Query
- Appending Data Tables
- Simple Append Operations
- Consolidating Flat Files From Folder
- Consolidating A Folder of CSV Files
- Consolidating Excel Data
- Consolidating External Excel Data Files
- Get Data From The Active Workbook
- Consolidating Data From Within The Active Work File
- UnPivoting Data
- UnPivoting Simple Data Sets
- UnPivoting Data With Subheadings
- UnPivoting Subcategorized Data Sets
- Flattening Data Sets
- Flattening Tables With VLookup
- 6 Ways To Join Foundation
- Flattening Tables With Power Query - The Basics
- Join Types
- 6 Ways To Join With Power Query
Changing Aggregations
- Sum Count And Others
- Introduction To Changing Aggregations
- Show Values As
- Running Totals
- Difference And % Difference From
- Difference From X And % Difference From X
- Ranking Items
- Ranking Items in PivotTables
- Showing the Top/Bottom X Records
- Displaying the Top/Bottom X
- Calculated Fields
- Working with Calculated Fields
- Calculated Items
- Working with Calculated Items
Slicing and Filtering
- Filtering with Classic Controls
- Working with PivotTable Filter Fields
- Filtering With Slicers
- Working With Slicers
- Filtering The Timelines
- Working With The Timelines
- The Show Details Feature
- The Show Details Feature - DDEMO
- Keep Your Pivots Looking Nice
- Toggling PivotTable Options
Building Pivot Based Dashboard
- Conditional Formatting on Pivots
- Conditional Formatting DEMO
- Keeping Pivots in Sync
- Linking One Slicer to Multiple Pivots
- Tricks For Extracting Key Info
- Extracting Data From Pivots And Slicers
- Working With Pivot Charts
- Building Pivot Charts
Data Refresh & Security
- Driving PivotTable Refresh
- Refresh Options External DataSets
- Refresh Options Local DataSets
- How Secure Is Your Data?
- DEMO of Exposing Data