Advanced Excel Pivot Table Techniques (Completed)

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 webcast, 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 (2019, 2016, 2013, 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 webcast.

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 2019, Excel 2016, and so on.

Who Should Attend
Practitioners who would like to expand their knowledge of manipulating and grouping pivot table data.

Topics Covered

• Automating spreadsheet updates by utilizing the From Text feature to connect to comma-separated value files
• Controlling whether linked workbooks update automatically or not as well as suppress unwanted prompts
• Creating flexible workbook links that can be copied down columns or across rows
• Discovering a free tool you can use to locate even the most buried workbook links
• Discovering the capabilities of the SUMPRODUCT function for calculating payroll and other amounts
• Eliminating the risk of workbook links by using Microsoft Query to get data from one workbook into another
• Employing the SUMIF function to sum values related to multiple instances of criteria you specify
• Integrating data on web pages into your workbooks and creating automatic links
• Learning how to arrange two workbooks on-screen at the same time for ease of use in creating linked formulas or comparing data
• Learning how to arrange two worksheets from within the same workbook on-screen and at the same time
• Learning how to break links to other workbooks and data sources
• Learning how to repair broken workbook links

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

Level
Advanced

Instructional Method
Group: Internet-based

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

Program Prerequisites
Intermediate-Level Experience with Excel Pivot Tables.

Advance Preparation
None