If you clean the same messy export every week (extra spaces, wrong headers, merged names, duplicates), Power Query can do it for you. You record the cleaning steps once with clicks, and next time you just press Refresh. No formulas and no macros.
Quick answer
- Click inside your data and go to Data → From Table/Range.
- Clean the data in the Power Query Editor using the ribbon buttons.
- Click Home → Close & Load. Next time the source changes, click Data → Refresh All.
Where to find Power Query
Power Query is built into Excel 2016 and later on Windows, and into Excel for Microsoft 365. It lives on the Data tab in the Get & Transform Data group. Excel for Mac (Microsoft 365) also includes Power Query, with fewer data sources.
Step 1: Load your data
You can load data from many places:
- Data in the same workbook: click inside it, then Data → From Table/Range. Excel turns it into a Table first.
- A CSV or text file: Data → Get Data → From File → From Text/CSV.
- Another Excel file: Data → Get Data → From File → From Workbook.
The Power Query Editor opens. Your data is in the middle, and on the right is the Applied Steps list, which records everything you do.
Step 2: Fix the headers and data types
- If the column names are in the first row of data, click Home → Use First Row as Headers.
- Check the small icon next to each column name (ABC for text, 123 for whole numbers, a calendar for dates). Click it to change the type. Wrong types are the most common cause of problems later.
Step 3: Remove what you do not need
- Columns: select the columns to keep, right-click and choose Remove Other Columns. This is safer than deleting columns one by one, because new columns in the source will not sneak in.
- Blank rows: Home → Remove Rows → Remove Blank Rows.
- Top rows such as report titles: Home → Remove Rows → Remove Top Rows.
Step 4: Clean text
- Extra spaces: select text columns, then Transform → Format → Trim.
- Capitalisation: Transform → Format → Capitalize Each Word.
- Wrong values: right-click a column → Replace Values, for example “N/A” with nothing.
Step 5: Split and combine columns
- Split “Ahmed Ali” into first and last name: select the column, then Home → Split Column → By Delimiter and choose Space.
- Combine columns: select them in order, then Transform → Merge Columns and choose a separator.
Step 6: Fill down and remove duplicates
- Fill down: exports often show a value only in the first row of a group, with blanks below. Right-click the column → Fill → Down.
- Remove duplicates: select the column(s) that should be unique, then Home → Remove Rows → Remove Duplicates.
Step 7: Filter and sort
Use the arrow on any column header to filter (for example, keep only the current year) or sort, exactly like an Excel filter. These filters become steps too, so they repeat on every refresh.
Step 8: Load the result and refresh
- Click Home → Close & Load. The clean data appears in a new sheet as an Excel Table.
- When the source file changes, click Data → Refresh All (or right-click the table → Refresh). All your steps run again in seconds.
To change the steps later, double-click the query in Data → Queries & Connections.
Bonus: Combine all files in a folder
If you receive one file per month or per branch with the same layout:
- Put all the files in one folder.
- Go to Data → Get Data → From File → From Folder and choose the folder.
- Click Combine & Transform Data, pick the sheet or table to use, and click OK.
Power Query stacks all files into one table. Add next month’s file to the folder, click Refresh, and it is included automatically.
Common mistakes
- Renaming or moving the source file: the query will not find it. Update the path in Data → Get Data → Data Source Settings.
- Editing the loaded table by hand: manual changes disappear on refresh. Make every change in Power Query instead.
- Skipping data types: set types early, or numbers may be treated as text.
- Too many steps: if you made a mistake, delete that step in Applied Steps (click the X) rather than adding a step to undo it.
FAQ
Do I need to learn the M language?
No. Power Query writes the M code for you as you click. You can view it in Home → Advanced Editor once you are curious.
Is Power Query the same in Power BI?
Yes, Power BI uses the same Power Query editor, so what you learn here works there too.
Does Google Sheets have Power Query?
No. Google Sheets has other tools, but nothing built in that works the same way.
Next steps
- How to make a pivot table in Excel (summarise your clean data)
- XLOOKUP in Excel: the complete beginner guide