Live Online training

# Mastering Microsoft Excel pivot tables

## What you'll learn-and how you can apply it

By the end of this live online course, you’ll understand:

• When and how to use pivot tables
• How to add calculated fields and items, group your data, and create frequency distributions
• How to use a data model

And you’ll be able to:

• Have full control over the values displayed in your pivot tables
• Filter your data easily using slicers and timelines
• Group your data automatically and manually
• Create calculated fields and items.
• Add tables to Excel’s data model and manage the relationships between them

## This training course is for you because...

• You want to quickly create powerful and interactive summaries of your data.

Prerequisites

• Familiarity with Excel tables
• A machine with Excel 2019 or Excel for Office 365 installed (All course demonstrations will use Excel for Office 365. Note that data models are only available in Windows versions of Excel.)

Recommended preparation:

Recommended follow-up:

• Dawn Griffiths has a first-class honours degree in mathematics and over 20 years’ experience using Excel. She’s written several books in the Head First series, including Head First Statistics, Head First 2D Geometry, Head First Android Development, and Head First Kotlin. Dawn also developed the animated video course The Agile Sketchpad with her husband, David, which teaches key concepts and techniques in a way that keeps your brain active and engaged.

## Schedule

The timeframes are only estimates and may vary according to how the class is progressing

Basic pivot tables (25 minutes)

• Lecture: Introduction to pivot tables; creating a pivot table with secondary rows
• Hands-on exercise: Create a basic pivot table
• Q&A

Pivot table calculations (35 minutes)

• Lecture: How to change the calculation using the value field settings
• Hands-on exercise: Create a pivot table that summarizes values without using SUM
• Q&A

Break (10 minutes)

• Filtering data using slicers and timelines (25 minutes)
• Lecture: Filtering data; how to add a slicer and a timeline
• Hands-on exercise: Add a slicer and timeline to a pivot table
• Q&A

Automatic and manual groups (35 minutes)

• Lecture: How to add an automatic group to dates; how to add an automatic group to numeric data; how to add manual groups
• Hands-on exercise: Add automatic groups to a pivot table
• Q&A

Break (10 minutes)

Calculated fields and items (35 minutes)

• Lecture: Calculated fields and items; how to add a calculated field; how to add a calculated item
• Hands-on exercise: Add a calculated field or item to a pivot table
• Q&A

Data validation (25 minutes)

• Lecture: How to improve your pivot table results by validating the underlying table
• Hands-on exercise: Add data validation to a table
• Q&A

Break (10 minutes)

Data models (30 minutes)

• Lecture: How to base a pivot table on multiple tables of data
• Hands-on exercise: Create a pivot table based on Excel’s data model
• Q&A