What merging Excel documents means and when you need it
Merging Excel documents means combining data from two or more separate spreadsheet files into a single file. This is different from straightforward copying and pasting — a proper merge preserves the structure of your data, avoids duplicates, and keeps everything organized so you can work with it as one dataset.
You need to merge when you have data scattered across multiple files that belongs together. Common situations include combining sales reports from different regions, pulling together survey responses collected in separate sheets, or consolidating budget data from different departments. The goal is to have one file you can sort, filter, and analyze instead of flipping between several.
The method you choose depends on how your files are organized and whether the data has headers (the row of labels at the top). Excel has built-in tools for this, but the simplest approach often depends on whether your files have identical structure or different layouts.
Key Takeaways
- The fastest way to merge two files is to open both, copy all data from one file, and paste it into the other below the existing data — this works when both files have the same column headers.
- If your files have different structures or you want to avoid manual copying, use the Consolidate feature under the Data menu, which can combine data by position or by label.
- Before you merge, check that all files use the same column headers and that you have removed any duplicate header rows that might appear in the middle of your data.
- After merging, sort or filter your combined data to spot any rows that were duplicated during the merge process.
The manual copy-and-paste method for files with matching structure
If all your Excel files have the same columns in the same order, copying and pasting is the quickest route. Open the first file and leave it on your screen. Then open the second file in a new window — on Windows, use Alt+Tab to switch between them; on Mac, use Command+Tab or open the second file and position both windows side by side.
In the second file, select all the data rows below the header. Click on the row number of the first data row, then hold Shift and click on the last row with data. This selects the entire rows. Right-click and choose Copy, or press Ctrl+C on Windows or Command+C on Mac.
Switch back to your first file. Click on the first empty row below your existing data — if your data ends at row 50, click on row 51. Right-click and choose Paste, or press Ctrl+V or Command+V. The data from the second file now appears below the first file's data in the same columns.
Repeat this process for any additional files. When you are done, save the combined file with a new name so you do not overwrite your original files. Use File > Save As and give it a name like "Combined Sales Data" or "All Responses Merged".
Using the Consolidate feature for files with different layouts
The Consolidate tool is built into Excel and handles files that have different structures or that you want to combine by matching labels rather than position. Open the file where you want the merged data to appear — this becomes your destination file.
Click on the Data menu at the top. Look for the Consolidate option — on Windows it is usually in the Data Tools group; on Mac it appears directly in the Data menu. Click it to open the Consolidate dialog box.
In the dialog, you will see a field labeled "Reference". Click the folder icon next to it. This lets you browse to the first file you want to merge. Navigate to that file, click on it, and then click on the sheet tab at the bottom (usually "Sheet1"). Select all the data including headers by clicking the top-left cell and dragging to the bottom-right cell, or by clicking the top-left cell and pressing Ctrl+Shift+End on Windows or Command+Shift+End on Mac. Click the button to return to the Consolidate dialog.
The reference now shows the file path and range. Click Add to add this reference to the list. Repeat the process for each additional file. Before you click OK, check the box that says "Use labels in top row" if your data has headers, and check "Use labels in first column" if your rows have labels in the first column. Then click OK. Excel combines all the data according to the labels you specified.
Removing duplicate rows after merging
After you merge files, you may have duplicate rows — especially if the same record appeared in more than one source file. Excel has a built-in tool to find and remove these. First, select all your merged data including headers. Click on the Data menu and look for Remove Duplicates.
A dialog will appear showing all your columns. By default, all columns are checked, which means Excel will consider a row a duplicate only if every single column matches. If you want to identify duplicates based on specific columns — for example, if you only care about duplicate ID numbers — uncheck the columns you do not want to compare. Click OK.
Excel removes the duplicate rows and shows you how many it deleted. Note that this action cannot be undone, so if you are unsure, save a copy of your merged file first, then run Remove Duplicates on the copy.
Merging files with different column orders
If your files have the same data but the columns are in different orders, do not use straightforward copy-and-paste — you will end up with misaligned data. Instead, rearrange the columns in one file to match the other before merging.
Open the file with the different column order. Right-click on a column header (the letter at the very top) and choose Insert to add a blank column if you need to shift things around. Then click and drag column headers to reorder them. For example, if one file has Name, Email, Phone and another has Email, Name, Phone, rearrange the second file to match the first.
Once the columns match, you can use the copy-and-paste method described earlier. This ensures that names stay in the Name column, emails in the Email column, and so on.
Merging files into a new blank file
If you want to keep your original files untouched and create a single merged file from scratch, open a new blank Excel file. Save it when ready with a name like "Merged Data" so you do not lose it by accident.
Copy the header row from your first source file. Click on row 1 in your new blank file and paste it. Then use the copy-and-paste method to add data from each source file below the headers. This approach takes slightly longer but leaves all your original files unchanged and gives you a clean merged file to work with.
Checking your merged data for accuracy
After merging, spend a few minutes checking that everything combined correctly. Sort your data by one column to see if any rows look out of place or misaligned. If you merged files with different structures, spot-check a few rows from each original file to make sure the data ended up in the right columns.
Use the filter feature to look for blank cells in columns that should always have data. Click on any cell in your merged data, then click Data > Filter. Small dropdown arrows appear in the header row. Click one and look for "(Blank)" in the list — if you see it, some rows are missing data in that column. This might be normal, or it might mean something went wrong during the merge.
If you used Remove Duplicates, do a quick count: add up the row counts from your original files and subtract the number of duplicates Excel reported. The result should match your merged file's row count (minus the header row). If the numbers do not match, you may have lost data accidentally.
Frequently Asked Questions
Can I merge files that have different numbers of columns?
Yes, but you need to be intentional about it. If one file has columns A, B, C and another has A, B, C, D, the file with four columns will paste correctly, but you will have empty cells in column D for rows from the three-column file. Use the Consolidate feature if you want Excel to match columns by label rather than position.
What if one file has headers and another does not?
Add a header row to the file that is missing one before you merge. Open that file, right-click on row 1, and choose Insert to add a blank row at the top. Type the column names to match your other file. Then proceed with merging.
Can I merge files from different Excel versions or formats?
Yes. Excel can read older formats like .xls and newer ones like .xlsx. Open both files normally and use either the copy-paste or Consolidate method. If you have trouble opening an older file, try opening Excel first, then using File > Open to browse for the file.
How do I merge files that are stored in different folders?
The Consolidate feature can reference files in any folder on your computer. When you click the folder icon in the Reference field, navigate to whichever folder contains the file you want to add. For copy-paste, open each file separately using File > Open, regardless of where it is stored.
What happens if I merge files with the same data but different formatting?
The formatting from the destination file (the file you are pasting into) usually takes priority. If you want all rows to have the same formatting, select all your merged data after combining it, then explore formatting once to the entire range. This ensures consistency across all rows.