Power Query or Formulas: Which One Should Do the Work?

By the XLsheetAI Team · Updated August 15, 2026 · 10 min read

The one-sentence answer

Reach for Power Query when the work is repetitive or structural — the same import every month, many files from a folder, a cross-tab that needs unpivoting, an export with the wrong data types, two tables that need joining — and reach for formulas when the calculation is per-row, has to react live to what is on the sheet, and needs to sit beside the data people are looking at. Most spreadsheets that feel like a monthly chore have this backwards: the repetitive reshaping is being done by hand with formulas, and the result is rebuilt from scratch every reporting cycle.

The rest of this article is the reasoning behind that rule, plus the part people miss — the two tools are not rivals. Power Query hands off to formulas at a clean boundary, and the best workbooks use both on the right side of it.

Refresh versus recalculate

Everything else follows from one mechanical difference in how the two are triggered.

A formula is reactive. Excel tracks which cells feed which formulas, and when an input changes, everything downstream re-evaluates immediately. You type a new number, the total moves. Nobody has to ask for it.

A query is recorded. When you transform data in the Power Query editor, you are not changing values — you are appending a step to a script. Remove a column, split a name, change a type, filter out blanks: each becomes an entry in the Applied Steps list on the right of the editor. Nothing is live. The script sits there until you refresh, at which point it re-reads the source from scratch and replays every step in order.

That is why the folder-of-files case is the clearest win. You point a query at a folder once (Data → Get Data → From File → From Folder), work out the shape once, and next month you drop a new file into the folder and hit Data → Refresh All. The steps do not care that the file is new. They were never about that file, only about the shape of the data.

Five jobs Power Query should own

These five patterns come up constantly, and all five are painful as formulas because they are about the shape of the data, not about calculating anything.

1. Importing and reshaping the same export on a schedule. Some system produces a CSV or XLSX with the same columns every month. Point a query at it, clean it once, and each new file is a refresh rather than a rebuild.
2. Combining many files from a folder. Twelve monthly files that need to become one table is the single strongest case for Power Query. Data → Get Data → From File → From Folder, and the query stacks every file it finds, adding a column for the source file name. Adding a thirteenth file is a refresh, not a copy-paste marathon.
3. Unpivoting a cross-tab into a proper table. Reports arrive with months across the top — a layout built for reading, not for analysis. Select the month columns in the editor and use Transform → Unpivot Columns to turn them into two columns, an attribute and a value. That is the shape PivotTables and lookups actually want. Doing the same thing with formulas means an INDEX gymnastics routine that nobody will maintain.
4. Type-cleaning an export. Numbers stored as text, dates as strings, trailing spaces, non-breaking spaces from a web copy-paste, a stray "N/A" in a numeric column. Trim, Clean, Replace Values and Changed Type handle these as steps that apply to every future refresh, instead of a helper column full of VALUE(SUBSTITUTE(TRIM(...))) that has to be dragged down again next time.
5. Joining two tables. Home → Merge Queries does a real join: left outer, inner, anti, on one column or several. Anti joins in particular are a genuine pain with formulas — "which customers are in this list but not that one" — and are a two-click operation in the editor.

Notice what all five have in common. None of them are calculations. They are all about getting rows and columns into a state where calculation becomes easy.

What formulas are still better at

The formula bar is not a legacy interface, and there are jobs where opening the query editor is the wrong instinct.

Live, per-row logic on the sheet. If a value must update the instant someone types in a cell — a commission that changes with a rate in B2, a status that flips when a date passes, a total that follows a filter — that is what the calculation engine is for. A query would need a manual refresh to notice, which is unhelpful for anything interactive.

Results that must sit beside the data. A margin column next to revenue and cost, a flag column that a reviewer will read while scrolling, a running total in a tracker people type into. Query output is a block that gets replaced wholesale on refresh; formulas can live anywhere.

Anything a user is meant to change. Assumption cells, scenario toggles, what-if inputs. The whole point is that a human edits an input and watches everything move. That is a formula relationship, not a data pipeline.

One-off work. If the task is "split this one column of 200 names and never think about it again", a TEXTSPLIT, a Flash Fill, or Data → Text to Columns is done before the Power Query editor has finished opening. Recording steps pays off through repetition, and where there is no repetition there is no payoff.

A task-by-task dividing line

The handoff: query output is a Table you can write formulas against

This is the part that resolves the false choice. When you finish in the editor and click Home → Close & Load, the result lands on a worksheet as a genuine Excel Table, with a name, structured column references and everything a Table normally gives you. It is not a locked object.

