Merge Multiple Excel Files into One: Step-by-Step
To combine multiple Excel files into one, you can use Excel’s built-in Power Query (Get & Transform) to append or consolidate data, or use the legacy “Move or Copyâ€� feature to merge entire sheets. This article explains the methods, when to use each, and how to avoid common pitfalls.
Why You Might Need to Merge Excel Files
Merging Excel files is a common task when data is spread across multiple workbooks—for example, monthly sales reports from different regions, survey responses collected by different teams, or inventory lists from various warehouses. Instead of manually copying and pasting, which is time-consuming and error-prone, you can use Excel’s built-in tools to combine files automatically. The right method depends on whether you need to combine data row-wise (append), column-wise (merge), or just stack entire sheets.
Method 1: Using Power Query to Combine Data (Recommended)
Power Query (called “Get & Transform� in Excel 2016 and later) is the most flexible way to merge multiple files with the same structure. It can combine dozens of files from a folder and refresh automatically when new files are added.
- Open a blank Excel workbook.
- Go to the Data tab and click Get Data > From File > From Folder.
- Browse to the folder containing your Excel files and click OK.
- In the dialog, click Combine > Combine & Load.
- Choose the sheet (or table) you want to combine from each file, then click OK.
- Power Query will combine all files into a single table, adding a “Source.Name� column that shows which file each row came from.
This method works best when all files have the same column headers. If headers differ, you can still combine but you’ll need to adjust the query in the Power Query editor.
Method 2: Using “Move or Copy� to Merge Whole Sheets
If you simply want to put multiple Excel sheets into one workbook (for example, to share with someone who needs all reports in one file), the “Move or Copy� feature is the quickest way.
- Open the destination workbook (where you want the sheets to end up).
- Open the source workbook that contains the sheet you want to move.
- Right-click the sheet tab at the bottom of the source workbook and select Move or Copy.
- In the “To book� dropdown, select the destination workbook.
- Choose where to place the sheet (e.g., before a specific sheet), check “Create a copy� if you want to keep the original, and click OK.
- Repeat for each sheet you need to move.
This method does not combine data; it just stacks sheets. Use it when you need to keep the original structure intact.
Method 3: Using Formulas to Reference Other Workbooks
If you want to create a summary sheet that pulls data from multiple files without physically merging them, you can use formulas like =SUM([Book1.xlsx]Sheet1!A1) or =VLOOKUP. However, this is only practical for small ranges because the formulas become long and hard to manage. It also requires the source files to remain in the same location.
For large datasets, Power Query is far more efficient than formulas.
How to Choose the Right Method
- Append data (stack rows) from many files with the same columns → Use Power Query (From Folder).
- Combine sheets from a few workbooks into one file → Use “Move or Copy�.
- Create a live summary that updates when source files change → Use Power Query with refresh, or formulas if the range is small.
- Combine files with different column layouts → Use Power Query and manually map columns in the editor.
Common Problems and How to Fix Them
- Headers are in different rows – In Power Query, you can set the first row as headers, but if files have varying header positions, you may need to promote headers after combining.
- Data types change (e.g., numbers become text) – Power Query sometimes misdetects types. Check the column data type in the query editor and change it to the correct type.
- Files are password-protected – Power Query cannot combine password-protected files. You must remove the password first.
- Files are in different formats (e.g., .xls and .xlsx) – Power Query can handle both, but older .xls files may require a different connector.
- Combining files from subfolders – In the “From Folder� dialog, you can enable “Include subfolders� to combine all files in nested folders.
Limitations and Considerations
Power Query is available in Excel 2016 and later, and in Microsoft 365. It is not available in Excel Online (web version) for the “From Folder� feature. Also, if you combine files with different column names, Power Query will create separate columns for each unique name, which can lead to a messy table. In that case, you may need to rename columns in the query editor before combining.
Another limitation: Power Query loads a snapshot of the data. If the source files change, you must refresh the query (right-click the query in the Queries pane and select Refresh).
Frequently Asked Questions
Can I merge multiple Excel files without Power Query?
Yes. You can use the “Move or Copy� feature to combine sheets, or copy-paste data manually. For simple one-time merges, that works fine. But if you need to combine many files regularly, Power Query is worth learning.
Will the merged file update automatically when source files change?
Only if you use Power Query and refresh the query. “Move or Copy� and copy-paste create static copies that do not update. With Power Query, you can also set it to refresh when the file opens.
How do I combine files with different column orders?
In Power Query, when you combine files, you can select the sheet and then in the query editor, reorder columns to match. The “Combine Files� dialog lets you pick which sheet to use; after loading, you can reorder columns manually.
What if my files have multiple sheets?
Power Query will only combine one sheet per file by default. If you need multiple sheets, you’ll have to run separate queries for each sheet, or use a more advanced approach with a custom function. The “Move or Copy� method can move multiple sheets at once.
Can I combine CSV files into Excel?
Yes. In Power Query, use “From Text/CSV� instead of “From Folder�. You can combine all CSV files in a folder using the same “Combine� button, or use “From Folder� and filter for .csv files.
Is there a way to combine files without losing formatting?
Power Query only imports data, not formatting. If you need to preserve formatting (colors, fonts, merged cells), you must use “Move or Copy� or copy-paste, because formatting is not carried through Power Query.
Related
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.