How to Combine Multiple Excel Files into One Using Power Query (Complete Guide)

If you have ever spent an afternoon manually copying data from a dozen Excel files into a single master spreadsheet, you already know the problem. It is slow, error-prone, and the moment a new file lands in your inbox, you start the whole process over again. Power Query solves this entirely. Once you set it up, combining new files takes a single click.

This guide covers the complete process from start to finish, including the part most tutorials skip: what to do when your files have mismatched column headers. That is the scenario that breaks most setups, and by the end of this article you will know exactly how to handle it.

Quick Answer

To combine multiple Excel files into one, place all your source files in a clean folder, then go to Data > Get Data > From File > From Folder in Excel. Choose Transform Data, filter the folder to include only .xlsx files if needed, click the Combine Files icon in the Content column, select the correct sheet or Table, and load the result. Once the query is built, you can add new files to the folder later and simply click Refresh to update the combined table automatically.

Best setup for reliable results:

• Use the same Excel Table name in every source file
• Keep column headers consistent across files
• Avoid extra title rows above the headers
• Do not save the output workbook inside the source folder

What Is Power Query and Why Use It for This?

Power Query is Excel’s built-in data transformation engine. It has been part of Excel since 2016, available on the Data tab under Get and Transform Data. In Excel 2010 and 2013, it existed as a free add-in under that exact name.

The traditional way to combine Excel files involves opening each one, copying the data, and pasting it below the previous batch. Some people write VBA macros to automate this. Power Query is better than both approaches for a straightforward reason: it records every step you take as a reusable, refreshable query. The next time you need to add a new file to the pile, you drop it in the folder and click Refresh. Power Query does the rest.

There are no formulas to break, no macros to maintain, and no manual copying. The result is a clean, connected table that updates automatically.

Who should use this method: Anyone who receives periodic reports from multiple sources in Excel format, whether that is monthly sales data by region, weekly timesheets from different departments, quarterly budget files from branch offices, or any other situation where the same structure repeats across multiple workbooks.

Power Query availability by Excel version:

Excel Version	Power Query Location
Excel 2010 / 2013	Available as a free add-in from Microsoft
Excel 2016 / 2019	Data tab > Get and Transform Data
Excel 2021	Data tab > Get and Transform Data
Microsoft 365	Data tab > Get and Transform Data (fully up to date)
Microsoft 365 for Mac	Available with Power Query support (some features still differ from Windows)

Before You Start: Preparing Your Files

The quality of your output depends heavily on how you prepare your source files. Skipping this step is where most people run into problems.

Use a dedicated folder. Power Query’s folder connector pulls in every compatible file it finds in the folder you point it at. If your folder contains stray files, old backups, or files with different structures, those will be included in the combine operation. Create a clean folder and put only the files you want to combine inside it. Do not put your output workbook inside the same folder.

Make sure your navigation target is consistent. If you connect to worksheets directly, the sheet names should match across files. If one file uses “Sheet1” and another uses “January Sales,” the default navigation step may fail. A more reliable approach is to format the data as an Excel Table and use the same Table name across all files. Names like “SalesData” or “ReportData” are much more stable than default sheet names.

Format the data as an Excel Table. In each source file, select your data range and press Ctrl+T to convert it to a Table (Insert > Table also works). Give the Table a consistent name, such as “SalesData,” across all files. Using a named Table rather than a plain range is the most reliable way to ensure Power Query picks up the right data every time, especially if your files have different amounts of data.

Make sure headers are in row 1. Power Query treats the first row as the header row by default. If your files have title rows, logo rows, or report-date rows above the actual column headers, you will need to skip those rows during the transformation step. It is cleaner to remove them from the source files if you can.

Consistent column names matter, but there is a workaround. Power Query matches data by column name, not by column position. If one file calls a column “Customer” and another calls it “Client,” Power Query will treat them as two different columns and leave one full of nulls. If you control the source files, standardize the headers now. If you do not control them, the section on mismatched headers later in this article covers the fix.

Save all files in the same format. Mixing .xlsx and .xls files in the same folder will cause problems. Standardize on .xlsx.

Step-by-Step: Combine Multiple Excel Files with the Folder Connector

The following steps assume your files are identical in structure. If they have mismatched headers, complete these steps first and then continue to the mismatched headers section.

