Level: Intermediate
Available Durations:
- 100 Minutes Live
Description:
In this presentation Excel expert David H. Ringstrom, CPA will compare and contrast two significant ways that you can automate repetitive tasks within your spreadsheets. For decades creating macros by using Excel’s Macro Recorder feature or writing code by hand in the Visual Basic Editor was a primary means of automating repetitive tasks. Conversely, Power Query offers a code-free solution that is particularly well suited to transforming reports and creating self-updating spreadsheets. David will automate certain tasks twice, first with macros and then Power Query so that you can compare the approaches first hand.
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:
Practitioners seeking to automate repetitive tasks in Microsoft Excel.
Topics typically covered:
- Managing the external data security warning that may appear when you link external data into Excel spreadsheets.
- Automating the cleanup of an accounting report in Microsoft Excel with a recorded macro.
- Navigating purposefully through worksheets by way of clickable hyperlinks.
- Utilizing the Relative References setting for creating Excel macros to be played back on any cell versus specific cells.
- Appending data from two or more worksheets into a self-updating consolidated list with Power Query.
- Transforming an accounting report by way of Power Query.
- Uncovering macro-related commands within Excel’s user interface.
- Recording a contact info macro.
- Making sense of the Enable Content prompt related to macros in Excel.
- Preserving macros by saving workbooks in the XLSM format versus the default XLSX format.
- Exploring the Queries & Connections task pane that shows data connections used within a given workbook.
- Introducing the Power Query feature in Excel.
Learning objectives:
- State which section of Excel’s File menu enables you to mark a document as trusted.
- Recall the menu in Excel where the Table feature resides.
- Identify the location of the PivotTable command within Excel’s ribbon menu interface.
Format:
Live webcast
Instructional Method:
Group: Internet-based
NASBA Field of Study:
Computer Software & 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.
