Overview
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 has more than 30 years of experience as a spreadsheet and accounting software consultant and speaker. He has presented over 2,500 live webinars and is the author or co-author of ten books, including “Microsoft 365 Excel All-in-One for Dummies”, “Microsoft 365 Excel for Dummies”, “Exploring Microsoft Excel’s Hidden Treasures”, and “QuickBooks Online for Dummies”.
In his webinars, David demonstrates every technique twice: first on a PowerPoint slide with numbered steps, and then live in Excel for Microsoft 365 for Windows. He highlights any differences in Excel 2024, 2021, or 2019 during the presentation and in his detailed handouts. Attendees also receive an Excel workbook containing most of the examples he uses, making it easy to follow along and apply the techniques later. David additionally supports Excel for Mac users by answering their follow-up questions via email.
Why Should You Attend
Practitioners who can benefit by using a variety of lookup functions to work more efficiently in Excel
Areas Covered
· Improving on older lookup methods by using the XLOOKUP function.
· Leveraging Excel’s AutoSave and Version History features to protect workbooks saved to OneDrive and SharePoint.
· Exploring an introductory use of Solver to identify amounts that add up to a specified total.
· Finding the last match in a list with XLOOKUP.
· Matching on two or more columns of criteria at once with XLOOKUP.
· Most features and functions work in Excel for Mac as well but expect differences.
· Cleaning accounting reports in Power Query by removing blanks, merges, and missing data.
· Adding values that match a single condition within a range.
· Identify the worksheet function that supports multi-criteria lookups without a helper column.
· State which section of Excel's File tab 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.