Step 1: Open a New Workbook

Open a blank Excel workbook. This will be your output workbook, the one that holds the combined data. Do not place this file inside the same folder as your source files.

Step 2: Connect to the Folder

Go to the Data tab on the ribbon. Click Get Data, then choose From File, then From Folder.

A dialog box will appear asking you to enter or browse to a folder path. Click Browse, navigate to the folder containing your source files, and click OK.

Excel will scan the folder and show you a preview listing every file it found. Before you do anything else, look at this list. Verify that only the files you want are shown. If you see unexpected files, close this dialog, clean up the folder, and start again.

Step 3: Choose “Transform Data”

At the bottom of the preview dialog you will see three buttons: Combine, Transform Data, and Cancel.

Do not click Combine yet. Click Transform Data instead.

This opens the Power Query Editor and shows you the full file list as a table. This step is important because it gives you the chance to filter the list before combining. For example, you can filter the Extension column to include only .xlsx files, which protects your query if someone accidentally drops a PDF or a .csv into the folder later.

Step 4: Filter by File Extension (Optional but Recommended)

In the Power Query Editor, you will see columns including Name, Extension, Date accessed, Date modified, Date created, Attributes, and Content.

Click the dropdown arrow on the Extension column header. Uncheck everything except .xlsx (or whichever extension your files use). Click OK.

This step future-proofs your query. It means that even if non-Excel files end up in the folder, they will be filtered out and will not cause errors.

Step 5: Combine the Files

Now look at the Content column. Each row in that column shows the word “Binary,” which represents the raw contents of each file. At the top of the Content column header, you will see a small icon that looks like two overlapping rectangles with a downward arrow. This is the Combine Files button.

Click that icon.

The Combine Files dialog will open. Power Query reads the first file in the list (alphabetically by default) to use as the sample file. It displays the structure of that file so you can tell it where the data lives.

In the left panel, you will see the workbook structure: sheets, tables, and named ranges. Click on the sheet name or Table name that contains your data. If you named your Table “SalesData,” click on “SalesData.” A preview of the data from the first file will appear on the right.

If the first file in the alphabetical list is not the most complete or representative file, you can change the Sample File using the dropdown at the top of this dialog. Choose the file that best represents the structure you want.

Click OK.

Step 6: Review What Power Query Built

Power Query will now process all your files. When it finishes, you will be back in the Power Query Editor with a table showing all the combined data.

Look at the Queries panel on the left side. You will notice that Power Query automatically created several helper queries, all organized in a group:

Transform File from [your folder name] is the function that Power Query applies to each individual file. It knows how to open a file and extract the right sheet or table.

Transform Sample File is a query built from the first file. When you modify this query, those changes are applied to every file automatically.

Sample File is a parameter that holds a reference to the example file.

Your main combined query is at the top, outside the group. This is where the final combined table lives.

Step 7: Add the Source File Name Column (Recommended)

One of the most useful things you can do at this stage is keep track of which file each row came from. Power Query automatically renames the file name column to “Source.Name” during the combine process. Look for this column in your combined table. If it is there, keep it. If you later want to filter by source file or troubleshoot data issues, this column is invaluable.

If you do not need the source file name in your final output, you can remove it now by right-clicking the column header and choosing Remove.

Step 8: Clean Up the Combined Table

At this point your data is combined, but it may need some tidying. Common things to check:

Data types: Power Query sometimes guesses data types incorrectly, especially dates and numbers. Click on the data type icon (the letters or symbols) on the left side of each column header to set the correct type. Dates should be set to Date, numeric values to Whole Number or Decimal Number, and text to Text.

Remove unnecessary columns: If any columns came through that you do not need, right-click the header and choose Remove.

Rename columns: Double-click any column header to rename it.

Filter blank rows: If any source files had empty rows at the bottom, those may have carried through. Filter out nulls in your key columns to remove them.

Step 9: Close and Load

When your data looks right, go to the Home tab in the Power Query Editor and click Close and Load.

If you want to load the data as a regular table on a worksheet, choose Close and Load. If you want to control exactly where it goes (a specific sheet, or as a PivotTable connection), choose Close and Load To… and configure the destination.

Excel will load all the combined data into your workbook. You now have a live, refreshable table that combines every file in your folder.

Step 10: Refresh When New Files Arrive

