The fastest way depends on how many files you have and whether they have the same layout

If all your files have identical column headers and you just need to stack the data, copy and paste is fastest for two to five files. For six or more files with the same structure, use Excel's built-in consolidation feature or Power Query. If the files have different layouts or you need to match data across them, you will need a different approach for each situation.

The method also depends on whether you are working on Windows or Mac — some features work differently or not at all on Mac. Start by opening all the source files (the ones you want to combine) so you can see what you are working with, then choose the path that matches your situation.

Key Takeaways

  • Copy and paste works for a small number of files with identical layouts, but becomes slow and error-prone above five files.
  • Excel's Data > Consolidate feature combines data from multiple sheets or files automatically if the column headers match exactly.
  • Power Query (in Excel 2016 and later on Windows, or Excel 2021 and later on Mac) can combine dozens of files in seconds without manual copying.
  • If files have different column orders or missing columns, you must rearrange them to match before combining, or use a pivot table to match data by column name instead of position.
  • Always keep the original files as backups before you start merging, in case you need to go back and check the source data.

Copy and paste for two to five files with identical structure

Open the first source file and select all the data except the header row (the row with column names). Copy it. Open your destination file (a new blank workbook or an existing one) and paste the data starting below the header row. Repeat for each additional file.

This method is reliable but slow. You have to remember to skip the header row each time, and if you accidentally paste headers into the middle of your data, you will have to delete them manually. It also leaves room for mistakes if the columns are in a different order in different files — Excel will paste the data into the columns you selected, not match it by column name.

Use this method only if you have fewer than six files and you have already checked that every file has the same columns in the same order. If any file is missing a column or has columns in a different sequence, rearrange them first before you start copying.

Use the Consolidate feature for files with matching headers

The Consolidate tool is built into Excel and works on both Windows and Mac. It combines data from multiple sheets or files automatically, as long as the column headers are identical and in the same order.

First, open all your source files. Create a new blank workbook for the merged result. In the new workbook, click the Data tab, then find and click Consolidate (on Windows, it is in the Data Tools group; on Mac, look in the Data menu). A dialog box will open.

In the dialog, choose the function you want — usually Sum if you are adding numbers, or Count if you just want to count rows. Then click the button next to "Reference" and switch to each source file one at a time. Select all the data including headers, and click the button again to add that range to the list. Repeat for every file. When you have added all files, click OK. Excel will combine them into your new workbook.

This method works well if all files have the same structure, but it fails silently if one file has a column in a different order or is missing a column entirely. Always spot-check the result by scrolling through and comparing row counts to the originals.

Power Query for dozens of files at once (Windows and newer Mac versions)

Power Query is the fastest method for large numbers of files. It is built into Excel 2016 and later on Windows, and Excel 2021 and later on Mac. It can combine 50 files in the time it takes to copy and paste two.

On Windows, click the Data tab, then click Get Data (or New Query), then From File, then From Folder. Navigate to the folder containing all your source files and click Select Folder. Power Query will show you a preview of the first file. Click the Combine button, then Combine and Load. Excel will ask which column to match on — usually you want to match by column name, not position, so the data lines up correctly even if columns are in different orders.

On Mac, the process is similar but the menu location differs. Click the Data tab, then Get Data, then From File, then From Folder. Follow the same steps as Windows.

Power Query also handles files where columns are in different orders, or where some files have extra columns that others do not. It matches by column name, so the data ends up in the right place regardless. This makes it much safer than copy-paste or Consolidate for messy real-world data.

Rearrange columns before merging if they are in different orders

If your source files have the same columns but in different orders, you must fix this before using Consolidate or copy-paste. Power Query handles this automatically, so you can skip this step if you are using that method.

Open each source file and look at the column headers. Decide on a standard order — usually the order in your first file. Then open each other file and cut and paste the columns to match that order. For example, if File A has columns Name, Date, Amount and File B has columns Date, Amount, Name, cut the Date and Amount columns in File B and paste them after Name so the order matches.

This is tedious but necessary. If you skip it, Consolidate will combine the data in the wrong columns, and copy-paste will paste data into the wrong place. Once all files have identical column order, you can proceed with either method.

Check your merged file for duplicates and missing data

After merging, scroll through the combined file and spot-check the data. Look for duplicate rows — these sometimes appear if a file was accidentally included twice, or if the source files overlapped. Look for blank cells where you expected data, which might mean a column was missing from one of the source files.

Count the total number of rows in your merged file and compare it to the sum of rows in all the source files. If the numbers do not match, something went wrong. Go back to the source files and figure out which one is missing or which one was counted twice.

If you used Power Query, you can edit the query to exclude a file or fix a column mismatch, then reload the data. If you used copy-paste or Consolidate, you may need to start over. This is why keeping the original files as backups is important — you can always go back and check them.

Save the merged file with a clear name and location

Once you have verified the merged data, save the file with a name that makes it clear what it contains and when it was created. For example, "Sales_Q1_2024_Merged" is better than "Combined" or "Data". Include the date if you plan to merge the same files again later.

Save it in a location where you and anyone else who needs it can find it easily. If this is a file you will update regularly (for example, merging new monthly reports into a running total), consider saving it in a shared folder or cloud storage so others can access the latest version.

Keep the original source files in a separate folder. Do not delete them or move them into the merged file. You may need to refer back to them if a question comes up about where a particular data point came from, or if you need to re-merge with different settings.

Frequently Asked Questions

What if the files have different column names but contain the same data?

You must rename the columns to match before merging. Open each file and change the header names so they are identical across all files. Power Query can handle some variation if you tell it to match by content rather than name, but it is safer to standardize the headers first. This takes a few minutes but prevents data from ending up in the wrong columns.

Can I merge files that have different numbers of columns?

Yes, but only with Power Query. If you use copy-paste or Consolidate, files with extra columns will paste data into the wrong place. Power Query matches by column name, so extra columns in one file will straightforward appear as blank cells in rows from files that do not have that column. This is usually what you want.

What if I want to keep track of which file each row came from?

Add a new column to each source file before merging, and fill it with the file name or a source identifier. Then when you merge, that column will come along and show you where each row originated. Power Query can do this automatically — it has an option to add a "Source File" column during the merge process.

Is there a way to merge files automatically on a schedule?

Power Query can be set to refresh on a schedule if the source files are in a shared folder or cloud storage. Set up the query once, then right-click it and choose Refresh. You can also use Excel's built-in task scheduler (Windows) or Automator (Mac) to open the file and refresh at a specific time each day or week, though this requires some setup.

What if one of the source files is corrupted and will not open?

Try opening it in a different program — Google Sheets or LibreOffice Calc can sometimes open files that Excel cannot. If that does not work, you may be able to recover some data by opening it as a CSV file instead. If the file is completely unrecoverable, exclude it from the merge and note that the final total is incomplete. Always keep backups of important files in case this happens.