Description
Transform overwhelming lists of data into meaningful reports using Excel’s LOOKUP functions and PivotTables.Excel expert David Ringstrom, CPA, explains helpful ways you can improve the integrity of your spreadsheets using Excel’s LOOKUP functions and PivotTables. In this comprehensive topic, he reveals alternatives to VLOOKUP, including the HLOOKUP, INDEX and MATCH, SUMIF, SUMIFS, and IFNA. You’ll also get a preview of the new XLOOKUP function being added to 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. David demonstrates every technique at least twice first, on a PowerPoint slide with numbered steps, and second, in the subscriptionbased Office 365 version of Excel. He draws your attention to any differences in the older versions of Excel (2019, 2016, 2013, and earlier) in his detailed handouts. He also provides an Excel workbook that includes most of the examples he uses during the topic. Office 365 is a subscriptionbased product that provides newfeature 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.
Date: 2020-05-19 Start Time: End Time:
Learning Objectives
Adding Fields to a Blank PivotTable to Create Instant Reports
Contrasting the INDEX and MATCH Combination to VLOOKUP or HLOOKUP
Creating a PivotTable to Transform Lists of Data Into On-Screen Reports
Determining Which Refresh Commands in Excel® Update a Single PivotTable vs. All PivotTables in a Workbook
Discovering Four Different Ways to Remove Data From a PivotTable Report
Discovering How to Use Wildcards and Multiple Criteria Within Lookup Formulas
Distinguishing the Differences Among PivotTable-Related Menus in Excel® 2013 and Later vs. Older Versions of Excel®
Employing the SUMIF Function to Sum Values Related to Multiple Instances of Criteria You Specify
Explaining the New XLOOKUP Worksheet Function Being Rolled out to Office 365 Users
Filtering Data Within PivotTables in Excel® 2010 and Later by Way of the Slicer Feature
Identifying Situations Where VLOOKUP May Return #N/A Instead of a Value
Identifying the Ideal Data for Analysis With Excel’s® PivotTable Feature
Integrating Data on Web Pages Into Your Workbooks and Creating Automatic Links
Learning About the IFNA Function Available in Excel® 2013 and Later
Learning What Types of User Actions Can Trigger #REF Errors
Performing Dual Lookups, Which Allow You to Look Across Columns and Down Rows to Cross-Reference the Data You Need
Summarizing Data From Access Databases With PivotTables, Even If You Don’t Have Microsoft Access® Installed
Transforming Numbers Stored as Text Into Values by Way of the Text to Columns Wizard
Understanding How the VLOOKUP Function Allows You to Look up Data Instead of Having to Manually Reference Individual Cells
Using the HLOOKUP Function to Look Horizontally Across Rows in Any Version of Excel®
Using the MATCH Function to Find the Position of an Item in a List
Using the SUMIFS Function to Sum Values Based on Multiple Criteria
Using VLOOKUP to Perform Approximate Matches
Utilizing Excel’s® IFERROR Function to Display Alternate Values When VLOOKUP Returns an Error
CPE ,Additional credit may be available upon request. Contact Lorman at 866-352-9540 for further information.
David H. Ringstrom, CPA-Accounting Advisors, Inc.