The next time a new source file lands in your folder, simply drop it in. Then in Excel, right-click anywhere in the combined table and choose Refresh, or go to Data > Refresh All. Power Query will re-scan the folder, pick up the new file, and add its data to the table automatically.

The Part Most Guides Skip: Handling Mismatched Column Headers

This is where most tutorials stop, and it is also the most common reason people get stuck. Here is the problem in plain terms.

Power Query matches columns by name. When it combines your files, it creates a column for every unique header name it encounters across all files. If one file has a column called “Customer” and another has a column called “Client,” your final table will have both columns, with each file’s data sitting in its own column and nulls everywhere else.

The result looks like this: rows from File A have data in “Customer” but nulls in “Client,” and rows from File B have data in “Client” but nulls in “Customer.” You effectively lost half your data.

There are three main scenarios and a solution for each.

Scenario 1: You Can Standardize the Headers

This is the easiest fix and the right answer if you have any control over the source files. Open each file, rename the inconsistent headers to match, and save. If “Customer” and “Client” both mean the same thing, pick one and use it everywhere.

Once you have done this, run the folder combine process above and everything will align correctly.

If you are dealing with files you receive from external sources and cannot edit, continue to the scenarios below.

Scenario 2: The Column Content Is the Same, Only the Name Is Different

This is the most common real-world case. The data represents the same thing (a customer name, a region, a product ID), but whoever created the files used different labels.

The approach here is to rename the inconsistent column inside the Transform Sample File query. Here is how to do it step by step.

After you have completed the standard combine steps above and can see the combined data, look at the Queries panel on the left. Click on Transform Sample File. This opens the query that Power Query applies to each file.

In the Transform Sample File query, you will see the structure of your sample file. Find the column with the inconsistent name (in this example, “Customer”). Right-click the column header and choose Rename. Change it to the standardized name you want to use across all files (for example, “Client”).

Because Transform Sample File is linked to the function that runs on every file, this rename will be applied during the combine process before the rows are stacked together. However, a normal rename step can fail if a file does not contain the column you are trying to rename. The safest approach is to use a conditional rename with MissingField.Ignore.

Use this M code instead:

= Table.RenameColumns(
#"Previous Step",
{{"Customer", "Client"}},
MissingField.Ignore
)

This tells Power Query to rename “Customer” to “Client” only when the “Customer” column exists. Files that already use “Client” will continue working without errors.

Go back to your main combined query and click Refresh. The nulls should be gone and the data should align correctly.

Important: When you rename inside Transform Sample File, you must make sure the sample file you chose in the Combine Files dialog is one that has the column name you want to rename. In the example above, if the sample file already calls the column “Client,” you will not see a “Customer” column to rename in Transform Sample File. Choose your sample file strategically.

Scenario 3: Different Files Have Different Columns (Extra or Missing Columns)

This is the more complex case. Some files have columns that others do not. For example, your January file has columns for Region and District, but your February file dropped District and added a Territory column.

When this happens, Power Query’s default behavior is to create a column for every unique header name it has ever seen across all files. Files that do not have a particular column will show nulls in that column’s rows. This is actually the correct behavior if you want all possible columns in your output. The null values accurately represent missing data.

However, problems arise when a column exists in one file but is named slightly differently in another, because then you get both columns with split data.

The solution is to edit the Transform Sample File query to handle the renaming or normalization logic before the files are combined. Here is the general approach:

In Transform Sample File, add a step that renames all known column variants to a single standard name. For example, if you know that some files say “Territory” and others say “District” and they mean the same thing, add a Rename step inside Transform Sample File that renames “District” to “Territory.” Use the Try/Otherwise pattern in M code to make this rename conditional, so the query does not error out on files where “District” does not exist.

The M code for a conditional rename looks like this:

= Table.RenameColumns(
#"Previous Step",
{{"District", "Territory"}},
MissingField.Ignore
)

The third argument, MissingField.Ignore, tells Power Query not to throw an error if the column it is trying to rename does not exist in a particular file. Without this argument, the step would fail on any file that already uses “Territory” and has no “District” column.

To add this step manually in the Advanced Editor, go to View > Advanced Editor inside Transform Sample File and add the rename step into the M code chain.

