Advanced Excel Pivot Table Techniques

Author: David H. Ringstrom

Learn from Excel expert David Ringstrom, CPA, how to push the boundaries of pivot tables and add even more interactivity to your pivot tables by grouping data in various ways. In this comprehensive course, David explains how to easily extract data from other sources, create simple macros that can resolve the most frustrating aspects of pivot tables, determine the number of duplicates in a list, and much more.

David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the subscription-based Microsoft 365 (formerly Office 365) version of Excel. David draws your attention to any differences in the older versions of Excel (2021, 2019, 2016 and earlier) during the presentation as well as in his detailed handouts. David also provides an Excel workbook that includes most of the examples he uses during the course.

Microsoft 365 is a subscription-based product that provides new feature updates as often as monthly. Conversely, the perpetual licensed versions of Excel have feature sets that don't change. Perpetual licensed versions have year numbers, such as Excel 2021, Excel 2019, and so on.

Publication Date: May 2022

Designed For
Practitioners who would like to expand their knowledge of manipulating and grouping pivot table data.

Topics Covered

• Altering the default sort order within pivot tables to a hierarchy of your choice with Custom Lists
• Determining which refresh commands in Excel update a single pivot table versus all pivot tables in a workbook
• Displaying two or more pivot tables close together on a single worksheet without triggering a conflict
• Filtering data within pivot tables in Excel 2010 and later by way of the Slicer feature
• Filtering two or more pivot tables simultaneously by way of the Slicer feature in Excel 2010 and later
• Preventing pivot tables from automatically resizing columns when you refresh or filter the data
• Resolving situations where data appears more than once within a pivot table
• Summarizing data from Access databases with pivot tables, even if you don't have Microsoft Access installed
• Understanding the conflicts that can arise when you position two or more pivot tables too close in proximity to each other
• Utilizing the Tabular Format command to display pivot table data in two or more columns instead of a single column in Compact Form
• Utilizing the Timeline feature in Excel 2013 and later to filter pivot tables based on date ranges

Learning Objectives

• Recognize and apply the Custom Lists feature to override the default sort order within pivot tables
• Describe how to create pivot tables from information you extract from databases
• Identify pivot table data in new ways by grouping based on dates or custom arrangements that you define
• Recognize which feature allows you to store a user-defined list of items that you can use in any workbook
• Identify which command enables you to connect two or more pivot tables to a Slicer
• Recognize what applies to Linked Pictures
• Recognize which command to use the activate Quick Access Toolbar keyboard shortcut for

Level
Advanced

Instructional Method
Self-Study

NASBA Field of Study
Computer Software & Applications (2 hours)

Program Prerequisites
Intermediate-Level Experience with Excel Pivot Tables.

Advance Preparation
None