Excel Automation: Macros vs. Power Query

Access this expert-led webinar instantly, available anytime on-demand.

5.0
Included in All-Access Membership
Live Webinar - no upcoming date
Customer Satisfaction Guarantee Learn with confidence. If you're not happy, we'll make it right. That's our guarantee.

Purchase Options

Select an attendee quantity to add to cart.

Recorded Webinar Only

$219.00
or

All Access Membership

The Aurora All Access Membership is designed to provide you with the training that you want when you want it. You will have 100% access to every live webinar, on demand webinar, professional alert, and podcast that Aurora Training Advantage offers with no additional cost.

Learn More About Our All Access Membership
$599.00
All Access Membership

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.

Topics typically 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.

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.
  1. Introduction
  2. Please Ask Questions Today! 00:02:58
  3. Excel Versions 00:04:12
  4. Accounting Report Macro: Clean-Up 00:05:41
  5. Accounting Report Macro: Filtering 00:19:21
  6. Accounting Report Macro: Delete Rows 00:27:01
  7. Accounting Report Macro: Text to Columns 00:33:04
  8. Accounting Report Macro: Number Format 00:32:40
  9. Accounting Report Macro: % of Sales 00:38:30
  10. Accounting Report Macro: Stop Recording 00:42:53
  11. Creating a Clickable Shape 00:45:40
  12. Saving Workbooks That Contain Macros 00:52:19
  13. Macro Security Notification Prompt 00:58:35
  14. Recording a Simple Contact Info Macro 00:59:39
  15. Running the Contact Info Macro 01:02:37
  16. Re-recording with Relative References 01:05:24
  17. Playing Back the Re-Recorded Macro 01:08:32
  18. Creating an Icon for Our Macro 01:10:29
  19. Power Query: Linking to Report 01:15:02
  20. Power Query: Merge Columns 01:18:20
  21. Power Query: Filtering Rows 01:20:00
  22. Power Query: % of Column - Steps 26 - 34 01:26:15
  23. Power Query: % of Column  Steps 35 - 42 01:26:41
  24. Power Query: Return Results to Excel 01:37:55
  25. Three Ways to Manually Refresh Queries 01:38:23
  26. Speaker Closing 01:41:16
  27. Presentation Closing 01:41:49
  • David H. Ringstrom, CPA

    David H. Ringstrom, CPA

    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 E [...]

    View Full Profile

ATATX Credit

Aurora Training Advantage is offering continuing education points designed to recognize dedication to training and excellence in accounting.

ATAAA Credit

Aurora Training Advantage is offering continuing education points designed to recognize dedication to training and excellence in administrative.

ATAOP Credit

Aurora Training Advantage is offering continuing education points designed to recognize dedication to training and excellence in operations.
  1. .CSV 00:24:06
  2. .XLSX 00:24:08
  3. Cell 00:22:22, 00:26:36, 00:30:38, 00:38:35, 00:43:02, 00:59:56, 01:03:45, 01:08:43
  4. Column 00:07:56, 00:23:11, 00:32:44, 01:18:24, 01:23:56
  5. Copy Command 00:21:01
  6. Dialog Box 00:09:38, 00:10:24
  7. Filter 00:21:38, 00:23:18, 00:28:37, 01:18:16, 01:20:03
  8. Format 00:31:33, 00:36:33
  9. Format Painter 00:38:38
  10. Formula 00:19:35, 00:21:13, 00:38:53, 01:31:31
  11. Keyboard Shortcut 00:12:36, 00:18:38, 00:27:10, 00:44:16
  12. Macro 00:00:06, 00:01:40, 00:05:52, 00:08:39, 00:10:29, 00:30:50, 00:44:06, 00:47:43 00:52:22, 01:02:51, 01:08:52, 01:37:50
  13. Macro Recorder 00:08:42, 00:12:15, 00:14:22, 00:18:37, 00:21:45, 00:34:04, 00:43:09, 00:59:42, 01:26:20
  14. Microsoft 365 00:04:16
  15. Paste Command 00:21:09
  16. Personal Macro Workbook 00:13:38, 01:00:53, 01:03:59
  17. Power Query 00:00:06, 00:02:05, 01:15:05, 01:26:27, 01:33:07, 01:38:24
  18. Power Query Editor 01:38:51
  19. Refresh 01:38:28
  20. Relative References 01:05:37, 01:10:07
  21. Row 00:08:00, 00:17:52, 00:22:06
  22. Text File 00:06:35, 00:15:16, 00:17:19
  23. Text to Columns Wizard 00:30:17
  24. Total Row 00:08:02, 00:26:28
  25. Workbook 00:11:11, 00:14:15, 00:17:15, 00:43:58, 00:53:08, 01:15:17

.CSV: Comma-Separated Value files are text files where each field of data is separated by a comma. This is an effective means to export data from QuickBooks that you, in turn, wish to analyze in Excel.

.XLSX: A file with the. xlsx file extension is a Microsoft Excel Open XML Spreadsheet (XLSX) file created by Microsoft Excel. You can also open this format in other spreadsheet apps, such as Apple Numbers, Google Docs, and OpenOffice.