After adding this step, go back to your main combined query and refresh. Files that had “District” will now contribute to the “Territory” column, and files that already used “Territory” will continue to work correctly.

Scenario 4: Completely Unknown Column Structures

Sometimes you are combining files where you genuinely do not know in advance what all the column names will be. New columns might appear in future files. In this situation, the safest approach is to tell Power Query not to lock down column names during the combine process.

The issue is that the automatically generated Changed Type step at the end of the main combined query hardcodes specific column names. If a future file introduces a new column, that step may error out.

To prevent this, click on the Changed Type step in the Applied Steps panel of your main combined query and delete it. Then set data types manually only on the columns you are certain will always exist, using a separate step that uses MissingField.Ignore.

Alternatively, remove the Changed Type step entirely and set data types after loading the data to Excel as a final manual step. This gives you maximum flexibility at the cost of some automation.

How the Helper Queries Work (And Why You Should Understand Them)

When Power Query sets up the folder combine, it creates several automatic queries in the background. Most people ignore these, but understanding them makes troubleshooting much easier.

Sample File is a parameter query. It holds a reference to the first file, the example Power Query used to understand the structure. You can open this parameter and change which file it references, which is useful when the first file alphabetically is not representative of your full dataset.

Transform Sample File is the blueprint. Every step you see in this query is applied to every file in the folder. This is where you make any structural changes, such as skipping header rows, renaming columns, removing blank rows at the top, or changing data types.

Transform File from [folder name] is the function Power Query auto-generated from Transform Sample File. You should not edit this directly. Edit Transform Sample File instead, and this function updates automatically.

The main combined query is the final output. It calls the function on each file’s Content binary, combines the results by stacking all the rows, and then adds any final steps like removing columns you do not need.

The relationship between Transform Sample File and the main query is important: anything you do to Transform Sample File is done to every individual file before the stacking happens. Anything you do in the main combined query is done to the stacked result as a whole.

Common Problems and How to Fix Them

Problem: The query pulls in extra files I did not want.
Fix: In the Power Query Editor, add a filter on the Extension column (to include only .xlsx) and a filter on the Name column if specific file names should be excluded. You can also filter by Date modified if you only want recent files.

Problem: I see “Column1,” “Column2,” etc. instead of my real headers.
Fix: Your source files do not have proper header rows, or Power Query is not recognizing them. In Transform Sample File, check whether there is a “Promoted Headers” step. If not, click Transform > Use First Row as Headers. If there are extra rows above your headers, add a step to remove those rows first (Home > Remove Rows > Remove Top Rows) before promoting headers.

Problem: The query errors out with “The key didn’t match any rows in the table.”
Fix: This error usually means Power Query is looking for a specific sheet name or table name that does not exist in one of your files. Go to Transform Sample File and look at the navigation steps. Power Query is hardcoding the sheet or table name it found in the sample file. If not all your files use that exact name, you need to make them consistent or modify the M code to use a more flexible approach, such as navigating to the first sheet rather than by name.

Problem: I have a “Changed Type” step that is causing errors when I add new files.
Fix: Delete the Changed Type step from the main combined query. Power Query adds this step automatically and hardcodes column names. If a new file has a different column structure, this step will fail. After deleting it, add a new data type step that only targets the columns you are certain will always be there, and use MissingField.Ignore as the third argument in the M code.

If your query works initially but starts behaving unpredictably after refreshes, especially after source files change structure, you may also want to read my guide on why Power Query refreshes sometimes appear to “lose” data and how to fix the underlying causes permanently.

Problem: My output table has duplicate rows.
Fix: This usually happens when one of the source files was saved with data on multiple sheets and Power Query picked up data from more than one sheet. Check the Transform Sample File query and make sure the navigation step points specifically to the correct sheet or Table, not to the workbook root.

Problem: Date columns are coming through as numbers.
Fix: Excel stores dates as serial numbers internally. When Power Query loads the data, it sometimes interprets dates as whole numbers. In Transform Sample File, click on the data type icon in the date column header and change it to Date. This will be applied to all files.

Problem: I renamed a column in my source file and now the query breaks.
Fix: Power Query records the original column names as text strings inside the Applied Steps. If you rename a column in the source file after the query is built, Power Query can no longer find it. Open the affected step in the Advanced Editor, locate the old column name as a text string, and update it to match the new name.

