Power Query in Excel for Beginners: What It Does
By ExcelReady TeamUpdated 6 min read
The short answer
Power Query is Excel's built-in tool for importing and cleaning data. You load data from a table, file or folder, fix it with commands such as Trim, Split Column and Remove Duplicates, and Excel records each step. Click Refresh and every step runs again on new data. Find it on the Data tab, under Get & Transform Data.
Key takeaways
- Power Query gets data, cleans it and loads the result back into Excel, without changing the original source.
- Every change you make is saved as a step, so next month's data is cleaned again with one click on Refresh.
- It is built into Excel 2016 and later on Windows and into Microsoft 365 on Mac, on the Data tab.
- Power Query's Trim removes spaces at the start and end only; Excel's TRIM function also squeezes double spaces inside the text.
- Use formulas for small, one-off fixes and live calculations; use Power Query for imports you repeat.
On this page+
Power Query in Excel is a tool for getting data in, cleaning it and loading it back out, with every step recorded so you can repeat the whole job with one click. If you import the same report every week and fix the same spaces, columns and duplicates each time, Power Query does that work for you. This guide explains what it does in plain English, walks through a first query, and shows when an ordinary formula is still the better choice.
What is Power Query?#
Power Query is a data preparation tool built into Excel. It does three jobs, often called ETL: extract, transform and load.
- Get the data from a source: a table in your workbook, a CSV or Excel file, a whole folder of files, a web page or a database.
- Transform it: remove extra spaces, split columns, fix data types, delete duplicates, fill blanks, reshape the layout.
- Load the result into a new sheet as an Excel table, or into the Data Model for pivot tables.
The key idea is that Power Query records your clicks as a list of applied steps. It never changes the source. Next month, when the new file arrives, you click Refresh and every step runs again on the new data.
Where to find Power Query in Excel#
Power Query lives on the Data tab, in the Get & Transform Data group. You don't install anything in current versions:
| Excel version | Power Query |
|---|---|
| Microsoft 365, Excel 2024, 2021, 2019, 2016 (Windows) | Built in, on the Data tab |
| Microsoft 365 for Mac | Built in, on the Data tab, with fewer data sources |
| Excel 2010 and 2013 (Windows) | A separate add-in from Microsoft, with its own Power Query tab |
| Excel for the web | Limited: mostly refreshing queries built in the desktop app |
The same engine also powers data loading in Power BI, so the skills carry over. The comparison of Excel and Power BI explains how the two tools fit together.
Your first query, step by step#
Say you paste a customer export into a sheet, and the names have stray spaces and the sales figures came in as text. Here is how to clean it with Power Query:
- Click any cell in the data and press Ctrl+T (Mac: ⌘T) to make it an Excel table. Power Query works best with tables.
- Go to Data > From Table/Range. The Power Query Editor opens in its own window with a preview of your data.
- Click the Customer column header, then choose Transform > Format > Trim.
- Click the Sales column header and choose Transform > Data Type > Decimal Number (or Whole Number).
- Look at the Applied Steps list on the right. Each change you made is there, in order. Click a step to see the data as it was at that point.
- Choose Home > Close & Load. Excel adds a new sheet with a clean table.
To import a file instead, start with Data > Get Data > From File and pick the file type, such as From Text/CSV or From Workbook. The editor then works the same way.
Common cleaning steps in Power Query#
Most beginner jobs use a handful of commands. This table shows where each one is, and the nearest formula or Excel feature:
| Task | Power Query command | Formula or Excel equivalent |
|---|---|---|
| Remove spaces at the ends | Transform > Format > Trim | =TRIM(A2) |
| Remove non-printing characters | Transform > Format > Clean | =CLEAN(A2) |
| Fix capitals | Transform > Format > Capitalize Each Word | =PROPER(A2) |
| Split one column into two | Home > Split Column > By Delimiter | Text to Columns, or TEXTSPLIT |
| Remove duplicate rows | Home > Remove Rows > Remove Duplicates | Data > Remove Duplicates |
| Fill blanks with the value above | Transform > Fill > Down | Go To Special > Blanks |
| Turn text into numbers | Transform > Data Type | =VALUE(A2) |
| Turn month columns into rows | Transform > Unpivot Columns | No simple formula |
One difference trips people up. Power Query's Trim removes spaces only at the start and end of the text. Excel's TRIM function also cuts runs of spaces inside the text down to one, so a name with three spaces between "Acme" and "Ltd" becomes "Acme Ltd". If your data has double spaces in the middle, add a Replace Values step that swaps two spaces for one, or clean those cells with TRIM.
The guides to cleaning messy data, Text to Columns and removing duplicates show the formula and button versions of these jobs in more detail.
Refresh: where Power Query saves time#
The first clean-up takes about as long as doing it by hand. The payoff comes the second time. When new data arrives:
- Replace the source file with the new one (same name and place), or paste new rows into the source table.
- Go to Data > Refresh All (Windows: Ctrl+Alt+F5), or right-click the result table and choose Refresh.
Every applied step runs again in seconds. That is why Power Query suits weekly sales exports, monthly bank downloads and any report you rebuild on a schedule. If something in the source changes, such as a renamed column, the refresh shows an error and you fix that one step in the editor.
Combine sheets and files#
Power Query is also the standard way to stack data from several places into one table:
- Append Queries stacks tables with the same columns on top of each other, such as January, February and March sales.
- Merge Queries joins two tables side by side on a matching column, much like a lookup: for example, adding each customer's region from a second list.
- Get Data > From File > From Folder loads every file in a folder and combines them, so dropping a new file in the folder and refreshing adds it.
The guide to combining data from multiple sheets compares Power Query with VSTACK, Consolidate and copy-paste. For linking a few sheets with formulas instead, such as a lookup against a price list on another tab, see the worksheets and cross-sheet references lesson.
Power Query or formulas: which should you use?#
Both are useful, and many workbooks use both. A quick rule:
| Use Power Query when... | Use formulas when... |
|---|---|
| You repeat the same import and clean-up every week or month | It is a one-off fix on a small list |
| The data comes from files, folders or databases | The data is already in the sheet |
| You need to reshape the layout, such as unpivoting | You need a result that updates the moment a cell changes |
| The data is large and formulas would slow the file down | Someone else must be able to read the logic in the cells |
Formulas are also what most Excel job tests check, because they show how you think. Power Query is a strong extra skill for analyst and reporting roles, but it builds on the same basics: knowing what clean data looks like and how TRIM, PROPER and SUBSTITUTE fix it.
Practise it#
Before you automate a clean-up, make sure you can do it by hand. Practise cleaning messy text in a live spreadsheet: you fix stray spaces, capitals and separators with TRIM, PROPER and SUBSTITUTE, and each formula is checked instantly. Start with the exercise on this page, which shows the one thing Excel's TRIM does that Power Query's Trim doesn't.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
Power Query's Trim would leave the extra spaces inside this name, but Excel's TRIM function removes it. Click B2 and clean A2 with TRIM, then press Enter.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Customer (clean) | |||
| 2 | Acme Ltd | |||
| 3 | Brightwave Co | |||
| 4 | Delta Foods | |||
| 5 | Northwind Traders | |||
| 6 | ||||
| 7 | ||||
| 8 |
Tip: TRIM needs just one argument: the cell to clean.
Practise this in a lesson
Frequently asked questions
What is Power Query used for in Excel?
It is used to import data from files, tables, folders and databases, clean it, reshape it and load it into a sheet. Because it records each step, the same clean-up runs again whenever you refresh.
Where is Power Query in Excel?
On the Data tab, in the Get & Transform Data group. Choose Get Data to import from a file or other source, or From Table/Range to work on data already in your sheet.
Is Power Query hard to learn?
The basics are not. Most cleaning jobs use menu buttons such as Trim, Split Column, Remove Duplicates and Change Type, so you don't need to write code. Behind the scenes Excel writes the steps in a language called M, which you can learn later if you want.
Does Power Query change my original data?
No. It reads the source, applies the steps to a copy and loads the result into a new table. The original file or range stays as it was.
Is Power Query available on Mac?
Yes, in Microsoft 365 for Mac, under the Data tab. Some data sources and options are still Windows-only, so files built on Windows may not refresh fully on a Mac.