O'Reilly logo
live online training icon Live Online training

Mastering Microsoft Excel Pivot Tables

Dawn Griffiths

Pivot tables are one of Excel’s most powerful components, allowing you to quickly create interactive summaries of your data. This course will teach you to go beyond the basics, and gain full mastery. You’ll learn how to create a pivot table with secondary rows, how to filter it using slicers and timelines, how to include calculated fields and items, and how to add automatic and manual grouping. Along the way, you’ll learn data validation techniques to keep your data clean and tidy, and discover how the Excel data model lets you use multiple tables of data in a single pivot table.

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

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

  • When and how to use pivot tables.
  • How to add calculated fields and items.
  • How to 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.
  • Provide an easy way of filtering your data using slicers and timelines.
  • Group your data both 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.
  • You have rudimentary pivot tables skills, and want to learn more advanced techniques.

Prerequisites

  • Familiarity with Excel tables.

Recommended preparation:

  • Installation of Excel 2019 or Excel for Office 365. All course demonstrations will use Excel for Office 365. Note that data models are only available in Windows versions of Excel.
  • Read Excel 2019 Bible, Chapter 4 (Working with Excel Ranges and Tables)

Recommended follow-up:

About your instructor

  • Dawn Griffiths has a First-Class Honours degree in Mathematics and over 20 years experience using Excel. She has 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, as a way of teaching 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)

  • Presentation: Introduction to pivot tables
  • Walkthrough: How to create a pivot table with secondary rows
  • Exercise: Create a basic pivot table
  • Q&A

Pivot table calculations (35 minutes)

  • Walkthrough: How to change the calculation using the Value Field Settings
  • Exercise: Create a pivot table that summarises values without using Sum
  • Q&A
  • Break (10 minutes)

Filtering data using slicers and timelines (25 minutes)

  • Presentation: Filtering data
  • Walkthrough: How to add a slicer and a timeline
  • Exercise: Add a slicer and timeline to a pivot table
  • Q&A

Automatic and manual groups (35 minutes)

  • Walkthrough: How to add an automatic group to dates
  • Walkthrough: How to add an automatic group to numeric data
  • Walkthrough: How to add manual groups
  • Exercise: Add automatic groups to a pivot table
  • Q&A
  • Break (10 minutes)

Calculated fields and items (35 minutes)

  • Presentation: What are calculated fields and items?
  • Walkthrough: How to add a calculated field
  • Walkthrough: How to add a calculated item
  • Exercise: Add a calculated field or item to a pivot table
  • Q&A

Data validation (25 minutes)

  • Walkthrough: How to improve your pivot table results by validating the underlying table
  • Exercise: Add data validation to a table
  • Q&A
  • Break (10 minutes)

Data models (30 minutes)

  • Walkthrough: How to base a pivot table on multiple tables of data
  • Exercise: Create a pivot table based on Excel’s data model
  • Q&A