Problem: Power Query is picking up hidden files like “~$filename.xlsx”.
Fix: When an Excel file is open, Windows creates a temporary file with a tilde prefix. This file will be picked up by the folder connector and will cause errors. In Power Query, filter the Name column to exclude any file names that start with “~$”. You can do this by right-clicking the Name column, choosing Text Filters > Does Not Begin With, and entering “~$”.

Adding the Source File Name to Your Combined Table

Knowing which file each row of data came from is often essential, especially when you are combining dozens of files and need to trace a data issue back to its origin.

Power Query preserves the source file name automatically when you use the Folder connector. After the combine step, look for a column called “Source.Name” in your combined table. This column contains the full file name (for example, “Sales_January.xlsx”) for every row.

You can use this column to:

Filter the combined table to rows from a specific file. Add a calculated column in Power Query that extracts a meaningful label from the file name (for example, extracting “January” from “Sales_January.xlsx”). Build a PivotTable that lets you slice the data by source file.

To extract just the month name from a file name like “Sales_January.xlsx,” add a custom column in the Power Query Editor with this M code:

= Text.BetweenDelimiters([Source.Name], "_", ".")

This pulls out the text between the underscore and the period, giving you “January,” “February,” and so on. This technique lets you create a clean period label column from your file names without adding a manual column to each source file.

Refreshing Automatically with a Scheduled Connection

Once your Power Query setup is in place, refreshing it manually is one click. But if you want the workbook to update on a schedule without any manual action, you have a couple of options.

In Excel on Windows: If the workbook is saved to a SharePoint or OneDrive location and opened in Excel desktop, you can set it to refresh on open. Go to Data > Queries and Connections, right-click your query, choose Properties, and enable “Refresh data when opening the file.” This way, every time you open the workbook, Power Query pulls in the latest files from the folder.

With cloud-based automation tools: Fully automated scheduled refreshes are possible, but the setup depends heavily on where your workbook and source files are stored. If your files live on SharePoint or OneDrive, you may be able to build a refresh workflow using Power Automate, Power BI, or related Microsoft services. For most Excel users, enabling refresh on open is the simplest and most reliable approach.

With Excel’s Refresh All on file open: If your team always opens the workbook at the start of the day, enabling refresh on open is the simplest solution and requires no additional setup.

Best Practices for Long-Term Maintenance

Document your folder path. If you or a colleague moves the source folder, the query will break. Add a comment or note in the workbook explaining where the source folder is and what naming conventions the files should follow.

Standardize source file names. File names like “Sales_2024_01.xlsx” are much easier to work with than “January sales FINAL (2).xlsx.” If the files are generated by a system, look at whether you can standardize the export file naming.

Test with a small batch first. Before pointing the connector at a folder with 200 files, test with two or three files. Confirm the structure looks right, the headers align, and the data types are correct. Then add all the files and refresh.

Keep the output workbook separate. Never save your output workbook inside the source folder. Power Query will try to process it as a source file, which will either cause an error or pull in its own output data as input, creating a circular mess.

Version control your Transform Sample File. If you have made complex modifications to Transform Sample File, copy the M code out of the Advanced Editor and save it somewhere. If the query gets corrupted or you need to rebuild it, having that code saved means you do not have to start from scratch.

Use named Tables in source files. Named Tables are more stable than sheet-level references. If you navigate to a named Table called “SalesData,” Power Query will find it even if the sheet name changes. Navigation by sheet name breaks the moment someone renames the sheet.

Frequently Asked Questions

Does Power Query combine files from subfolders too?
Yes. By default, the Folder connector scans not just the folder you specify, but all subfolders within it. If you want to exclude subfolders, you need to filter the Folder Path column in Power Query after choosing Transform Data. Add a filter to the Folder Path column to include only the exact folder path you want, which excludes any deeper subfolder paths.

How many files can Power Query handle?
There is no hard limit imposed by Power Query itself. In practice, performance depends on the size of each file and the processing power of your machine. Combining a hundred small files with a few thousand rows each typically runs in seconds. Combining fifty files with hundreds of thousands of rows each can take several minutes. For very large datasets, consider whether Power Query combined into a single Excel file is the right tool, or whether a proper database or Power BI would serve you better.

