Microsoft Excel: Mastering Pivot Tables
Microsoft Excel Training Series | Level 3
Course Details
Duration: 1 day (9 am – 4 pm)
Microsoft® Excel® Versions: 2016, 2019, 2021 or 365 (Windows)
Delivery Methods (Instructor-led): In-person (Live classroom) | Online in virtual classroom
Course Dates: June 2, 2025 (Online in virtual classroom or In-person) | View schedule 
Course Fee: $295 CAD per person + HST (Virtual classroom), $325 CAD per person + HST (Bring your own device for in-person courses) or $345 CAD per person + HST (Avantix Learning provides device for in-person courses)*
Timing: Public scheduled courses run from 9:00 am to 4:00 pm (Eastern Time).
Virtual classroom courses: Our instructor-led virtual classroom courses are delivered in a virtual classroom environment. Students will be sent a virtual classroom invitation prior to the course.
In-person classroom courses: Our instructor-led, live classroom (in-person) courses are held in downtown Toronto at 18 King Street East, Suite 1400, Toronto, Ontario, Canada. Some courses are also held at an alternate downtown Toronto location.
Custom training: This course may be delivered at your site or ours as an instructor-led virtual classroom or in-person solution. Contact us at info@avantixlearning.ca for more information including savings for custom group training.
Some public courses and / or formats and may be subject to a minimum enrollment requirement.
Course Overview
Learn to master pivot tables in this in-depth, instructor-led course designed for users who want to elevate their Excel skills. Starting with a quick refresher on pivot table basics, this course dives into advanced techniques such as cleaning data sets, using dynamic source data, and importing data from various sources. Students will learn to manipulate pivot table fields, customize layouts, create calculated fields and items, and utilize advanced sorting, dynamic filtering, conditional formatting, and custom formatting to transform data into actionable insights. Additionally, students will import data using advanced Excel tools and generate pivot tables from the imported data, culminating in the creation of impactful dashboards with pivot tables and pivot charts. This course is designed for students with a basic understanding of pivot tables who want to learn more advanced strategies and tools and deliver powerful data-driven insights. This hands-on course provides ample opportunities for students to practice their skills, ask questions, and interact with the instructor. Each student will receive a comprehensive course manual with tips, tricks, shortcuts, as well as sample and exercise files.
Prerequisite: Microsoft Excel: Intermediate / Advanced or comparable knowledge and skills
Location and timing: Public scheduled courses are held online in virtual classroom format or in downtown Toronto and run from 9:00 am to 4:00 pm (Eastern Time).
Related training: View all Microsoft Excel courses >
INCLUDED IN THIS COURSE
- Comprehensive course manual
- Keyboard shortcuts quick reference
- Sample and exercise files (new samples added frequently)
- Refreshments (for classes conducted in Avantix Learning classrooms)
- Certificate of completion
- Follow-up email support
Course Topics
Preparing Data for Analysis
- Excel source data setup basics
- Common issues in source data
- Strategies for cleaning data
- Converting text to dates
- Combining and splitting text data
- Removing extra spaces
Creating Pivot Tables
- Creating a pivot table from a range
- Placing fields in a pivot table
- Rearranging fields in a pivot table
- Formatting numbers in value fields
Changing Report Layouts
- Changing report layouts
- Adding and removing subtotals
- Adding and removing grand totals
Formatting Pivot Tables
- Applying number formatting to value fields
- Changing the font theme and color theme
- Applying pivot table styles
- Creating a custom pivot table style
Refreshing Pivot Tables and Changing the Data Source
- Adding records to the source data
- Updating or refreshing pivot tables
- Changing the data source
Creating Summary Calculations using Aggregate Functions
- Summarizing value fields using aggregate functions
- Changing value field labels
Filtering Pivot Tables
- Filtering pivot tables using filter menus
- Using custom filters for advanced filtering
- Filtering pivot tables using slicers
- Formatting slicers
Sorting Pivot Tables
- Sorting pivot tables by fields
- Using advanced sorting techniques
Grouping Data
- Understanding default date grouping and ungrouping
- Summarizing pivot table data by groups (number or date)
- Alternatives to grouping by a date field
Creating Calculations for Rank, Percent of Total and Running Totals
- Calculating percent totals
- Calculating rank
- Calculating running totals
- Using other Values As calculations
Creating Other Types of Calculations in Pivot Tables
- Creating and editing calculated fields
- Creating calculated items
- Understanding solve order and listing formulas
- Using GETPIVOT DATA
Applying Advanced Formatting
- Highlighting cells in pivot tables using built-in conditional formatting
- Applying conditional formatting that updates when a pivot table is refreshed
- Applying and editing graphical conditional formats
- Creating custom rules for conditional formatting
- Creating and applying custom number formats
Using Dynamic Data Sources for Pivot Tables
- Using Tables as dynamic data sources
- Creating dynamic data sources using functions such as OFFSET or INDEX
- Using dynamic arrays as data sources
- Using queries as dynamic data sources
Creating Pivot Tables from Multiple Worksheets and Workbooks
- Creating pivot tables from related data on different sheets using the Data Model
- Creating pivot tables from multiple worksheets using Power Query
- Creating pivot tables from multiple external workbooks in a folder
Creating Pivot Charts
- Creating a pivot chart
- Changing the chart type
- Formatting pivot charts
- Removing filter buttons
- Refreshing or updating pivot charts
- Creating a map chart using pivot data
- Creating a dynamic chart title
- Adding sparklines
Creating and Formatting Dashboards
- Creating dashboards that include multiple pivot tables and pivot charts
- Setting dashboard components to consistent sizes
- Connecting slicers to multiple pivot tables and pivot charts
- Alternatives to pivot tables to create dashboards
- Tips for formatting dashboards
*Prices subject to change.
Our instructor-led courses are delivered in virtual classroom format or at our downtown Toronto location at 18 King Street East, Suite 1400, Toronto, Ontario, Canada (some in-person classroom courses may also be delivered at an alternate downtown Toronto location). Contact us at info@avantixlearning.ca if you'd like to arrange custom instructor-led virtual classroom or onsite training on a date that's convenient for you.
Copyright 2025 Avantix® Learning
Custom training (Onsite or Online in Virtual Classroom)
Register now for a public course or contact us at info@avantixlearning.ca for more information about any of our courses or to discuss custom training options (virtual classroom or in-person in live classroom).
FIND THE BEST EXCEL COURSE FOR YOUR NEEDS >
Related courses
Microsoft Excel: Introduction to Power Query to Get and Transform Data
Microsoft Excel: Introduction to Power Pivot and Data Models
Microsoft Excel: Data Analysis with Functions, Dashboards and What-If Analysis Tools
Microsoft Excel: Data Visualization
Microsoft Excel: Introduction to Visual Basic for Applications (VBA) Macros
You may like
What is Power Query in Excel?
Power Query in Excel is a powerful data transformation tool that allows you to import data from many different sources and then extract, clean, and transform the data. You will then be able to load the data into Excel or Power BI and perform further data analysis. With Power Query (also known as Get & Transform), you can set up a query once and then refresh it when new data is added. Power Query can import and clean millions of rows of data.
How to Stop or Control Green Error Checking Markers in Excel
In Microsoft Excel, errors are flagged with small green marker or triangle in the upper left corner of the cell. However, these indicators display when there may be an error but is, in fact, not an error.
Excel Shortcuts to Zoom In and Out in Your Worksheets (4 Shortcuts)
There are several mouse and keyboard shortcuts you can use to zoom in and out in Excel worksheets. Some of these shortcuts are built-in and others can be created by customizing Excel Options.
How to Use Flash Fill in Excel (4 Ways with Shortcuts)
You can use Flash Fill in Excel to extract, combine, clean or format data quickly without using formulas. In order to use Flash Fill, Excel must be able to understand a pattern in a column to the left of the column where you want to fill the data so the source data should be entered in a consistent way. You can use Flash Fill by clicking a button, using shortcuts or by using the Fill handle. You run Flash Fill by entering an example of the data you want to fill using a "by example" logic.
How to Convert Text to Numbers in Excel (5 Ways)
There are several ways to convert text to numbers in Microsoft Excel. When data is downloaded from other programs, numbers may sometimes be stored as text and you can't perform calculations correctly on the values. Also, some users may enter a number as text by typing an apostrophe (') before the number. Numbers stored as text usually appear with a green error checking marker in the cell. You can't sum or perform many other types of calculations with text. If you're working with large data sets, it can take a long time to convert specific cells to numbers. In this article, we'll use 5 different ways to quickly convert numbers stored as text to numbers.
How to Insert Multiple Rows in Excel (4 Fast Ways with Shortcuts)
You can quickly insert multiple rows in Excel using a drop-down menu, keyboard shortcuts or the Home tab in the Ribbon. The key to inserting multiple rows at once, rather than one by one, is to select the number of rows you want to insert first and then insert rows. Excel will insert the same number of rows you selected.
3 Excel Strikethrough Shortcuts to Cross Out Text or Values in Cells
You can apply strikethrough to text or values in Excel to cross out or create a line through a cell or part of a cell. There are 3 common ways to apply strikethrough in your Excel worksheets – using a built-in keyboard shortcut, using the Font dialog box, or by adding a command to the Quick Access Toolbar.
How to Replace Blank Cells in Excel with Zeros (0), Dashes (-) or Other Values
You can quickly fill or replace blank cells in Excel with zeros, dashes or other number or text values (0, -, N/A, Null or other text). It's useful to fill blank cells with a number, symbol or value if you want to use the data set as the source for a pivot table or use other data analysis tools.
You may also like
How to Make Text Appear on Click in PowerPoint
You can make text appear on click in PowerPoint using entrance animations. The most common entrance animations are appear, fade, wipe and fly in. During a slide show, presenters can make text appear by line, bullet point, paragraph, word or character. In this article, we will focus on using animations to make text appear on click so you will need to run a slide show and then click anywhere on the slide to make the text appear. You can also press the right arrow or spacebar to make text appear during a slide show. Animations are typically added to placeholders or text boxes in Normal View but can be applied in Slide Master View.
How to Flip an Image in PowerPoint (2 Easy Ways)
You can easily flip an image in PowerPoint using the Ribbon or by dragging a sizing handle. Images can be flipped horizontally or vertically on a slide.
How to Recolor a Picture in PowerPoint
You can recolor a picture in PowerPoint in several ways. The recolor options in the Ribbon and in the Format Picture task pane allow you to recolor a picture but, depending on your version of PowerPoint, the colors available may only work with the current color theme and may also blend colors from the original picture which is problematic. An alternative is to create a shape, fill it with a color, change its transparency and overlay the shape on top of the picture. You would typically make the image grayscale first if you use this strategy.
Microsoft, the Microsoft logo, Microsoft Office and related Microsoft applications and logos are registered trademarks of Microsoft Corporation in Canada, US and other countries. All other trademarks are the property of the registered owners.
Avantix Learning |18 King Street East, Suite 1400, Toronto, Ontario, Canada M5C 1C4 | Contact us at info@avantixlearning.ca