Cell: In spreadsheet applications, a cell is a box in which you can enter a single piece of data. The data is usually text, a numeric value, or a formula. The entire spreadsheet is composed of rows and columns of cells.

Column: A column is a vertical series of cells in a chart, table, or spreadsheet in Excel.

Copy Command: If you want to copy the contents of the selected cell, press Ctrl + C. The dotted border around the cell is so-called marching ants. If you see marching ants around a cell, it means that the content has been copied or cut. if you rather use the mouse to copy, to copy the contents of the selected cells to another location, select these cells, hold down the Ctrl key, then move your cursor over their border, so that the cursor will change to an arrow with a plus sign next to it.

Dialog Box: A dialog box in Excel is a screen where you input information and make choices about different aspects of the current worksheet or its content, such as data, charts, and graphic images.

Filter: The Filter feature in Excel allows you to show or hide rows within a list of data by making selections from drop-down lists. The Filter feature is available on the Data tab of all versions of Excel as well under the Sort & Filter command on the Home menu.

Format: When we format cells in Excel, we change the appearance of a number without changing the number itself. We can apply a number format (0.8, $0.80, 80%, etc) or other formatting (alignment, font, border, etc). By default, Excel uses the General format (no specific number format) for numbers.

Format Painter: The Format Painter copies formatting from one place and applies it to another. For example, if you have written text in Word, and have it formatted using a specific font type, color, and font size you could copy that formatting to another section of text by using the Format Painter tool.

Formula: A formula is an expression which calculates the value of a cell.

Keyboard Shortcut: A keyboard shortcut is a series of one or several keys that invoke a software program to perform a preprogrammed action. This action may be part of the standard functionality of the operating system or application program, or it may have been written by the user in a scripting language.

Macro: One or more lines of programming code that automate tasks. The Macro Recorder allows users to automate tasks without seeing the underlying programming code.

Macro Recorder: A feature in Excel that allows you to transcribe actions you take in Excel into programming code.

Microsoft 365: Microsoft 365, formerly Office 365, is a line of subscription services offered by Microsoft which adds to and includes the Microsoft Office product line.

Paste Command: You can paste the contents into a cell using the Ctrl + V keyboard shortcut.

Personal Macro Workbook: A hidden workbook that typically serves as a repository for macros you wish to always be available in any Excel workbook you have open.

Power Query: Power Query is a data connection technology that enables you to discover, connect, combine, and refine data sources to meet your analysis needs. Features in Power Query are available in Excel and Power BI Desktop. Power Query is one of three data analysis tools available in Excel: Power Pivot.

Power Query Editor: Power BI Desktop also comes with Power Query Editor. Use Power Query Editor to connect to one or many data sources, shape and transform the data to meet your needs, then load that model into Power BI Desktop.

Refresh: The Refresh command appears on the Options tab of Excel 2007 and 2010 as well as the Analyze tab of Excel 2013. Pivot tables store a snapshot of the underlying source data, so they don’t immediately reflect changes to said data. You must periodically refresh any pivot table to ensure it reflects any changes to the source data.

Relative References: When you reference a worksheet cell in an Excel formula, this defaults to a relative reference, meaning the column letters and row numbers change automatically when you copy or move the formula. Conversely, unless you turn on the Relative Reference feature, macros you record will capture the absolute address of any cells that you effect during the recording process. Relative References instruct the Macro Recorder to transcribe the number of rows or columns to move to next rather than to a specific cell address.

Row: A row is the range of cells that go across (horizontal) the spreadsheet/worksheet. Rows are identified by numbers e.g. row 1, row 5. Examples of use. A row might contain the headings of a table e.g. product ID, product name, price, number sold.

Text Files : Raw data files that often have file extensions such as .TXT or .CSV. TXT files are sometimes tab-delimited (meaning each field is separated by a tab character) while CSV files are comma-delimited.

Text to Columns Wizard: An Excel feature which allows users to separate data from a single column within an Excel spreadsheet into two or more columns, or to remove unnecessary data from within a column.

Total Row: A Total row appears below the data where each column has access to several automatic formulas. The default selection for the Total Row is none, meaning no function is selected when you first turn on the Total Row on your Table.

Workbook: In Microsoft Excel a workbook is a collection of one or more spreadsheets, also called worksheets, in a single file.

Customer Satisfaction Guarantee
Invest in your future with confidence! Our Customer Satisfaction Guarantee eliminates all risk, letting you focus purely on mastering new skills and advancing your career. If you're not completely satisfied, we'll ensure you are. Your satisfaction is not just a promise; it's our guarantee.

Webinar Survey Overall Rating

This webinar received a total of 3 survey responses. Attendees have given an average rating of 5.0 stars out of a possible 5, reflecting the quality and value of the content presented.

Average rating

5.0 / 5
Webinar Presentation
How many of the objectives of the event were met?
5.0 Stars
How useful was the information presented at this event?
5.0 Stars
Overall, how satisfied were you with this event?
5.0 Stars
Speaker Performance
Overall, how satisfied were you with this presenter?
5.0 Stars
How closely did the presenter follow the schedule?
5.0 Stars

Reviews From Webinar Survey

