For IndividualsFor Analysts & FinanceFor Students & LearnersFor Teams & Automation
MENU
=ARTICLES

Power Query vs VBA for data cleanup: how to choose

CMP.A14 NO-CODE ETL VS FULL LOOP VBA
=YOUTUBE

Watch alongside this article

Public YouTube resources matched to this article's topic, functions, and practice goal.

Power Query and VBA can both automate data cleanup, and picking the wrong one makes the work harder than it needs to be — usually by a lot, because the cost shows up later, when somebody else has to maintain it.

The distinction worth holding onto: Power Query transforms data; VBA controls Excel. Most cleanup is transformation, which is why Power Query is the default answer more often than people expect.

Why Power Query is the default

The steps are visible. Every transformation is a named row in the Applied Steps list. Someone who has never seen the file can open the query, read what it does, and change a step. A macro is a wall of code that most Excel users will not open, and will not modify if they do.

It is declarative, not procedural. You describe the end state; the engine works out how to get there. A VBA cleanup loop is a set of instructions that has to handle every case you anticipated — and silently mishandles the ones you didn’t.

It handles the awkward parts for free. Combining a folder of files, unpivoting a cross-tab into a flat list, merging two tables on a key, grouping and aggregating, type conversion with locale handling. Each of those is a right-click in Power Query and fifty-plus lines of VBA.

It does not need macro permissions. A .xlsx with queries opens normally anywhere. A .xlsm triggers a security warning, is blocked outright by many organisations when it arrives by email, and gets stripped by some mail gateways. In a corporate environment this alone decides it.

It refreshes without running anything. One button, or on file open, or on a schedule via the Power Automate / gateway route.

What Power Query genuinely cannot do

The limits are real and worth knowing precisely, because people often reach for VBA before hitting them:

  • Write anywhere except its own output table. It cannot populate a scattered template, fill a form layout, or update cells in an existing sheet.
  • Create, rename, delete, or move sheets.
  • Format cells, charts, or shapes. It applies no formatting at all beyond the output Table.
  • Respond to events. Nothing happens on cell change, on open, or on save without something else triggering it.
  • Interact with other applications. No Outlook, no Word, no file system operations beyond reading sources.
  • Write back to a source. Queries are read-only by design.
  • Prompt the user. Parameters exist, but they are set in the query, not in a dialog box mid-run.
  • Loop with conditional logic per file. It will combine a folder of similarly shaped files beautifully, and it struggles when each file needs different handling.

If your task is on that list, you need VBA — or Office Scripts, which is the modern answer for the web and increasingly the better one for new automation.

Where VBA is clearly right

  • Building a workbook — creating sheets, applying formats, inserting charts.
  • Emailing a report, saving a PDF per recipient, interacting with Outlook.
  • Responding to a worksheet event — workbook and worksheet events covers the mechanics.
  • Walking a folder where each file needs different logic.
  • Anything with a user interface — a form, a prompt, a progress indicator.
  • Writing back into an existing structured layout rather than an output table.

The case that decides it most often

Monthly files arriving in a folder, same format each time.

In Power Query: Data → Get Data → From File → From Folder, point it at the directory, click Combine. It reads every file, applies the same transformations to each, and stacks them. Drop next month’s file into the folder, press Refresh, done. Five minutes to build.

In VBA: open a Dir loop, open each workbook, find the data range, copy, paste, close without saving, handle the file that has an extra header row, handle the one that is locked because someone has it open, handle the .xls among the .xlsx. A few hundred lines, and it needs maintaining.

If you are choosing between the two for a recurring import, this scenario is almost always what you are really choosing between.

Use both: the orchestration split

The pattern that works in practice:

  1. Power Query imports and cleans into a staging Table. All the data logic lives here, visible and editable.
  2. A short VBA macro does what Power Query cannot — refresh the queries, format the output, save a PDF, email it.
Sub RefreshAndPublish()
    ThisWorkbook.RefreshAll
    Application.CalculateUntilAsyncQueriesDone
    ' ... formatting, export, email
End Sub

That CalculateUntilAsyncQueriesDone line matters: RefreshAll returns immediately while the queries are still running in the background, so a macro that exports straight afterwards exports the previous month’s data. It is one of the more common bugs in this pattern and produces a report that is wrong in a way nobody notices for a cycle.

Keep the split clean: data logic in the query, orchestration in the macro. A macro that also cleans data is a macro someone will have to read.

Three practical considerations

Performance. Power Query is generally faster on large transformations, and it streams rather than loading everything into memory. VBA that loops cell by cell is dramatically slower than VBA that reads a range into an array — if you are writing VBA for data work, read into an array first.

Who maintains it. This decides more real cases than any technical factor. A query is maintainable by a competent Excel user. A macro, in most teams, is maintainable by the person who wrote it, and becomes unmaintainable the day they leave.

Where it has to run. Power Query works in Excel for Windows and Mac, and refreshes in Excel for the web for some source types. VBA does not run in Excel for the web at all. If anyone in the audience uses the browser version, VBA is not an option for them — see Excel desktop vs Excel Online.

The practical answer

Start with Power Query for anything that is reshaping tabular data from files that arrive in a consistent format. Reach for VBA when the task requires controlling Excel itself rather than transforming data — and when you do, keep it to the orchestration layer.

If you are new to queries, getting started with Power Query is the place to begin, and Power Query vs Excel formulas covers the other side of the same decision. For the VBA side, recording your first macro and VBA editor basics.

FREE EXCEL UPDATES

Get practical Excel tips in your inbox

New tutorials, tools, templates, and worked examples a few times a month. Unsubscribe anytime.

=PRACTICE

Go deeper with this skill

Create a repeatable cleanup and refresh process instead of doing the same manual edits every month. For this article, the goal is to practice: What Power Query genuinely cannot do, what VBA costs you in maintenance and trust prompts, the folder-import case that decides it most often, and the hybrid split that works in practice.

Practice workbook setup

Use a raw export with extra columns, inconsistent text, blanks, or repeated monthly files.

Practice workflow

  1. Import the data into Power Query and rename every meaningful step.
  2. Remove columns only after confirming they are not needed downstream.
  3. Set data types deliberately for dates, numbers, IDs, and text.
  4. Load the cleaned result to a table and refresh it after changing the source.

Quality checks

  • Applied steps are named clearly enough to review later.
  • Data types are correct before loading the result.
  • Refresh works without manual cleanup in the worksheet.

Common mistakes

  • Editing the loaded table instead of the query steps.
  • Removing columns before checking whether reports depend on them.
  • Ignoring errors caused by new values or changed source headers.

Next actions

  • Create a small refresh checklist and keep it beside the report.
  • Try combining two files or appending a new month to test repeatability.

Formula focus: even if this workflow is not formula-heavy, add one check cell that confirms the final output still matches the source data.