Summary

Every month the same export arrives, and every month you fix the same things: the header row sits three rows down, the dates import as text, there are blank rows scattered through it. Power Query records those fixes once and replays them on demand. This article walks one file all the way through — import, clean, load — and then shows you the payoff, which is dropping next month's file into place and clicking Refresh. The steps here are written for Excel for Windows, which has the fullest version of the feature; Excel for Mac and Excel for the web include Power Query with a shorter list of data sources, and Before You Start sets out the difference.

In this guide

• 1. Import the File Instead of Opening It
• 2. Promote the Real Header Row
• 3. Set the Data Type on Each Column
• 4. Remove the Blank and Junk Rows
• 5. Rename and Split the Columns You Need
• 6. Close and Load the Result into a Worksheet
• 7. Edit or Delete a Step in Applied Steps
• 8. Refresh Next Month with One Click

Before You Start

• Excel for Windows, desktop version. Power Query is the Get & Transform Data group on the Data tab in Excel 2016 and later, and in Microsoft 365. Nothing to install.
• Excel for Mac reaches the same feature through Get Data on the Data tab, but supports a shorter list of sources — text and CSV files, Excel workbooks, XML, JSON, SharePoint folders and lists, SQL Server, OData feeds, and a local folder. Excel for the web also has Power Query, with its own source list, and it does not refresh queries that were loaded to the Data Model. Power Query is not supported in Excel for Android or iOS.
• A data file to work with — a CSV export or an Excel workbook. The examples use a monthly sales export named sales-january.csv.
• Edit permission on the workbook. No admin rights required.

Instructions

The steps below follow one file from start to finish. Read them in order the first time; after that, steps 1 and 6 are the only two you repeat.

1. Import the File Instead of Opening It

Bringing the file in through Get & Transform Data gives you a preview and an editor before a single value lands in the sheet.

1. Open Excel and create a blank workbook. Do not double-click the CSV — that route hands Excel free rein over your columns, which is How to Open a CSV File in Excel Without Breaking It in a nutshell.
2. Open the Data tab on the ribbon.
3. In the Get & Transform Data group at the far left, click From Text/CSV. For an Excel file, click Get Data > From File > From Workbook instead — and the same submenu holds From PDF, covered in How to Convert a PDF to an Excel Spreadsheet.
4. Browse to your file, select it, and click Import. A preview window opens with the file name as its title, showing the first 200 rows in a grid and three dropdowns along the top: File Origin, Delimiter, and Data Type Detection.
5. Click Transform Data at the bottom-right of that window. Load would drop the file straight into the sheet unchanged — Transform Data is the one that opens the editor.

The Power Query Editor opens in its own window, separate from Excel. Three areas matter:

• The Queries pane down the left, listing every query in the workbook.
• The data grid in the middle, showing a preview of your file.
• The Query Settings pane down the right, with the Applied Steps list at the bottom of it.

Note: If you opened an Excel workbook rather than a CSV, a Navigator window appears first, listing every sheet and named table in the file. Click one to preview it, then click Transform Data.

2. Promote the Real Header Row

Exports often carry a title row and a blank line above the real column names. Power Query labels those columns Column1, Column2 until you tell it otherwise.

1. Look at the first few rows in the grid. If junk rows sit above your real headings, click Home > Remove Rows > Remove Top Rows, type the number of rows to drop, and click OK. Nothing is deleted from your file — this removes rows from the query's output, and step 7 shows you how to undo it.
2. With the heading row now at the top, click Home, then click Use First Row as Headers in the Transform group.
3. The column headings change from Column1, Column2 to the real names from your file, and a Promoted Headers entry appears in Applied Steps on the right.

Verification: The top row of the grid is now your first record of data, not your headings. If the headings still read Column1, you removed one row too few — see step 7 to adjust.

3. Set the Data Type on Each Column

This is the step that stops dates reading as text and account numbers collapsing into scientific notation.