Our webinars are crafted to deliver exceptional value and insight to business professionals. Below, you'll find genuine feedback from attendees.

Isabel V.
March 5, 2025
5.0 / 5
Webinar Rating:
5.0 Stars
Speaker Rating:
5.0 Stars
Do you have any other comments, questions or concerns?
great class, however too little time to follow everything.
Carmen Y.
March 5, 2025
5.0 / 5
Webinar Rating:
5.0 Stars
Speaker Rating:
5.0 Stars
Do you have any other comments, questions or concerns?
Wish the training had started with Power Query and less time on Macroes.
Olga P.
March 5, 2025
5.0 / 5
Webinar Rating:
5.0 Stars
Speaker Rating:
5.0 Stars
Do you have any other comments, questions or concerns?
Great presentation

Frequently Asked Questions

Excel's Macro Recorder captures the steps you take in Excel and converts them into Visual Basic for Applications (VBA) code automatically—no programming knowledge required. To start recording, go to View > Macros > Record Macro (or Developer > Record Macro if the Developer tab is visible), give the macro a name, optionally assign a keyboard shortcut, choose where to store it, and click OK. Perform the actions you want to automate—filtering data, applying formatting, deleting rows, running Text to Columns—then click Stop Recording. The recorded macro can then be played back to repeat those exact actions on demand. Before recording, decide whether to use Absolute References (actions target specific cells) or Relative References (actions target cells relative to wherever your cursor is at playback), the latter making macros portable across any dataset. Workbooks containing macros must be saved in the .XLSM format to preserve the code. David Ringstrom, CPA, covers macro recording best practices in Aurora Training Advantage's Excel Automation: Macros vs. Power Query webinar.
The file format you choose when saving an Excel workbook determines whether macros are preserved. The default .XLSX format is an XML-based file that deliberately cannot store VBA macro code—this is a security feature to prevent macro-based malware from spreading through ordinary spreadsheet files. If you try to save a workbook containing macros as .XLSX, Excel warns you that the VBA code will be removed. To retain macros, you must save the file as .XLSM (Excel Macro-Enabled Workbook). When someone opens an .XLSM file, Excel displays a security notification prompting them to Enable Content—this is the macro security notification prompt that signals the file contains executable code. For macro-free workbooks, .XLSX is the standard. The Personal Macro Workbook (PERSONAL.XLSB) is a special hidden workbook that stores macros available in any open Excel file. David Ringstrom, CPA, explains these file format distinctions in detail in Aurora Training Advantage's Excel Automation: Macros vs. Power Query webinar.
Power Query (available via Data > Get Data in Excel 2019 and later, or Data > New Query in Excel 2016) is a data transformation and connection technology that lets you import, clean, and reshape data from virtually any source—CSV files, databases, web pages, other workbooks—without writing any code. You connect to a data source, apply transformation steps (filtering rows, merging columns, removing duplicates, changing data types, splitting columns, calculating percentages) through a visual interface, and load the results back into Excel. The key advantage over macros is refreshability: once your Power Query transformation is built, you click Refresh and Excel re-runs all steps against the current data automatically. There are three ways to refresh: right-clicking the table and selecting Refresh, using Data > Refresh All, or enabling automatic refresh on file open. Power Query is ideal for recurring reports from systems like QuickBooks. Aurora Training Advantage's Excel Automation: Macros vs. Power Query webinar, led by David Ringstrom, CPA, demonstrates building Power Query transformations step by step.
Macros and Power Query serve complementary but distinct automation purposes in Excel. Power Query excels at data import and transformation—cleaning imported reports, filtering rows, merging columns, standardizing formats, and creating refreshable connections to external data sources, all without code. It is the better choice when your core task is getting data into shape repeatedly from an external source. Macros (VBA) are better suited for interactions that go beyond data transformation: formatting worksheets, creating clickable buttons, sending emails, interacting with other applications, performing conditional logic based on user input, or automating workflows that span multiple workbooks or require user prompts. Complex formatting sequences, custom keyboard shortcuts, and actions that cannot be expressed as data transformations are natural macro territory. Many professional Excel solutions combine both: Power Query handles the data import and cleaning, while macros handle the presentation, distribution, and workflow orchestration. David Ringstrom, CPA, explores this decision framework thoroughly in Aurora Training Advantage's Excel Automation: Macros vs. Power Query webinar.
When you record a macro in Excel, the Macro Recorder by default captures the absolute cell addresses of every cell you interact with. This means the macro always acts on those specific cells—useful for fixed-layout reports but limiting for dynamic datasets. Enabling Relative References (View > Macros > Use Relative References, or Developer > Use Relative References) changes the recorder's behavior: instead of storing the exact cell address, it records how many rows and columns to move from the current cursor position. A macro recorded with Relative References will perform the same sequence of actions starting from wherever the cursor is at playback, making it portable across any row or column position. For example, a contact info macro recorded with Relative References can be played back in any empty row, entering data relative to the current position. Toggling Relative References on or off before recording different steps lets you combine absolute and relative behaviors in a single macro. David Ringstrom demonstrates this technique in Aurora Training Advantage's Excel Automation: Macros vs. Power Query webinar.