Course Description
Learn from Excel expert David Ringstrom, CPA, how to push the boundaries of pivot tables and add even more interactivity to them by grouping data in various ways. In this comprehensive course, David explains how to easily extract data from Microsoft Access, create simple macros that can resolve the most frustrating aspects of pivot tables, determine the number of duplicates in a list, reap the benefits of Excel’s Slicer feature, sort data in any order you desire, 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, 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 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 2019, Excel 2016, and so on.
Who should attend:
Practitioners who would like to learn how to manipulate their pivot table data faster and more efficiently.
Level: Intermediate
Topics covered:
- Contrasting the INDEX and MATCH combination to VLOOKUP or HLOOKUP.
- Demonstrating how XLOOKUP can look up and down columns or across rows.
- Displaying alternate results with XLOOKUP by populating the If_Not_Found argument instead of using IFERROR or IFNA.
- Distinguishing how wildcards work with Excel's XLOOKUP function
- Explaining the new XLOOKUP worksheet function being rolled out to Office 365 users.
- Exploring the risks and benefits of participating in the free Microsoft Office Insider program.
- Looking up data to the left or right of a given column with XLOOKUP.
- Returning multiple columns of data with XLOOKUP from a single formula by using Office 365's dynamic array functionality.
- Summing results from multiple columns with a single XLOOKUP function nested within a SUM function.
- Summing results from multiple rows and optionall columns with XLOOKUP.
- Understanding how the VLOOKUP function allows you to look up data instead of having to manually reference individual cells.
- Understanding the nuances of approximate matches with XLOOKUP, which can be greater than or less than the look-up value.
Learning objectives:
- Recall how macros can help you format pivot table data faster and more efficiently.
- Define how to create pivot tables from information you extract from databases.
- Identify how to summarize pivot table data in new ways by grouping based on dates or custom arrangements that you define.
Review our course policies and procedures page for further information