1. Look at the small symbol to the left of each column heading. ABC means text, 123 means whole number, 1.2 means decimal number, a calendar means date, and ABC/123 means Power Query has not decided.
2. Click the symbol on a column that has the wrong type. A menu of types opens.
3. Choose the right one — Text for order numbers, ZIP codes, and product codes; Decimal Number for amounts; Whole Number for counts; Date for dates.
4. If a Change Column Type dialog asks whether to replace or add, click Replace current. Add new step converts the column twice, and any damage from the first conversion is baked in before the second one runs.
5. For a date column where you cannot tell whether 03/04/2026 is March or April, right-click the heading, choose Change Type > Using Locale, set Data Type to Date, pick the locale that matches the export — English (United Kingdom) for day-first dates — and click OK.
6. Repeat for each column that matters. A Changed Type entry appears in Applied Steps.

Tip: Power Query usually adds its own Changed Type step the moment you import. It guesses from the first 200 rows, so a column that turns numeric on row 900 gets typed wrong. Delete that automatic step (step 7) before setting your own, or your types are applied on top of a bad guess.

4. Remove the Blank and Junk Rows

Subtotal lines, footer notes, and empty separator rows all come through the import, and all of them break a PivotTable later.

1. Click Home > Remove Rows > Remove Blank Rows. Every row where all cells are empty disappears from the preview, and a Removed Blank Rows entry joins Applied Steps.
2. For rows that are not blank but do not belong — a Total: line at the bottom, say — click the small dropdown arrow on the column that identifies them.
3. Clear the (Select All) checkbox, then check only the values you want to keep, and click OK. A filter symbol appears on that column heading and a Filtered Rows entry joins Applied Steps.
4. Check the row count in the status bar at the bottom-left of the editor. It updates as you filter, so you can see how many rows survived.

Removing rows here does not touch the source file, and it is not permanent — it is one line in a recipe you can delete. Step 7 shows how.

Note: To strip duplicate records instead, use Home > Remove Rows > Remove Duplicates. For duplicates in a sheet you already have, How to Remove Duplicate Rows in Excel covers the in-sheet tool.

5. Rename and Split the Columns You Need

Give columns names you will recognise in a formula, and break combined fields apart while you are here.

1. Double-click a column heading, type the name you want, and press Enter. The heading updates and a Renamed Columns entry appears in Applied Steps.
2. To break one column into two — a Last, First name column, for instance — click that column's heading to select it.
3. Click Home, then Split Column in the Transform group, then By Delimiter.
4. In the Split Column by Delimiter dialog, choose the separator from the Select or enter delimiter dropdown — Comma, Space, Semicolon, or Custom for anything else.
5. Under Split at, choose Each occurrence of the delimiter, then click OK. The single column becomes two, named after the original with .1 and .2 appended.
6. Rename both new columns as in the first step of this section.

Note: This is the same job Text to Columns does on a worksheet, with one difference that matters here: doing it in Power Query means it reruns automatically on next month's file. See How to Split Text into Columns in Excel for the one-off version.

6. Close and Load the Result into a Worksheet

The editor holds a preview, not your data. Loading is what puts the cleaned table into the workbook.

1. Click Home, then click Close & Load at the far left of the ribbon, in the Close group.
2. The Power Query Editor closes and Excel returns to the front. Your cleaned data lands on a new worksheet as a formatted Excel table.
3. The Queries & Connections pane opens down the right side of Excel, listing your query by name with the number of rows loaded underneath it.
4. Press Ctrl + S and save the workbook as an .xlsx file. The query is saved inside it.

Tip: To send the data somewhere other than a new sheet, click the arrow under Close & Load and choose Close & Load To. The Import Data dialog lets you pick Table, PivotTable Report, or Only Create Connection, and choose the worksheet and cell it starts in.

Note: Loading hundreds of thousands of rows onto a worksheet makes the workbook large and slow to open. For a source that size, choose Only Create Connection in Close & Load To and build a PivotTable on the connection instead — the rows stay out of the sheet. How to Shrink a Large Excel File covers what else inflates a workbook.

7. Edit or Delete a Step in Applied Steps

Applied Steps is a recipe, written down in order, and every line of it can be changed or thrown away.

