Maximize Excel: Advanced Pivot Table

CPE Credit: 2 hours
 
Excel expert David Ringstrom, CPA, demonstrates step-by-step how to manipulate your pivot table data faster and more efficiently. You’ll learn how to extract data from Microsoft Access databases and other sources, minimize repetitive steps in Excel by creating keyboard shortcuts, and adapt simple macros that can be recorded. In addition, David discusses several helpful Excel features, including the Table feature, PivotTable feature, Slicer feature, Linked Picture feature, the PowerPivot feature, and others.
 
David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in Excel 2016. He draws your attention to any differences in Excel 2013, 2010, or 2007 during the presentation as well as in his detailed handouts. David also provides an Excel workbook that includes most of the examples used in the course.


Topics Covered

• Converting .XLS files compatible with Excel 2003 into the modern Excel workbook format.
• Utilizing the Timeline feature in Excel 2013 and later to filter pivot tables based on date ranges.
• Summarizing data from Access databases with pivot tables, even if you don’t have Microsoft Access installed.
• Altering the default sort order within pivot tables to a hierarchy of your choice with Custom Lists.
• Displaying two or more pivot tables close together on a single worksheet without triggering a conflict.
• Filtering two or more pivot tables simultaneously by way of the Slicer feature in Excel 2010 and later.
• Filtering data within pivot tables in Excel 2010 and later by way of the Slicer feature.
• Discovering the Custom Lists feature in Excel, which enables you to embed frequently used lists into Excel’s Options dialog box for use with any spreadsheet.
• Understanding the conflicts that can arise when you position two or more pivot tables too close in proximity to each other.
• Resolving situations where data appears more than once within a pivot table.

Learning Objectives:

• Identify how to summarize pivot table data in new ways by grouping based on dates or custom arrangements that you define.
• Define how to create pivot tables from information you extract from databases.
• Recognize when and why to apply the Slicer feature.

Course Number: 
DR1921
NASBA Field of Study: 
Computer Software and Applications
Level: 
Intermediate
Presenter:
David Ringstrom
Prerequisites: 
Intermediate-Level Experience with Excel Pivot Tables
Advanced Preparation: 
None