O'Reilly logo
live online training icon Live Online training

Mastering Microsoft Excel pivot tables

Topic: Business
Dawn Griffiths

Pivot tables are one of Excel’s most powerful components, allowing you to quickly create interactive summaries of your data. Go beyond the basics with expert Dawn Griffiths to gain full mastery of this important technique. You’ll learn how to create a pivot table with secondary rows, filter it using slicers and timelines, include calculated fields and items, and add automatic and manual grouping. Along the way, you’ll pick up 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 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.
  • You have rudimentary pivot tables skills and want to learn more advanced techniques.

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:

About your instructor

  • Dawn Griffiths has 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. She 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. She has a first-class honours degree in mathematics.

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