To clean data in Excel with Power Query, select your source data, choose Data > From Table/Range, make cleanup changes in the Power Query Editor, then select Home > Close & Load. The result is a repeatable query: when the source data changes, refresh the query to apply the same cleanup steps again. This guide covers Excel for Microsoft 365 on Windows.
What You’ll Need
- Excel for Microsoft 365 on Windows, with the workbook open.
- A worksheet containing the data you want to clean. A single header row and one kind of record per row make the job easier.
- A backup or an untouched copy of the original data, especially if the data is important. Power Query creates a cleaned output rather than editing the source, but preserving the source makes it easier to compare results.
- A clear idea of which values are valid. For example, decide whether blank cells should remain blank, become a label such as “Unknown,” or cause the row to be excluded.
Power Query is built into current desktop versions of Excel for Windows. In Excel for Mac, Power Query is available in Microsoft 365, but the available connectors and editing features can differ; menu labels may also vary. Excel for the web does not provide the same full query-authoring experience, so use desktop Excel for these steps.
Step-by-Step
- Prepare the source range. Open the worksheet and check that each column has a descriptive heading, such as Order Date or Email. Remove completely empty rows above the headings, and avoid merged cells in the data area. Click a cell in the data and press Ctrl+T to format it as an Excel table. In the Create Table dialog, confirm the range and select My table has headers if the first row contains column names, then click OK. A table gives Power Query a clearly defined source. If your data is already an Excel table, you can use it as it is.
- Open the Power Query Editor. Click a cell inside the table and choose Data > From Table/Range. If the data is an ordinary range, Excel may first ask to create a table; confirm the range and header setting. The Power Query Editor opens with a preview of the data. The original worksheet is not being cleaned in place: the transformations you make will be recorded as query steps and applied to the output when you load or refresh it.
- Check headers and remove irrelevant rows or columns. Confirm that the column names are in the header row. If the first row is data rather than headers, select Home > Use First Row as Headers only when that is appropriate. Select a column you do not need, right-click its heading, and choose Remove. To remove multiple columns, hold Ctrl while selecting their headings. To discard completely blank records, choose Home > Remove Rows > Remove Blank Rows. Preview the result: this command removes rows that Power Query recognizes as blank, not rows that merely have one empty field.
- Standardize text spacing and characters. Select a text column, then choose Transform > Format > Trim to remove spaces at the beginning and end of values. Use Transform > Format > Clean to remove nonprinting characters. These are useful for names, product codes, and imported text where entries may look identical but contain hidden spaces or control characters. Apply the operations to other text columns as needed. Do not apply text formatting operations to dates or numbers; set their types in a later step.
- Fix inconsistent labels and split combined fields. To standardize a recurring spelling or label, select the column and choose Transform > Replace Values. Enter the value to find and its replacement, then inspect the preview to make sure the replacement is not too broad. To separate a field such as “Lee, Morgan,” select the column and choose Transform > Split Column > By Delimiter. Pick the comma as the delimiter and choose how to split the column, such as at each occurrence or at the left-most delimiter. Review the new columns and rename them by double-clicking their headings. If a column contains several different patterns, correct or separate those patterns deliberately rather than assuming one delimiter will handle every row.
- Set and verify data types. Check the small type icon at the left of each column heading. Select a column and use Transform > Data Type to assign the right type, such as Text, Whole Number, Decimal Number, Date, or Date/Time. Identifiers like ZIP codes, account numbers, and product IDs are usually better kept as Text, because converting them to numbers can remove leading zeros. For dates or decimals that are interpreted incorrectly because of regional settings, choose Transform > Data Type > Using Locale, then specify the intended type and locale. Check several preview values, including unusual ones, before proceeding.
- Handle missing values and errors intentionally. Use the filter arrow on a column to inspect blanks or particular values. Decide whether to keep, replace, or exclude them based on what the data means. To replace a known placeholder such as “N/A,” select the column and choose Transform > Replace Values; replacing it with a blank is not the same as removing the entire row. If a column contains errors, select it and choose Home > Remove Rows > Remove Errors only when dropping those records is acceptable. Otherwise, correct the cause or replace errors with a meaningful value. Removing errors can hide data problems, so inspect the affected column and source before choosing.
- Remove duplicates with the right columns selected. Decide what counts as a duplicate before using the command. To remove identical records, select all relevant columns. To keep only one record for each customer ID, select just the ID column. Then choose Home > Remove Rows > Remove Duplicates. Power Query keeps one occurrence based on the selected columns; it does not automatically decide which record is most current or most complete. If you need to keep a specific record, sort or otherwise define that rule before removing duplicates, and verify the output afterward.
- Review the recorded steps. Look at Applied Steps in the Query Settings pane. Select earlier steps to see how the preview looked at that point, and check that later changes still make sense. Rename the query in the Query Settings pane to something recognizable, such as Cleaned Orders. Scan the final preview for unexpected blanks, errors, changed values, or columns that have the wrong type. The preview is not a substitute for checking the loaded result, but it is a useful place to catch transformation mistakes.
- Load the cleaned data and test a refresh. Choose Home > Close & Load to put the result in a new worksheet as a table. If you need a different destination or want a connection without a worksheet table, use Home > Close & Load > Close & Load To and choose an available load option. Check the output against the original, especially row counts and key fields. To rerun the transformations after changing the source, choose Data > Refresh All. Add new records inside the original source table so the query can include them; records typed below the table but outside it may not be part of the source.
Common Mistakes
Changing the source instead of the query output: Power Query normally leaves its input data intact and loads a separate result. Editing the source table and editing the loaded result are not interchangeable; refresh can replace changes made directly to the output.
Removing too many rows: A blank in one column does not necessarily make a record useless. Before filtering out blanks or errors, consider whether the rest of the row contains information you need. Keep a copy of the source so you can restore or investigate excluded records.
Converting identifiers to numbers: A value like 00427 may become 427 if it is treated as a number. Set codes and IDs to Text when their digits are labels rather than quantities. Similarly, check date interpretation when the source uses a different date order from your regional settings.
Removing duplicates using the wrong selection: Selecting only a name column may collapse separate people with the same name. Selecting every column may leave records that differ only by a timestamp. Define the matching rule first, then select the columns that represent it.
Expecting cleanup to apply to cells outside the source: A query only processes its defined source. If the table does not include new rows or columns, refresh will not automatically clean them. Confirm the source range or table contents when the output seems incomplete.
Tips
- Make one kind of change at a time and give the query a meaningful name. Clear steps are easier to maintain than a series of unexplained edits.
- Keep the original imported columns until you are confident the cleaned result is right. You can remove them later, but retaining them during review helps you compare before-and-after values.
- Use filters in the preview to investigate unusual values, but remember that a filter added as a query step will keep excluding those values on future refreshes. Remove the filter step if it was only for inspection.
- For a recurring process, keep the source table and the query output in clearly labeled locations. Add new source records to the table, then use Data > Refresh All rather than repeating each cleanup manually.
- Check both sample records and totals. Compare the number of source and output rows, and spot-check important columns such as dates, IDs, and amounts. Row counts may legitimately change if you removed blank rows, duplicates, or errors, so know which steps explain the difference.
Troubleshooting
From Table/Range is unavailable: Click a cell inside the data first. If the range has merged cells or inconsistent headings, fix those issues and try again. You can also create a table with Ctrl+T before opening the query. On a Mac, use the Data tab and look for the Power Query or Get Data options available in your Excel build; connector and editing support varies by version.
Dates or numbers turn into errors: The source may use a different decimal separator, date order, or locale from Excel. In the Editor, select the column and use Transform > Data Type > Using Locale to choose the intended format. If the source contains mixed formats, filter for errors and inspect the original values rather than repeatedly changing the type without checking.
Refresh does not include new records: Confirm that the records were added inside the source Excel table, not merely below it. Then select Data > Refresh All. If the source is a file or another external connection, check that its location has not changed and that Excel can access it. Review the query’s source step if the data location or table name has changed.
The output changes after refresh: This is expected if the source data changed or if a query step removes or reshapes records. Open the query from Data > Queries & Connections, double-click it, and review Applied Steps to find where the difference begins. Avoid typing corrections directly into the loaded output; update the source or the query step instead.
Some rows disappear unexpectedly: Look for filter, remove duplicates, remove blank rows, or remove errors steps in Applied Steps. Select each step in order and check the preview. If a step was added accidentally, use the X beside that step in Query Settings to remove it, then verify the result before loading again.
Frequently Asked Questions
Does Power Query change my original Excel data?
Usually, no. It reads from the source and loads a transformed result separately. The source changes only if you edit it yourself or use another process that writes to it. Treat the query output as refreshable, because refreshing can replace manual edits made there.
Can I undo a Power Query cleanup?
While editing a query, you can remove or adjust steps in Applied Steps. After loading, you can reopen the query and revise it; you do not need to repeat every transformation. Ctrl+Z in the worksheet does not work like an undo history for query steps, so use the Editor to change the query.
Will the cleanup run automatically when data changes?
Not necessarily. You can choose Data > Refresh All to run the query again. Excel also offers refresh settings for some connections through query properties, but automatic refresh behavior depends on the connection and workbook settings. A normal workflow is to update the source table and refresh when needed.
Can Power Query clean multiple files with the same layout?
Yes. In Excel for Windows, Data > Get Data includes options for importing from a folder and combining files. This works best when the files have consistent columns and structure. Combining files is a separate setup from cleaning one table, so check the sample file and confirm that the combined output includes the expected records.
Should I use formulas or Power Query?
Use formulas when you need results to recalculate directly alongside worksheet data or want a cell-by-cell calculation. Power Query is useful for repeatable import and cleanup tasks, such as standardizing columns and combining records before analysis. You can use both: load a cleaned table with Power Query, then add worksheet formulas or analysis on a separate sheet.
Related
- How to Change Note Color in Excel for Easier Scanning
- How to Change a Link Name in Excel
- How to Change Every Other Row Color in Excel Automatically
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Shortcut to Select All Data in a Column QuicklyIn Excel for Microsoft 365 on Windows, click a cell in the column and press Ctrl+Shift+Down Arrow to…
- How to Add Page Numbers to Each Page in WordTo number every page in Microsoft Word, open the document and select Insert > Page Number, choose a…
- How to Use Styles in Word for Clear, Consistent DocumentsTo use styles in Word, select the text or place your cursor in a paragraph, then choose a…
- How to Insert a Line Break in Microsoft Word: Quick StepsTo insert a line break in Microsoft Word without starting a new paragraph, place the cursor where the…
- Monthly Budget Tracker Template for Google Sheets?A monthly budget tracker template for Google Sheets can be built from a transaction log, a monthly category…
- How to Create Inbox Rules in Outlook on Windows and WebTo create an inbox rule in Outlook, open the rules settings, choose a condition such as who sent…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.