Skip to Content
View all events

Mastering Microsoft Excel pivot tables

Published by O'Reilly Media, Inc.

Intermediate content levelIntermediate

Go beyond the basics to gain proficiency of this important skill

PivotTables 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 PivotTable with secondary rows, filter it using slicers and timelines, include calculated fields and items, and add automatic and manual grouping. You’ll pick up data validation techniques to keep your data clean and tidy and use VLOOKUP to add extra columns to tables that you can then include in PivotTables. Finally, you’ll discover how the Excel data model lets you use multiple related tables in a single PivotTable and learn how to use Power Pivot to create more advanced PivotTables.

What you’ll learn and how you can apply it

  • When and how to use PivotTables
  • How to add calculated fields and items, group your data, and filter PivotTables
  • How to create different data aggregations
  • What Excel’s data model is and when to use it
  • How Power Pivot helps you interact with the data model

And you’ll be able to:

  • Have full control over the values displayed in your PivotTables
  • 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
  • Use Power Pivot to create more advanced PivotTables based on the data model

This live event is for you because...

  • You want to quickly create powerful and interactive summaries of your data
  • You have rudimentary PivotTables skills and want to learn more advanced techniques
  • You want to learn how to use Power Pivot

Prerequisites

  • Familiarity with Excel tables
  • A machine with Excel installed, ideally Excel 2019 or later. All course demonstrations will use Excel 365.
  • To take part in the sections on the data model and Power Pivot you will need a Windows version of Excel on Windows with Excel’s Power Pivot tool enabled. You can find instructions for enabling Power Pivot here.

Recommended preparation:

Recommended follow-up:

Schedule

The time frames are only estimates and may vary according to how the class is progressing.

Basic PivotTables (25 minutes)

  • Demos: Introduction to PivotTables; creating a PivotTable with secondary rows
  • Hands-on exercise: Create a basic PivotTable
  • Q&A

PivotTable aggregations (45 minutes)

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

Break (10 minutes)

Filtering data using slicers and timelines (25 minutes)

  • Demos: Filtering data; how to add a slicer and a timeline
  • Hands-on exercise: Add a slicer and timeline to a PivotTable
  • Q&A

Automatic and manual groups (40 minutes)

  • Demos: 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 PivotTable
  • Q&A

Break (10 minutes)

Calculated fields and items (55 minutes)

  • Demos: 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 PivotTable
  • Q&A

Break (15 minutes)

Data validation (25 minutes)

  • Demos: How to improve your PivotTable results by validating the underlying table; how to add extra columns using VLOOKUP
  • Hands-on exercise: Add an extra column to a table
  • Q&A

Data models and Power Pivot (40 minutes)

  • Demos: Introduction to the data model and Power Pivot; adding tables to the data model; defining relationships; creating a PivotTable based on the data modelHands-on exercise: Create a PivotTable based on Excel’s data model
  • Q&A

Break (10 minutes)

Using Power Pivot (45 minutes)

  • Demos: Adding calculations to the data model; using a data table to include financial quarter; using named sets
  • Hands-on exercise: Update the data model using Power Pivot
  • Q&A

Your Instructor

  • Dawn Griffiths

    Dawn Griffiths is an author and trainer with over 20 years of experience using Excel. Her most recent book is Excel Cookbook, and she's also written several books in the Head First series, including Head First Statistics, Head First Android Development, and Head First Kotlin. Dawn also developed the animated video course The Agile Sketchpad with her husband, David, to teach key concepts and techniques in a way that keeps your brain active and engaged.

    Xlinksearch

Skills covered

  • Microsoft Excel
  • PowerPivot