Course Description
In this course, author and Excel expert David H. Ringstrom, CPA, will explore the latest features available in the Microsoft 365 Excel Beta Channel for both Windows and Mac users, including powerful new functions like XLOOKUP and XMATCH. You'll learn how to leverage Regular Expressions (REGEX) with functions such as REGEXTEST, REGEXEXTRACT, and REGEXREPLACE to streamline data manipulation and validation. David will also demonstrate practical applications of XLOOKUP for approximate matches and its integration with Excel Tables, alongside traditional functions like VLOOKUP and SUMIF, highlighting their wildcard nuances. Additionally, you'll discover how Power Query can enhance your data analysis with fuzzy matching techniques, making your workflows more efficient and effective. Join us for an insightful session that will elevate your Excel skills to new heights!
David is the author of Microsoft Excel 365 for Dummies and 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
Level: Intermediate
Topics covered:
- Defining regular expression components
- Comparing partial (wildcard) matches to approximate matches
- Defining regular expressions
- Searching for text with the REGEXTEST function
- Comparing wildcards to regular expressions
- Using the SUBSTITUTE function to strategically replace unwanted characters within your data
- Extracting text based upon pattern matching with REGEXEXTRACT
- Comparing fuzzy matching to regular expressions
- When Text to Columns won’t suffice, use the LEFT, MID, and RIGHT functions to extract text from a cell
- Breaking down regular expression patterns into tokens
- Replacing text based upon pattern matching with REGEXREPLACE
- Demonstrating how to use the REPLACE function in Excel to modify text by replacing a specified number of characters at a given position
Learning objectives: - Recall what to specify in the match_mode argument to cause XLOOKUP to return an approximate match based upon the next smaller item.
- Identify the appropriate use of an operator within the SUMIF function.
- Recall the menu in Excel where the Table feature resides.
Review our course policies and procedures page for further information