Teach on Udemy

Turn what you know into an opportunity and reach millions around the world.

Learn More

Your cart is empty.

Keep shopping

Excel Pivot Tables and Pivot Charts - Complete Guide

Name: Excel Pivot Tables and Pivot Charts - Complete Guide
Rating: 4.6 (2754 reviews)

Turn thousands of rows into the answers your manager asks for. Build PivotTables, charts and dashboards in Excel.

Created byIan Littlejohn

Last updated 9/2026

English

English [Auto],French [Auto],

What you'll learn

Build a PivotTable from a raw data export and turn thousands of rows into a one-page summary report
Set your data up correctly first, so your PivotTable returns the right number - the step most Excel users skip
Group dates into months, quarters and years to show trends, and build trend charts a manager can read at a glance
Filter with slicers that drive several PivotTables from a single click, so one selection updates the whole report
Create percentages, running totals, difference-from and ranking calculations using Show Value As, with no formulas
Use conditional formatting with rules, icons, data bars and color scales to make the numbers say something
Run frequency analysis on Human Resources data to answer questions like how many staff fall into each salary band
Build two complete interactive dashboards from an empty sheet - one for Human Resources, one for Finance
Practice on nine hands-on activities with downloadable data files, each with a full walkthrough of the answer

Course content

10 sections • 69 lectures • 4h 27m total length

Introduction to the Course1:54
Meet your instructor and see what PivotTables do: turn thousands of rows of data into the totals, averages, rankings and trends a business report needs.
About the Course1:23
How to get the best from the course: setting the video quality to full HD, changing the playback speed to suit you, and where to go for Udemy support and your certificate.
Download the course data files0:05
Download the sales data workbook used throughout the course so you can build every PivotTable alongside the lectures.
Correctly Setup Data7:36
Set your source data up the way PivotTables expect: one header row, no blank rows or columns, no subtotals or grand totals inside the data, and the range formatted as an Excel table.
Welcome to Udemy Role Play0:35
What the Udemy role-play is: practice discussing a PivotTable data analysis project with an AI character, and how to get the most from the conversation.
Discuss the data analysis project for the Sales department

Introduction to Pivot Tables0:33
What this section covers: creating your first PivotTable, then building it out with multiple dimensions in rows and columns and multiple measures in values.
Section overview: Creating Pivot Tables1:20
Written overview of the Creating Pivot Tables section, with a Quick Guide PDF to download - the field list, the rows, columns and values areas, and Recommended PivotTables.
Creating Pivot Tables8:50
Create your first PivotTable from Insert, PivotTable: drag fields into rows and values, try Recommended PivotTables, and set the number format.
Working with Multiple Dimensions3:42
Add a second and third dimension to a PivotTable - fields in both rows and columns - to break a total down by more than one category at a time.
Working with Multiple Measures4:14
Put more than one measure in the values area of a PivotTable and format each one, so a single table shows several figures side by side.
Your turn: build pivot tables0:20
Your turn: build PivotTables from the sales data with the dimensions and measures the report needs, and format the numbers.
Walkthrough: the pivot tables exercise5:24
Walkthrough of the Pivot Tables exercise: laying out the fields, adding the second measure and applying the number formats.
Discuss using Pivot Tables in the Sales data analysis project with Ada

Introduction to Methods of Aggregation and Grouping1:10
What this section covers: Sum, Average, Count, Max and Min, combining several aggregations in one PivotTable, grouping data, formatting, and comparison charts.
Section overview: Methods of Aggregation and Grouping1:50
Written overview of the Methods of Aggregation and Grouping section, with a Quick Guide PDF to download - Value Field Settings, grouping, subtotals and grand totals.
Methods of Aggregation4:41
Change how a PivotTable summarizes a field in Value Field Settings - Sum, Average, Count, Max and Min - and set the number format to match.
Combining Methods of Aggregation4:26
Use the same field several times in one PivotTable with a different aggregation each time, so average, maximum, minimum and count sit side by side.
Grouping Data6:04
Group items in a PivotTable row field into your own categories, and ungroup them again when the report changes.
Formatting Pivot Tables7:10
Format a PivotTable for reporting: table styles, the report layout options, and turning subtotals and grand totals on or off.
Comparison Graphs7:46
Build a column PivotChart to compare categories, and see how the chart follows the PivotTable as you change the fields behind it.
Your turn: use the methods of aggregation0:35
Your turn: summarize the sales data with several aggregations at once and chart the comparison.
Walkthrough: the methods of aggregation exercise4:53
Walkthrough of the Methods of Aggregation exercise: setting each measure's aggregation, formatting the numbers and adding the PivotChart.
Discuss using Different Methods of Aggregation with Ada