So the honest architecture for most workbooks is a pipeline, not a competition:

• Power Query does the loading and shaping. Import, combine, unpivot, clean types, join, filter. Output: one tidy Table per subject.
• Formulas, PivotTables and charts do the analysis on top. XLOOKUP against the loaded Table, SUMIFS by structured column reference, a PivotTable built directly on it, a dashboard sheet that reads from it.

One practical rule keeps this clean: put your own formulas outside the loaded Table, on a neighbouring sheet or in a separate output block, and reference the Table by name. Columns you add inside the Table can be preserved, but a refresh that changes the query's column layout is exactly when that arrangement breaks. Treating the loaded Table as read-only output — the query owns it, you read from it — avoids the whole category of problem. If the next step is summarising, that Table is also the ideal source for a PivotTable; our guide on how to create a pivot table in Excel picks up exactly where the query leaves off.

Applied Steps as documentation

There is a maintenance argument here that gets less attention than it deserves.

A cleanup written as a chain of formulas is only readable by reverse engineering it. Someone opens the workbook in six months, finds four helper columns of nested SUBSTITUTE and IFERROR, and has to work backwards to figure out what each one was defending against. If the person who wrote it has left, that knowledge is gone.

The same cleanup as a query is a numbered list in plain language: Source, Promoted Headers, Changed Type, Trimmed Text, Replaced Errors, Filtered Rows, Removed Duplicates. You can click any step and see the data as it looked at that moment, insert a step in the middle, delete one that is no longer needed, or rename it to something meaningful. The transformation documents itself, in order, at the level a human thinks about it.

It also makes handover realistic. "Refresh the query" is an instruction a colleague can follow on their first day. "Redo the cleanup the way I do it" is not.

The performance argument, stated honestly

You will see claims that Power Query is faster than formulas. That framing is not quite right, and the real mechanism is more useful to understand.

Power Query does not make transformation cheap. It makes transformation happen once, at load time, and then the result sits on the sheet as ordinary static values. Refreshing a large query can take a while — that cost is real, and it is paid at a moment you chose.

Formulas that do cleanup work are different in kind. They stay live. Every one of them is a standing instruction to the calculation engine, re-evaluated whenever anything they depend on changes. Ten thousand rows of nested text cleanup, or lookups pointed at whole columns, are re-derived on every edit, every sort, every recalculation — even though the underlying source data has not changed since the last import and the answer is guaranteed to be identical.

That is the argument, and it is a mechanical one rather than a benchmark: work done once at load beats the same work redone continuously in the background. It also explains the classic symptom. A workbook that has become sluggish to type in usually has thousands of cleanup formulas quietly re-running, not one slow calculation. Moving that cleanup into query steps removes them from the recalculation chain entirely, and typing in the sheet becomes responsive again.

The trade-off is that refresh is manual by default. If you want the query to update itself, open the Queries & Connections pane, right-click the query, choose Properties, and enable Refresh data when opening the file, or set a refresh interval.

How to decide in about ten seconds

Three questions, in order:

1. Will I do this again? If the answer is yes — next month, next quarter, every time a file arrives — record it as a query. If it is genuinely once, use formulas and move on.
2. Am I changing the shape or calculating a value? Changing shape (combining, splitting, unpivoting, joining, filtering, retyping) is Power Query's job. Deriving a number from other numbers on a row is a formula's job.
3. Does it need to react to something on the sheet? If a human input has to move the result immediately, it must be a formula. A query cannot see the sheet and will not notice.

When two of the three point the same way, that is your answer. When they conflict — repetitive work that also has to react live — split it: query the import and the cleanup, formula the reactive part on top.

Not sure which side of the line your task falls on? Describe the cleanup in plain English and XLsheetAI will tell you whether it is a formula job or a Power Query job — and give you the working formula when it is the former, or explain any formula you paste in when you inherit someone else's.

Bottom line

Formulas are not the wrong tool; they are the wrong tool for repeated reshaping, which is what most people are using them for. If a cleanup happens on a schedule, or if it changes the shape of the data rather than deriving a value from it, record it as a query and spend one click a month instead of an afternoon. Keep formulas for what only they can do: per-row logic that reacts, live, to what is on the sheet in front of someone.

Related reading: how to create a pivot table in Excel covers the natural next step once a query has produced a clean Table, and Excel vs Power BI covers the point at which the pipeline has outgrown a workbook altogether.