Advanced Excel Skills for Accountants

In this presentation author and Excel expert David Ringstrom, CPA, helps you expand your Excel toolbox by comparing the INDEX/MATCH functions to the XLOOKUP worksheet function in Excel 2021 and Excel for Microsoft 365. You'll also see how to transform ugly reports exported from accounting software and other platforms into analysis ready formats by using Power Query. David will also show you how to use Excel's Solver feature to identify the combination of amounts from a list, such as invoices or deposits, that add up to an amount of your choice.

David is the author of “Microsoft Excel 365 for Dummies”, “Exploring Microsoft Excel’s Hidden Treasures”, and has written or co-authored six other books. He demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the subscription-based Excel for Microsoft 365. David draws your attention to any differences in Excel 2021, 2019 or 2016 during the presentation and in his detailed handouts. The handouts include an Excel workbook with most of the examples he uses during his demonstrations.

Excel for Microsoft 365 is a subscription-based product that receives periodic feature updates. Conversely, perpetually licensed versions have year numbers in their names and do not receive any feature updates.

Who Should Attend
Professionals seeking to use Microsoft Excel more effectively.

Topics Covered

• Enabling a workbook-specific setting that will create an automatic back-up of critical workbooks
• Changing the data type for columns of data within the results grid
• Using XLOOKUP to search lists from the bottom up to find the last match (instead of only the first match with VLOOKUP)
• Contrasting the INDEX and MATCH combination to VLOOKUP or HLOOKUP
• Diagnosing #N/A errors that arise when numbers are stored as text or when text contains extraneous spaces
• Tweaking Excel’s AutoRecover settings to raise the odds of recovering your work after an Excel crash
• Exploring the XLOOKUP worksheet function in Excel 2021 and Microsoft 365
• Using Power Query to clean up accounting reports to remove pitfalls like blank rows, merged cells, missing data, and more
• Transforming an accounting report by way of Power Query
• Using Power Query to clean up accounting reports to remove pitfalls like blank rows, merged cells, missing data, and more
• Using Excel's Solver feature to find the amounts that match a total, such as which checks or invoices make up a particular total
• Enabling Excel’s Solver add-in for more complex what-if analyses

Learning Objectives

• Define the arguments for the INDEX worksheet function
• State which section of Excel's File menu enables you to mark a document as trusted
• Recall the area of Excel's Options dialog box that allows you to enable the Solver feature in Excel

Level
Intermediate

Instructional Method
Group: Internet-based

NASBA Field of Study
Specialized Knowledge and Applications (2 hours)

Program Prerequisites
Prior experience with Microsoft Excel is recommended.

Advance Preparation
None