Power Query or a Macro? How to Choose in Excel
Power Query builds a refreshable new table from a source. A macro changes what is already on your sheet. That difference decides which one you want.
2026-09-20
Power Query or a macro?
Use Power Query when you are pulling data in from somewhere and cleaning it on the way. Use a macro when you are changing something that is already sitting on your sheet. That one distinction settles most cases.
We publish macros, so treat the rest of this as an argument partly against our own interest. There are jobs where Power Query is plainly the better tool, and using a macro for them is more work for a worse result.
TL;DR: Key takeaways
- Power Query creates a new table from a source and refreshes it on demand.
- A macro changes what is already there, in place.
- Power Query runs in Excel for the web. VBA does not.
- Power Query leaves the original source untouched, by design.
- Neither is a subset of the other. Plenty of workbooks sensibly use both.
The dividing line
Microsoft describes Power Query as four phases: connect, transform, combine, load. Everything it does falls inside that arc. You point it at a source, shape the data on the way through, and it lands as a table.
Two consequences follow, and they are the whole comparison.
First, the original stays untouched. Microsoft is explicit that transformation happens "while the original source remains unchanged." Power Query does not edit your data. It produces a new version of it.
Second, every step is recorded and repeatable. The Applied Steps list is the query, and refreshing re-runs all of it against current data. That is why it suits anything you do to a monthly file.
A macro works the other way round. It reaches into the cells you have and changes them. That is why our tools operate on your selection, and also why they cannot be refreshed: once run, the change is simply made.
Use Power Query when
- You import the same messy file every week and clean it the same way.
- The data comes from outside the workbook: a CSV, a database, a folder of files, the web.
- You want the cleanup to re-run when the source updates.
- You need to combine several tables or files into one.
- You want the result auditable, since every step is listed and editable.
The monthly-report case is the clearest. If you are doing the same six cleanup steps to a new export every month, that is a query, not a macro.
Use a macro when
- The data is already in the workbook and needs changing where it sits.
- The job is about formatting, structure, or the workbook itself rather than the data.
- You want a one-off fix, not a permanent pipeline.
- The task touches sheets, names, comments, hyperlinks, or visibility, none of which Power Query addresses.
Most of our free tools fall squarely here. Unmerging cells and filling down, deleting hidden sheets, trimming whitespace across a selection, exporting each sheet to its own CSV. None of those are import-and-shape problems, and Power Query has no answer for them.
Power Query works in the browser, VBA does not
This one decides it outright for some teams.
Microsoft supports Power Query on Excel for Windows, Excel for Mac and Excel for the web. VBA runs on desktop Excel only, and the browser version cannot create, run, or edit macros at all.
If your organisation lives in Excel for the web, a macro is not an option no matter how well it fits the job. Worth knowing before you build something nobody can run. Note the Mac caveat too: Power Query is supported in Microsoft 365 for Mac, but not in Excel 2016 or 2019 for Mac.
They are not mutually exclusive
The framing of one versus the other is mostly an artefact of comparison articles.
A realistic monthly close might use a query to pull and clean three exports into one table, then a macro to format the result, add a totals row and export each sheet to CSV for the finance system. Query for getting the data right, macro for everything after.
If you are choosing what to learn first and your work is mostly importing and cleaning, learn Power Query. It handles a large share of everyday Excel drudgery without a line of code.
Frequently asked questions
Is Power Query better than macros?
Neither is better. Power Query is better at importing, cleaning and combining data on a repeatable basis. Macros are better at changing an existing workbook, including formatting, sheet management and anything outside the data itself. The task decides.
Can Power Query replace VBA?
For import-and-clean work, often yes, and with less maintenance. It cannot replace VBA for formatting cells, managing sheets, or acting on a workbook's structure, because its documented scope is connecting to data, shaping it and loading it.
Does Power Query trigger a macro security warning?
No. It is a built-in Excel feature, not VBA code, so a workbook using only queries can be saved as a normal .xlsx with no macro warning and no blocked-file problem when someone downloads it.
Can I use Power Query in Excel for the web?
Yes. Microsoft supports Power Query on Excel for Windows, Mac and the web, and in 2025 the web version gained refresh from authenticated data sources. VBA macros remain desktop-only.
Should I learn Power Query or VBA first?
Power Query, if your work is mostly importing and cleaning data, which for most people it is. It needs no code and covers a wide range of repetitive tasks. Learn enough VBA afterwards to run and adjust macros for the jobs queries cannot reach.
What to do next
Look at what you actually repeat. If it starts with opening a file someone sent you, try a query. If it starts with a workbook you already have and ends with it looking different, a macro is the shorter path. Our free tools library covers the second category, and running a downloaded macro is the thirty-second version of getting started with one.