The fastest way depends on how many files you have and whether the data matches

If you have two or three Excel files with the same columns, copy and paste is usually fastest. Open both files side by side, select all the data in the first file (not the header row if you're combining them), copy it, switch to your destination file, and paste below the existing data. Repeat for each additional file.

For larger jobs — five files or more, or files with different column layouts — use Excel's built-in consolidation tools or Power Query. These methods take longer to set up but handle mismatched columns, duplicate removal, and calculations across files without manual work. The method you choose also depends on whether you need to update the merged file later when the source files change.

Key Takeaways

  • Copy and paste works for two to three files with matching columns, and takes only a few minutes to complete.
  • Excel's Data > Consolidate tool combines data from multiple files by location or label, and updates automatically if you change the source files.
  • Power Query (in Excel 2016 and newer) can combine files from a folder, remove duplicates, and match columns by name even if they are in different orders.
  • If your files have different structures or you need to combine them regularly, Power Query saves time compared to manual copying.
  • Always keep the original files unchanged and work in a new file called something like "Combined Data" so you can go back if something goes wrong.

Copy and paste for small jobs

Open the first file and the destination file in separate windows. In the first file, click the row number of the first data row (the row after your headers). Hold Shift and click the last row with data to select everything. Press Ctrl+C (or Cmd+C on Mac) to copy.

Switch to your destination file. Click the first empty row below your headers. Press Ctrl+V to paste. The data lands in the same columns it came from. Repeat this for each additional file. If the files have headers in different orders — for example, one has "First Name" and "Last Name" while another has "Name" — you will need to rearrange columns before pasting or manually fix them afterward.

This method works best when all files have identical column headers and you are combining fewer than five files. It takes about two minutes per file once you get the hang of it.

Use the Consolidate tool for files with matching layouts

The Consolidate tool lives under the Data menu and works when all your source files have the same structure — same columns in the same order, same row labels. It can combine data by adding numbers together, averaging them, or just stacking them.

Open a new blank file. Go to Data > Consolidate. In the Function dropdown, choose what you want to do — Sum if you want totals, Average if you want averages, or Count if you just want to count rows. In the Reference field, type the path to your first file and the range of data you want to include, like C:\Users\YourName\Documents\Sales_Q1.xlsx!Sheet1!A1:D50. Click Add. Repeat for each additional file. Click OK.

Excel combines everything into the destination file. If you later change a number in one of the source files, you can refresh the consolidation by going back to Data > Consolidate and clicking OK again — it will update automatically. This saves time if you are merging files that change regularly, like monthly sales reports.

Use Power Query for complex merges

Power Query is built into Excel 2016 and newer (on Windows) and Excel 2016 and newer on Mac. It handles files with different column orders, removes duplicates, and can combine all files from a folder at once without you naming each one individually.

Go to Data > Get Data > From File > From Folder. Navigate to the folder containing your Excel files and click OK. Power Query shows you a preview of all the files it found. Click the Combine button, then Combine and Load. Excel asks how to combine them — choose "Combine and Load to New Sheet" to see the result in a new worksheet.

Power Query automatically matches columns by name, so even if one file has columns in the order "Name, Date, Amount" and another has "Amount, Name, Date", it puts them in the right place. You can also remove duplicate rows, filter out unwanted data, and sort before loading. Once you set this up, you can refresh it later — Power Query will pull in any new files added to the folder and combine them the same way.

The downside is that Power Query has a learning curve if you have never used it. The upside is that it saves enormous amounts of time if you are combining more than ten files or doing this regularly.

Handle duplicate rows and mismatched data

After you merge files, you often end up with duplicate rows — the same customer or transaction appearing twice because it was in multiple source files. Excel's Remove Duplicates tool finds and deletes them. Select all your data, go to Data > Remove Duplicates, check the columns that should be unique (usually ID or Name), and click OK. Excel deletes any row where those columns match a row above it.

If your files have different column names for the same information — one says "Phone" and another says "Phone Number" — you need to fix this before merging. Rename the columns in your source files so they match, or manually rename them in the merged file and then use Remove Duplicates. Power Query can handle some of this automatically, but it is safer to standardize column names first.

If one file has extra columns the others do not have, those columns will be empty for rows from the other files. This is usually fine — you can delete the column later if you do not need it, or leave it and fill in the blanks if the data exists somewhere else.

Save your merged file safely

Always save the merged result in a new file with a clear name like "Combined_Sales_2024" or "Merged_Customer_Data". Do not overwrite one of your source files — if something goes wrong, you want the originals to go back to.

If you used Consolidate or Power Query and set it up to refresh automatically, keep the source files in the same location and with the same names. If you move or rename them, the merged file will not be able to find them when you try to refresh.

Save the merged file as .xlsx (Excel format) unless you have a reason to use another format. If you need to share it with someone using an older version of Excel, save it as .xls instead, but be aware that some newer features may not work.

When to use a database instead of merging files

If you are merging the same files every month, or if you have more than twenty files to combine, consider moving your data to a database or a tool like Google Sheets instead. A database lets you add new data once and query it however you want without re-merging files each time. Google Sheets has similar consolidation features and makes it easier to share the work with other people.

Excel is designed for analysis and reporting, not for managing large amounts of data that changes constantly. If you find yourself merging files more than once a quarter, the time you spend setting up a better system will pay for itself quickly.

Frequently Asked Questions

Can I merge files that have different numbers of columns?

Yes. Copy and paste will leave empty cells where a file does not have data. Power Query matches columns by name, so it handles different column counts automatically. The Consolidate tool requires matching structures, so it is not the right choice for this situation.

What if my files are in different folders?

Copy and paste works from any location. The Consolidate tool requires you to type the full file path, which you can get by right-clicking the file, selecting Properties, and copying the location. Power Query's folder method only works if all files are in the same folder, but you can add files from different locations one at a time using the Reference field.

Do I have to delete the source files after merging?

No. Keep them. If you set up Consolidate or Power Query to refresh, the source files need to stay in place. Even if you use copy and paste, keeping the originals lets you go back and check the data if you find an error in the merged file.

Can I merge files that are still being edited?

You can, but it is risky. If someone is adding data to a source file while you are merging, you might miss the new rows or get an error. Wait until the files are finalized, or set up Power Query to refresh later once all the data is in place.

What is the difference between consolidating and merging?

Merging usually means stacking rows on top of each other — all the data from File A, then all the data from File B. Consolidating can mean that too, but it can also mean combining numbers (adding them together, averaging them) across files. Excel's Consolidate tool does both, while copy and paste only stacks rows.