1. Click any cell in the loaded table. A Query tab appears on the ribbon. Click it, then click Edit. The Power Query Editor reopens on that query.
2. Read the Applied Steps list in the Query Settings pane on the right. It runs top to bottom: Source, Promoted Headers, Changed Type, and so on.
3. Click any step in the list. The grid shows the data exactly as it stood at that point, which is how you find the step that went wrong.
4. Click the small x to the left of a step to delete it. Deleting a step changes the recipe only — your source file is untouched and the rows come back the moment the step is gone.
5. Click the gear icon on the right of a step to reopen the dialog that created it, change a setting, and click OK.
6. Drag a step up or down the list to reorder it, or right-click a step and choose Rename to give it a name that means something.
7. Click Home > Close & Load to save your changes back to the worksheet.

Note: Deleting a step in the middle can leave a later step pointing at a column that no longer exists. Power Query shows a yellow bar naming the missing column. Delete the broken step as well and add it again — there is nothing to recover, because the source file was never changed.

8. Refresh Next Month with One Click

This is the whole point of the previous seven steps.

1. When next month's export arrives, save it over the old file — same folder, same filename. Power Query looks for the exact path you gave it in step 1.
2. Open your workbook.
3. Open the Data tab and click Refresh All in the Queries & Connections group. Excel reruns every step in order against the new file.
4. Watch the Queries & Connections pane on the right. The query shows a spinning indicator, then reports the new row count when it finishes.
5. Check the table. Headers promoted, types set, blanks gone, columns split — all of it done, none of it repeated by hand.

To refresh one query rather than all of them, click a cell in its table, then click Query > Refresh on the ribbon.

If the new file has a different name or lives somewhere else, point the query at it instead of renaming the file:

1. Click any cell in the table, then click Query > Edit.
2. Click the gear icon beside the Source step at the top of Applied Steps.
3. Browse to the new file, click Open, then click Close & Load.

Tip: To have the query refresh itself every time the workbook opens, right-click the query in the Queries & Connections pane, choose Properties, check Refresh data when opening the file, and click OK.

That is the setup done. You pay for the cleanup once, and every file after it costs one click.

Troubleshooting

INFO

Power Query never writes to your source file. Removing rows, changing types, and splitting columns all happen on a copy inside your workbook, and the recipe that produced them is visible and editable in Applied Steps. Nothing you do in the editor can damage the export you imported.

Symptom / Error	Potential Cause	Solution
From Text/CSV and Get Data are missing from the Data tab	Excel 2013 or earlier, or an Excel edition without the connector	Excel 2016 and later include it as standard. On Excel 2013, use Data > From Other Sources. To move to a current build, raise a ticket saying "I need Excel upgraded to Microsoft 365 for the Get & Transform Data connectors" and include your version from File > Account.
DataFormat.Error: We couldn't convert to Number	A column typed as a number contains text such as N/A	Click the step's gear icon and retype the column as Text, or click Home > Remove Rows > Remove Errors to drop the offending rows.
Refresh fails with Could not find file	The source file was renamed, moved, or is on a drive you are not connected to	Click Query > Edit, click the gear beside Source, and browse to the file's current location. Reconnect to the network drive first if that is where it lives.
Columns arrive as Column1, Column2 after a refresh	The new file has a different number of junk rows above the headings	Open the query, click the gear beside Removed Top Rows, and correct the number for the new file.
A step shows a yellow bar naming a column	A column was renamed or dropped in the source file	Delete the broken step and add it again against the new column name.
The query loaded but every number is left-aligned	Columns were imported as text	Reopen the query and set the type on each numeric column (step 3), then Close & Load.
Refresh does nothing in Excel for the web	The query was loaded to the Data Model	Excel for the web does not refresh Data Model queries. Refresh the workbook in Excel for Windows or Mac.

Related Articles

• How to Open a CSV File in Excel Without Breaking It
• How to Split Text into Columns in Excel
• How to Remove Duplicate Rows in Excel
• How to Create a PivotTable in Excel

Did this solve your problem?