Introduction to Trend Analysis1:06
What this section covers: grouping dates into years, quarters and months, adding your own date fields to the source data, and building trend charts.
Section overview: Trend and Date Analysis1:26
Written overview of the Trend and Date Analysis section, with a Quick Guide PDF to download - date grouping, custom date fields and refreshing the PivotTable.
Grouping Date Fields for Trend Analysis5:39
Group a date field in a PivotTable into years, quarters, months and days, and ungroup it again to change the level of the trend.
Creating Fields for Trend Analysis5:39
Add your own year, month and day columns to the source data when you need more control than date grouping gives, then refresh the PivotTable to pick them up.
Creating Fields for Trend Analysis Part 26:12
Add month name and weekday columns to the source data so trend reports read January and Monday rather than 1 and 2, then refresh the PivotTable.
Trend Charts8:31
Build a line PivotChart to show the trend over time, and combine it with a column chart so volume and trend sit on one chart.
Your turn: analyze the trend0:24
Your turn: group the order dates, add the date fields you need, and chart the sales trend.
Walkthrough: the trend analysis exercise6:09
Walkthrough of the Trend Analysis exercise: grouping the dates, adding the extra date fields and building the trend chart.
Meeting to discuss Trend Analysis with Ada

Introduction to Filtering and Top 10 Analysis1:27
What this section covers: label and value filters, Top 10 analysis, report filters, and slicers and timelines connected to more than one PivotTable.
Section overview: Filtering and Top 10 Analysis2:00
Written overview of the Filtering and Top 10 Analysis section, with a Quick Guide PDF to download - label and value filters, Top 10, report filters, slicers and timelines.
Filtering Pivot Tables8:03
Filter a PivotTable with label filters and value filters so it shows only the rows that meet your condition.
Top 10 Analysis3:04
Use the Top 10 value filter to show the highest or lowest items - by count, by value, or by percentage of the total.
Report Filters4:22
Add a field to the Filters area to filter the whole PivotTable by something that does not appear in the rows or columns.
Using Slicers to Filter Data8:47
Add a slicer, and a timeline for dates, to filter a PivotTable with buttons instead of dropdowns.
Using Slicers to Filter Multiple Pivot Tables6:08
Use Report Connections to wire one slicer to several PivotTables, so a single click filters the whole report.
Your turn: filter the pivot table0:26
Your turn: filter the sales data with label and value filters and pull out the top 10 by value.
Walkthrough: the filtering exercise5:19
Walkthrough of the Filtering exercise: applying the label and value filters, the Top 10 filter and the percentage view.
Discuss using Filters and Slicers with Ada

Introduction to Analyzing Data and Calculations1:04
What this section covers: percentage of total contribution analysis, pie charts, Difference From and Running Total calculations, and ranking.
Section overview: Analyzing Data and Calculations1:58
Written overview of the Analyzing Data and Calculations section, with a Quick Guide PDF to download - the Show Values As options for percentages, differences, running totals and rank.
Percentage Calculations6:14
Use Show Values As, % of Grand Total to turn a PivotTable of values into a contribution analysis, and format the percentages.
Percentage Calculations - Part 25:43
Go further with Show Values As - % of parent row total and % of subtotal - to see how each item contributes within its own group.
Pie Charts8:07
Build a pie PivotChart to show contribution, and see when a column or line chart tells the story better.
Difference From and Running Total Calculations5:55
Use Show Values As, Difference From and Running Total In to show change against a previous period and a cumulative total, and rank items largest to smallest.
Your turn: calculate percentages0:29
Your turn: show each item as a percentage of the total and add a running total alongside.
Walkthrough: the percentage calculations exercise10:31
Walkthrough of the Percentage Calculations exercise: applying the Show Values As options and formatting the result.
Meeting to discuss more advanced calculations with Ada

