Excel 2019: Master Advanced PivotTable Tools

Course description

This course explores tools and techniques that extend PivotTable functionality in Excel 2019. Participants learn to create PivotTables with the PivotTable and PivotChart Wizard, add custom calculations, and enhance clarity with conditional formatting. The course begins with a walkthrough of the wizard as an alternative to the standard creation process. Learners discover how to access it through the Quick Access Toolbar or shortcut keys and use it to build PivotTables from Excel lists, external data sources, or existing PivotTables, including consolidating multiple data ranges into one PivotTable. Next, participants use calculated fields to generate new data points such as commissions, percentages, or totals without modifying the source data, and learn how to create, modify, and delete them, along with their strengths for simple aggregations and limitations compared to Power Pivot. Learners then explore calculated items to create customized groupings and combinations of existing row or column labels, with best practices for managing totals and avoiding data duplication. Finally, participants apply conditional formatting within PivotTables to highlight trends and outliers, seeing how color scales, data bars, and other rules adapt when PivotTables are filtered or refreshed and how to manage rules tied to PivotTable ranges. By the end, participants can create dynamic PivotTables that incorporate calculated metrics, grouped data, and visual formatting to reveal key business insights efficiently.

Course outline