Intermediate PivotTable Techniques 

In this presentation, author and Excel expert David H. Ringstrom, CPA, will enable you to level up your PivotTable skills. Automate data transformations and create self-updating PivotTables by way of Power Query. David will also delve into techniques like reconstructing PivotTable source data, preventing unwanted drill-down actions, and safeguarding your PivotTable-based data. David will contrast different ways to perform calculations inside and alongside PivotTables and overcome pesky nuances such as the GETPIVOTDATA worksheet function.

David is the author of “Microsoft Excel 365 for Dummies”, “Exploring Microsoft Excel’s Hidden Treasures”, and has written or co-authored six other books. He demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the subscription-based Excel for Microsoft 365. David draws your attention to any differences in Excel 2021, 2019 or 2016 during the presentation and in his detailed handouts. The handouts include an Excel workbook with most of the examples he uses during his demonstrations.

Excel for Microsoft 365 is a subscription-based product that receives periodic feature updates. Conversely, perpetually licensed versions have year numbers in their names and do not receive any feature updates.

Who Should Attend
Professionals seeking to use Microsoft Excel more effectively.

Topics Covered

• Understanding the nuance of crafting formulas that reference data within PivotTables
• Managing information overload by creating a Top 10 PivotTable
• Filtering data within PivotTables by way of the Slicer feature
• Using Go To Special to select specific types of cells within a worksheet
• Determining which refresh commands in Excel update a single PivotTable versus all PivotTables in a workbook
• Drilling down into the details behind any amount within a PivotTable with just a double-click
• Removing the Table feature from Excel spreadsheets once it’s no longer needed or simply erasing the alternate row shading
• Preventing PivotTables, from automatically resizing columns when you refresh or filter the data
• Filling all blank cells within a list of data at once by way of the Ctrl-Enter keyboard shortcut
• Resetting all filters at once within a PivotTables, by way of the Clear Filters command
• Understanding the data integrity risks posed by PivotTables when users add additional data to the original source list
• Improving the integrity of PivotTables by utilizing the Table feature in Excel

Learning Objectives

• Demonstrate how the AGGREGATE function provides error-handling capabilities that make it superior to SUM and SUBTOTAL in cases where errors exist in the dataset
• Identify the purpose of the OFFSET function
• Understand how XLOOKUP retrieves an array of values from a table and how SUM can be used to total the results

Level
Intermediate

Instructional Method
Group: Internet-based

NASBA Field of Study
Specialized Knowledge and Applications (2 hours)

Program Prerequisites
Prior experience with Microsoft Excel is recommended.

Advance Preparation
None