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.

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.

Areas Covered:-

• Transforming an accounting report by way of Power Query.
• Recording a contact info macro.
• Preserving macros by saving workbooks in the XLSM format versus the default XLSX format.
• Utilizing the Relative References setting for creating Excel macros to be played back on any cell versus specific cells.
• Adding clickable shapes to Excel spreadsheets as a visual means of launching Excel macros.
• Navigate Excel menus entirely by keyboard shortcuts.
• Making sense of the Enable Content prompt related to macros in Excel.
• Refreshing results from Power Query and editing the underlying queries.
• Automating the cleanup of an accounting report in Microsoft Excel with a recorded macro.
• Understanding the nuance of creating a percentage of column in Power Query.

Who Will Benefit:-

• Business Skills
• Software
• Human Resources
• Finance