Excel now gives you three very different ways to solve most data problems: classic formulas, Power Query and Copilot.
The trick is not to pick a favorite and use it for everything, but to know where each one shines so you do not overcomplicate simple tasks or under‑engineer important ones.
Table of Contents
Option 1: Classic formulas
Formulas are still the most transparent way to calculate results directly on the worksheet: SUM, AVERAGE, IF, XLOOKUP, FILTER and friends all fall into this camp.
They are ideal when your data is already in a reasonable shape and you just need calculations, small summaries, or quick what‑if analysis.
- Pros: easy to see and audit, no special add‑ins or admin rights required, update instantly.
- Cons: can become messy with lots of copy‑pasted formulas, not ideal for heavy data cleanup or multi‑file imports.
Option 2: Power Query (Get & Transform)
Power Query is Excel’s built‑in ETL tool: you connect to files, folders or databases apply step‑by‑step transformations and load the cleaned result into a sheet or the data model.
It really shines when you are dealing with recurring tasks such as importing monthly CSVs, merging several sheets, or reshaping crosstabs into a proper table.
- Pros: repeatable, refreshable, great for combining and cleaning data from multiple sources.
- Cons: one extra learning curve and interface, changes are not in worksheet cells but in the query editor.
Option 3: Copilot in Excel
Copilot uses natural‑language prompts to suggest formulas, build summaries, create charts and even highlight outliers for you.
It sits on top of your existing data and tools, so in practice Copilot frequently generates formulas, pivot tables or Power Query steps you could have written yourself, just much faster.
- Pros: great for exploration, rapid prototyping and for people who are not fluent in advanced formulas.
- Cons: you still need to sanity‑check the results; requires a modern Microsoft 365 subscription with Copilot access.
How to decide what to use
Scenario 1: one‑off calculation on tidy data
Example: you have a single sales sheet and just need totals by region and a quick chart.
In this case, use formulas first: SUMIFS, XLOOKUP and maybe a dynamic array like FILTER or UNIQUE, then build a chart on top.
Scenario 2: same messy CSV arrives every month
Example: every month you get a CSV with slightly awkward column structure, but it is always “the same kind of messy”.
Here Power Query is usually the best answer: build an import and cleanup query once, then refresh next month instead of repeating the same manual steps.
Scenario 3: exploring a new dataset you have never seen
Example: someone emails you a random export and you are not even sure what is inside yet.
Copilot is very handy here: ask it to describe the data, propose a few summaries and highlight anything unusual; once you know what matters, you can lock in the final logic with formulas or Power Query.
A practical workflow that mixes all three
A pattern that works well in real projects is: use Copilot to explore and sketch the first version, Power Query to formalize repeatable data‑prep steps and formulas or pivot tables for the final reporting layer.
Once you get used to thinking this way, you will waste less time in repetitive manual steps and spend more time on the part people actually care about: the decisions that come after the numbers.
