Advanced Excel Skills For Accountants

Learning Objectives

• Understanding how the VLOOKUP function allows you to look up data instead of having to manually reference individual cells.
• Using VLOOKUP to perform approximate matches.
• Identifying situations where VLOOKUP may return #N/A instead of a value.
• Contrasting the INDEX and MATCH combination to VLOOKUP or HLOOKUP.
• Using the SUMIFS function to sum values based on multiple criteria.
• Explaining the new XLOOKUP worksheet function being rolled out to Office 365 users.
• Identifying the ideal data for analysis with Excel’s PivotTable feature.

Course Description

Excel expert David Ringstrom, CPA, explains helpful ways you can improve the integrity of your spreadsheets using Excel’s lookup functions and pivot tables. In this comprehensive online CPE course, David reveals alternatives to VLOOKUP, including the HLOOKUP, INDEX and MATCH, and SUMIFS, as well as the new XLOOKUP function in Office 365. Lookup functions are only the start, though. In many cases, you're only a few mouse clicks away from transforming overwhelming lists of data into meaningful reports with pivot tables.

David demonstrates every technique at least twice in this continuing education course: first, on a PowerPoint slide with numbered steps, and second, in the subscription-based 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.

Office 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: This online CPE course is recommended for Practitioners who can benefit by using a variety of lookup functions to work more efficiently in Excel.

This online Excel CPE webinar covers the following key topics:

• Understanding how the VLOOKUP function allows you to look up data instead of having to manually reference individual cells.
• Using VLOOKUP to perform approximate matches.
• Identifying situations where VLOOKUP may return #N/A instead of a value.
• Contrasting the INDEX and MATCH combination to VLOOKUP or HLOOKUP.
• Using the SUMIFS function to sum values based on multiple criteria.
• Explaining the new XLOOKUP worksheet function being rolled out to Office 365 users.
• Identifying the ideal data for analysis with Excel’s PivotTable feature.
• Creating a pivot table to transform lists of data into on-screen reports.
• Distinguishing the differences among pivot table-related menus in Excel 2013 and later versus older versions of Excel.
• Adding rows to a blank pivot table to create instant reports.
• Discovering four different ways to remove data from a pivot table report.
• Determining which refresh commands in Excel update a single pivot table versus all pivot tables in a workbook.
• Filtering data within pivot tables in Excel 2010 and later by way of the Slicer feature.

Overview

• VLOOKUP Introduction

  3 mins
• VLOOKUP - Approximate Match

  10 mins
• XLOOKUP Introduction (Office 365)

  20 mins
• Initiating a Pivot Table

  30 mins
• Pivot Table Slicers (Excel 2010+)

  48 mins