Excel Macros versus Power Query

Author: David H. Ringstrom

In this course 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 course 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.

Publication Date: April 2024

Topics 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
• Identify the menu in Excel where the Table feature resides
• Identify the location of the PivotTable command within Excel's ribbon menu interface
• Identify the keyboard shortcut that enables you to delete one or more rows or columns that you've selected
• Identify the characters that can be used in a macro name

Level
Intermediate

Instructional Method
Self-Study

NASBA Field of Study
Computer Software & Applications (2 hours)

Program Prerequisites
Prior experience with Microsoft Excel is recommended.

Advance Preparation
None