Introduction to Frequency Analysis0:52
What this section covers: frequency analysis - grouping a numeric field into bands and counting how many records fall into each, using human resource data.
Section overview: Frequency Analysis1:58
Written overview of the Frequency Analysis section, with a Quick Guide PDF to download - grouping numeric fields into intervals and charting the distribution.
Download the Frequency Analysis data file0:04
Download the EmployeeMaster workbook used in the frequency analysis section so you can build the distribution yourself.
Frequency Analysis7:21
Group a numeric field such as salary or age into bands in a PivotTable and count the employees in each - the PivotTable equivalent of a histogram.
Frequency Analysis and Charts3:11
Chart a frequency distribution as a column PivotChart, show it as a percentage of the total, and filter it with a slicer.
Your turn: build a frequency analysis0:31
Your turn: group the employee data into bands and chart the frequency distribution.
Walkthrough: the frequency analysis exercise5:26
Walkthrough of the Frequency Analysis exercise: setting the grouping intervals, counting the records and building the chart.
Meeting with Ada to discuss frequency analysis

Introduction to Conditional Formatting1:06
What this section covers: conditional formatting inside a PivotTable - highlight rules, Top 10 rules, icon sets, data bars and color scales.
Section overview: Conditional Formatting2:05
Written overview of the Conditional Formatting section, with a Quick Guide PDF to download - highlight rules, top and bottom rules, data bars, color scales and icon sets.
Conditional Formatting using Rules6:03
Apply a conditional formatting rule to a PivotTable value field so figures above or below your threshold highlight themselves as the data changes.
Conditional Formatting using Top 10 Analysis3:47
Use conditional formatting Top 10 rules to highlight the highest and lowest values, including by percentage and against the average.
Conditional Formatting using Icons, Data Bars and Color Scales8:49
Add icon sets, data bars and color scales to a PivotTable, and control whether they apply to the values, the subtotals or the grand total.
Your turn: apply conditional formatting0:23
Your turn: highlight the sales figures with a rule, a Top 10 rule and data bars.
Walkthrough: the conditional formatting exercise4:35
Walkthrough of the Conditional Formatting exercise: setting each rule and choosing which cells it applies to.
Meeting with Ada to discuss conditional formatting in the Sales data project

Introduction to Interactive Dashboards0:38
What this section covers: bringing PivotTables, charts and slicers together into two interactive dashboards - one HR, one finance - with no formulas and no VBA.
Section overview: Interactive Dashboards2:13
Written overview of the Interactive Dashboards section, with a Quick Guide PDF to download - laying PivotTables and charts out on one sheet and wiring slicers to all of them.
Your turn: build the human resource dashboard0:19
Your turn: build a human resource dashboard from the employee data, bringing headcount, salary and distribution views onto one sheet.
Walkthrough: the human resource dashboard exercise, part 1 - the layout9:49
Walkthrough of the Human Resource Dashboard exercise, part 1: building the PivotTables and charts and laying them out on the dashboard sheet.
Walkthrough: the human resource dashboard exercise, part 2 - the interactivity4:38
Walkthrough of the Human Resource Dashboard exercise, part 2: adding the slicers and using Report Connections so one click filters every chart.
Your turn: build the finance dashboard0:31
Your turn: build a finance dashboard from the sales data, with variance and running total views on one sheet.
Walkthrough: the finance dashboard exercise, part 1 - the layout7:31
Walkthrough of the Finance Dashboard exercise, part 1: building the PivotTables, the Difference From calculations and the conditional formatting.
Walkthrough: the finance dashboard exercise, part 2 - the interactivity5:23
Walkthrough of the Finance Dashboard exercise, part 2: adding the slicers and report connections and finishing the layout.

Requirements

Microsoft Excel 2016 or later, or Microsoft 365. Excel 2016 and 2019 reached end of support in October 2025, so Microsoft 365 is recommended
The course is recorded in Excel for Windows. PivotTables work in Excel for Mac, but the ribbon layout differs in places
Excel for the web has a reduced PivotTable feature set. If that is the version you use, take Complete Introduction to Excel Online Pivot Tables in 3 Hrs instead
You should be able to enter data, write a basic formula and build a simple chart in Excel. No PivotTable experience is needed
No Microsoft 365 Copilot license is required. Everything in this course is built with standard Excel features