Can I combine Excel files stored on SharePoint or OneDrive?
Yes. Instead of using Get Data > From File > From Folder, use Get Data > From File > From SharePoint Folder. You will need to enter the root URL of your SharePoint site. The process from that point is nearly identical to the local folder approach. Note that large SharePoint libraries can be slow to query, especially during the initial connection.

What if the files are on a network drive?
Network paths work fine. Use the full UNC path (\servername\sharename\foldername) when browsing for the folder. If the query will be shared with other users, be aware that the path is stored as text in the query and everyone who uses the workbook will need access to that network path.

Why does Power Query use the first file alphabetically as the sample file? Can I change it?
Power Query uses the first file in the folder alphabetically as the sample file by default. This matters because the sample file determines what sheet name and structure Power Query expects to find in all other files. You can change the sample file in the Combine Files dialog by selecting a different file from the Sample File dropdown before clicking OK. You can also change it later by editing the “Sample File” parameter query in the Queries panel.

Can I combine files with different numbers of columns?
Yes, and Power Query handles this gracefully by creating a column for every unique header name it encounters. Files that do not have a particular column will show null values in that column’s rows. This is the correct behavior. The issue only arises when the same data appears under different column names in different files, which is the mismatched headers scenario covered in detail above.

Can I combine files that are already open?
It is best not to. When an Excel file is open, Windows often creates a temporary file that starts with ~$. Power Query may try to import this temporary file and generate errors unless you filter those files out. For the most reliable refresh experience, close the source files before refreshing the query or add a filter that excludes file names beginning with ~$.

How do I stop Power Query from importing the header row as a data row?
This happens when Power Query does not automatically detect that the first row should be headers. In the Power Query Editor, select the Transform Sample File query, then go to Transform > Use First Row as Headers. This step will be applied to every file. If the headers are not in row 1 (because there are extra rows above them), first add a Remove Top Rows step to eliminate those rows before promoting headers.

Can I combine CSV files and Excel files in the same folder?
Not cleanly with the standard approach. The Combine Files dialog expects all files to be the same type. If your folder has both .csv and .xlsx files, filter the Extension column to include only one type. If you genuinely need to combine both file types into one output, you can build two separate folder queries (one for CSV, one for Excel), transform each into the same column structure, and then use Append Queries to combine the two query results.

What is the difference between Append and Merge in Power Query?
Append stacks tables on top of each other, adding rows. This is what you use when combining multiple files with the same structure, such as monthly sales files. Merge joins two tables side by side based on a matching key column, similar to VLOOKUP or a SQL JOIN. For the multi-file combine scenario this article covers, Append is the right operation. Merge is used when you want to enrich one table with columns from another based on a shared identifier.

Will the query break if I move the source folder?
Yes. The folder path is stored as a text string inside the query. If you move the source folder, the query will fail with a folder not found error. To fix it, go to Data > Queries and Connections, right-click your query, choose Edit, and look for the Source step in the Applied Steps panel. Click the gear icon next to it and update the folder path.

Can I filter to include only files modified after a certain date?
Yes. After opening the folder connector and choosing Transform Data, you can filter the Date modified or Date created columns just like any other column. Apply a filter to show only files modified after a specific date. Be aware that if you hard-code a specific date, you will need to update that filter step each time your refresh criteria change.

My query was working and then suddenly stopped. What happened?
The most common causes are: a source file was renamed or moved, a source file’s sheet or table name changed, a source file is currently open (creating a lock file), or a new file was added to the folder with a different structure. Work through each possibility systematically. The error message in the Power Query Editor usually points to which step failed, which narrows down the cause.

Summary

Power Query’s Folder connector is one of the most practical features in Excel for anyone who deals with recurring multi-file reporting. The standard case, where all your files have identical structure, takes less than five minutes to set up and then runs forever with a single Refresh click.

The more advanced case, where files have mismatched or inconsistent column headers, requires a bit more work inside the Transform Sample File query, but once you understand the logic, it is a fully solvable problem. The key insight is that Transform Sample File is the place to fix structural issues before the files are combined, not after.

The setup you build here will absorb new files automatically, track which file each row came from, and produce a clean, consistently formatted output table every time you refresh. That is a significant upgrade over manual copying or VBA macros, and it requires no programming skills to maintain.

If you have been manually combining Excel files, this is the method that makes that problem go away for good.