PivotTables Tutorial Series: Learn the Fundamentals Step by Step
- TWX Margerate
- May 15
- 4 min read
Updated: Jun 8
PivotTables are one of the most useful tools in Excel for summarising and analysing data quickly.
But if you are new to PivotTables, they can feel confusing at first. You may wonder where to place fields, why Excel sometimes uses SUM and sometimes COUNT, or how to turn a PivotTable into something useful for real analysis.
That is why I created this PivotTables Tutorial Series.
In this series, I walk through the fundamentals step by step using a simple sales dataset, so you can follow along and practise as you learn.
Download the Practice File
Use the practice file below to follow along with the series.
Inside the file, you’ll find:
Start_Here sheet
Sales_Data sheet used throughout the tutorial
Challenge sheet for the final practice challenge
Download here:
What You’ll Learn in This Series
This series covers the key PivotTable fundamentals you need to start analysing data in Excel more confidently.
You’ll learn how to:
Core PivotTable skills
Create your first PivotTable
Understand the Field List
Place fields into Rows, Columns, Values, and Filters
Analysis skills
Sort and filter results
Use slicers
Group dates and manually group data
Use Show Values As
Presentation and practice
Change layout
Drill down into records
Create PivotCharts
Complete a final challenge
Watch the Full PivotTables Series
I recommend watching the videos in order, especially if you are new to PivotTables.
Watch the full playlist here:
Prefer to jump to a specific topic? You can use the video guide below.
Video 1: Create Your First PivotTable
In the first video, you’ll learn how to create a PivotTable from a sales dataset.
We use a simple business question:
What is the revenue generated in each region?
You’ll learn how to insert a PivotTable, use the Field List, drag fields into Rows and Values, and quickly summarise revenue by region.
Video 2: Change Default Calculations and Number Format
In this video, we look at why PivotTables sometimes use SUM and sometimes use COUNT.
You’ll learn why numeric fields are usually summed, why text fields are usually counted, how to use Value Field Settings, and how to change number formats properly inside a PivotTable.
This is important because accepting the wrong default calculation can lead to the wrong conclusion.
Video 3: Sort, Filter, and Group Data
In this video, you’ll learn how to make PivotTables more useful for analysis.
We cover sorting, filtering, slicers, grouping dates by month, and manually grouping data.
These features help you focus on specific parts of the data and understand patterns more clearly.
Video 4: Go Deeper with PivotTables
Once you know the basics, the next step is learning how to go deeper.
In this video, we cover adding multiple fields, breaking data down by region, category, and product, using Show Values As, changing the PivotTable layout, and drilling down into the source records behind a number.
This helps you move beyond simple summaries and start understanding what is driving the result.
Video 5: Create PivotCharts and Try the Challenge
In this video, you’ll learn how to create PivotCharts from PivotTables.
We cover creating PivotCharts, choosing suitable chart types, using slicers with PivotCharts, and visualising trends and comparisons.
At the end of the video, I introduce the final PivotTable challenge so you can apply what you’ve learned.
Video 6: PivotTable Challenge Solution Walkthrough
The final video is a challenge walkthrough.
The challenge scenario is:
Sales quantity has increased over time, but revenue has not grown as expected.
In the walkthrough, we investigate the issue step by step using PivotTables. We look at monthly trends, region breakdown, category and product performance, discount impact, and final business findings.
The goal is not just to build PivotTables, but to use them to ask better questions and identify what is driving the numbers.
How to Use This Series
Here’s the best way to go through the series:
Download the practice file.
Watch the videos in order.
Follow along using the Sales_Data sheet.
Try the final challenge yourself before watching the walkthrough.
Compare your approach with the solution video.
Don’t worry if your PivotTables do not look exactly the same as mine.
The goal is not to copy every step perfectly. The goal is to understand what question each PivotTable is helping you answer.
Want More PivotTable Practice?
If you have completed the free series and want more hands-on practice, I created a practice pack with 5 real business scenarios:
PivotTables Practice Pack: 5 Real Business Scenarios
Inside the pack, you’ll practise with work-style datasets across:
Sales performance
Monthly expenses
Customer support tickets
Inventory stock movement
Management summary with PivotChart
Each scenario includes:
Excel dataset
Business question
Guided tasks
Solution walkthrough video
The goal is to help you move from:
I know how to create a PivotTable
to:
I can use PivotTables to analyse data and explain the findings.
View the PivotTables Practice Pack:
PivotTables are not just a shortcut for summarising data.
When used properly, they help you explore data, compare different views, investigate patterns, and communicate findings more clearly.
Start with the free practice file, watch the videos in order, and try the challenge at the end.
The more you practise, the more natural PivotTables will feel.
Comments