Level: Intermediate
Available Durations:
- 100 Minutes Live
Description:
In this presentation, author and Excel expert David H. Ringstrom, CPA, will guide you through various topics to enhance your accounting report automation using Excel. Learn how to clean up your reports using Excel’s Macro recorder to filter data efficiently, delete unnecessary rows, convert text to columns, apply number formatting, calculate percentages of sales, and then stop recording. Discover the best practices for saving workbooks containing macros and managing macro security notification prompts. You’ll then see why you don’t necessarily need to use macros because you can create code-free automation solutions with Power Query. David will show you how to make refreshable connections to reports that you can transform by filtering rows and making other changes, and return the results to Excel, where you can add supplemental formulas as needed. Don’t miss out on this opportunity to streamline your accounting reports and save valuable time.
David is the author of “Exploring Microsoft Excel’s Hidden Treasures: Turbocharge your Excel proficiency with expert tips, automation techniques, and overlooked features”. 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 typically covered:
- Refreshing results from Power Query and editing the underlying queries.
- Understanding the nuance of creating a percentage of column in Power Query.
- Adding clickable shapes to Excel spreadsheets as a visual means of launching Excel macros.
- Preserving macros by saving workbooks in the XLSM format versus the default XLSX format.
- Navigate Excel menus entirely by keyboard shortcuts.
- Transforming an accounting report by way of Power Query.
- Utilizing the Relative References setting for creating Excel macros to be played back on any cell versus specific cells.
- Making sense of the Enable Content prompt related to macros in Excel.
- Automating the cleanup of an accounting report in Microsoft Excel with a recorded macro.
- Recording a contact info macro.
- Discovering new worksheet functions available in Excel 2016 and later.
- Returning multiple columns of data with XLOOKUP from a single formula by using dynamic array functionality in Excel 2021 and Microsoft 365.
Learning objectives:
- Identify the Excel feature that can be used to create Excel macros without manually writing any programming code.
- Identify the feature that allows you to separate data into multiple columns.
- State which menu the Get Data command appears on in Excel 2019 and later.
Format:
Live webcast
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
About the Instructor:
David H. Ringstrom, CPA is the owner of Accounting Advisors, Inc., an Atlanta-based spreadsheet consulting firm that he started in 1991. Throughout his career David has spoken at conferences on Microsoft Excel, and written dozens of freelance articles about spreadsheets. He offers Excel and Access training and consulting services nationwide.
Pricing and Format Options:
Click here to learn about the presentation format and view pricing information.
Click here to view other topics.
