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.
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
Credits and Other information:
- Recommended CPE credit – 2.0
- Recommended field of study – Taxes
- Session Prerequisites and preparation: None
- Session learning level: Basic
- Location: Virtual/Online
- Delivery method: Group Internet Based
- NASBA Sponsor: 146439
- IRS Course ID: PJGWS-T-00073-25-O
- Attendance Requirement: Yes
- Session Duration: 2 Hours
- Case Studies and Live Q&A session with speaker
- PowerPoint presentation for reference
Who Will Benefit:
- CPA
- Enrolled Agents (EAs)
- Tax Professionals
- Attorneys
- Other Tax Preparers
- Finance professionals
- Financial planners
Coder Archives is registered with the National Association of State Boards of Accountancy (NASBA) as a sponsor of continuing professional education on the National Registry of CPE Sponsors. State boards of accountancy have final authority on the acceptance of individual courses for CPE credit. Complaints regarding registered sponsors may be submitted to the National Registry of CPE Sponsors through its website: www.nasbaregistry.org.
Speaker Profile:
David H. Ringstrom, CPA, is a nationally recognized instructor who leads dozens of Excel webinars each year. He is the author of Microsoft 365 Excel for Dummies and several other books. With over 30 years of consulting and teaching experience he empowers users to work more efficiently in Excel.
We are approved to provide CPE/CE credits to Tax Professionals
We reports the educational credits to the respective licensing/regulatory bodies in 5 business days.
The credentials to log into your account is your EMAIL ADDRESS & PASSWORD used during account creation.
- If you’ve forgotten your password, please go to the login page and click on the ‘Forgot Password.’ Then enter your registered email id to receive the password reset email. Click on the link given in the email to enter your new password.
- If the face any challenge, please reach out to us via email support@pro2learn.com or contact our support team through live chat support. Our support line is 1-937-865-8027.Our support team is available Monday to Friday from 09:00 AM – 06:00 PM EST/EDT
When you register for any of our webinars/product, you receive will the instruction 24 hours prior to the conference. Also, the instructions are avilable in your account under ‘My Webinars’ section.
You can cancel your registration to by email at support@pro2learn.com. If you cancel your registration for the paid webinar, the amount shall be credited back in your payment method within 7 business days.
The conference materials are available 4 hours prior to the webinar. You can download it from the webinar page or by logging to your account.
Our seamlessly integrated with ZOOM, WebX and, Goto for delivering continuing education webinars. The system requirements are :
- An internet connection – broadband wired or wireless
- Speakers and a microphone – built-in, USB plug-in, or wireless Bluetooth
Our package courses are the bundles of various continuing education courses which have been specially designed to meet your continuing education requirements for a particular Qualification.
- The web download is the recording of the webinar. You will receive a link to download and save the webinar on your computer and watch it anytime and multiple times.
- The transcript is the detailed written material presented during the webinar.
On demand session is when we conduct the webinar as per your available dates after the live training. You will get access to the training for 24 hours.
To access a course that you have already purchased, all you must have to create/log in to your account. Click on the "Log In" button in the top right of the screen. Once you are logged in, you will have immediate access to all your purchased CPE courses, packages and other self-service or you can write on support@pro2learn.com.
We will email your certificate within 3 working days of the end of the webinar, or you can login your account at www.pro2learn.com. If you don’t receive the email within 3 working days of the end of the webinar, check your spam/junk folder. If it’s not there, we recommend that you send us an email at support@pro2learn.com or connect over the online chatroom.