Description

This course contains the use of artificial intelligence.

Every lesson in this course is written, created and recorded by me. AI is used only to help produce supporting images and written materials around the lessons.

Your manager asks which region is growing, which product is falling behind, and how this quarter compares to last. The data is sitting in a spreadsheet with thousands of rows. Answering by hand takes an afternoon, and the answer is stale by Friday.

A PivotTable answers all three in about a minute, and it refreshes next month with one click.

This course teaches you to do exactly that, in the version of Excel already on your desk. No formulas. No VBA. No add-ins.

WHAT MAKES THIS COURSE DIFFERENT

I teach the analysis, not the tool. Every section opens with a business question a manager actually asks - which products contribute most to revenue, how are sales trending, how many staff sit in each salary band - and then shows you how to answer it. You get the technique and the reason to reach for it.

You will also start where most PivotTable courses do not: setting your data up correctly. This is the single most common reason a PivotTable returns the wrong number, and it is the first thing we cover.

WHAT YOU WILL BUILD

Creating PivotTables - from a raw data export to a summary report, working with multiple dimensions and multiple measures.
Methods of aggregation and grouping - sums, counts and averages, custom groups, and formatting that makes a table readable.
Trend and date analysis - grouping dates into months, quarters and years, and building trend charts that show direction at a glance.
Filtering and Top 10 analysis - report filters, and slicers that drive several PivotTables from one click.
Show Value As calculations - percentages, running totals, difference from and ranking, with no formulas.
Frequency analysis - answering "how many fall into each band" on real Human Resources data.
Conditional formatting - rules, Top 10 highlighting, icons, data bars and color scales.
Two complete interactive dashboards - a Human Resources dashboard and a Finance dashboard, built from an empty sheet.
Nine hands-on practical activities with downloadable data files. Every one has a full video walkthrough, so you try it yourself and then watch the answer.

ABOUT THE TRAINER

I have been training business people to work with data since 2008, and teaching on Udemy since 2013 - sixteen courses, more than 398,000 learners and more than 139,000 reviews at an average rating of 4.6.

I specialize in teaching business users to turn data into information they can act on, using Microsoft Excel, Copilot in Excel, Microsoft Power BI, Looker Studio and Amazon QuickSight.

WHAT STUDENTS ARE SAYING

"Yes, really like Ian's teaching style. Very effective."

"The course was exactly what I needed. Amount of information, pacing, and tutorials were all appropriate to the level."

"The step by step instructions were very helpful and easy to follow along with."

READY TO START?

Download the data file, open Excel, and build your first PivotTable in the next fifteen minutes. I will see you in lesson one.

Who this course is for:

Excel users who are comfortable with basic formulas but have never built a PivotTable, and are tired of summarizing data by hand
Analysts and finance, HR or operations staff who use PivotTables occasionally and have never been shown grouping, Show Value As, slicers or dashboards
Managers and business users who need a monthly report they can refresh in seconds instead of rebuilding it every month
Excel desktop users specifically - if you work mainly in Excel for the web, Complete Introduction to Excel Online Pivot Tables in 3 Hrs covers this ground for that version
Anyone who wants PivotTables on their own - if you want the wider data analysis methodology with Copilot in Excel alongside PivotTables, Introduction to Business Data Analysis with Excel Copilot AI is the deeper course

Excel Pivot Tables and Pivot Charts - Complete Guide

What you'll learn

Explore related topics

Course content

Introduction6 lectures • 12min

Create Pivot Tables8 lectures • 24min

Methods of Aggregation and Grouping10 lectures • 39min

Trend and Date Analysis9 lectures • 35min

Filtering and Top 10 Analysis10 lectures • 40min

Analyzing Data and Calculations9 lectures • 40min

Frequency Analysis8 lectures • 19min

Conditional Formatting8 lectures • 27min

Interactive Dashboards8 lectures • 31min

Course Conclusion2 lectures • 1min

Requirements

Description

Who this course is for: