How to Merge Two Excel Files Into One

The fastest way depends on file size and what you're combining

Merging two Excel files means taking data from one spreadsheet and putting it into another. The method you choose depends on whether you're combining rows of the same data, adding new columns, or just putting two separate tables side by side. For most people, copying and pasting works fine. For larger files or repeated merges, using Excel's built-in tools saves time and reduces mistakes.

The three main approaches are: copy and paste (fastest for small amounts), using the VLOOKUP or INDEX/MATCH functions (when you need to match rows from two files), or consolidating data (when you have multiple files with the same structure). Each takes a different amount of setup, and which one you pick depends on how much data you have and whether you need the connection to update automatically.

Key Takeaways

Copy and paste works for most merges—open both files, select the data you need from one, and paste it into the other.
If you need to match rows between files (like joining customer names to order numbers), use VLOOKUP or INDEX/MATCH instead of pasting.
The Consolidate tool in Excel combines multiple files with identical layouts automatically, which is faster than manual copying for repeated merges.
Save your original files before merging so you have a backup if something goes wrong.

Copy and paste for adding rows or columns

Open both files at the same time. In Windows, use Alt+Tab to switch between them. On Mac, use Command+Tab or open them in separate windows side by side. Select the data you want to move from the first file—click the cell in the top-left corner of your data, hold Shift, and click the bottom-right cell to select the whole range. You can also click the row or column header to select an entire row or column.

Press Ctrl+C (or Command+C on Mac) to copy. Switch to the second file and click the cell where you want the data to start. Press Ctrl+V to paste. If you're adding new rows below existing data, click the first empty row. If you're adding new columns to the right, click the first empty column. Excel will paste everything in the same layout it had in the original file.

After pasting, check that the data landed in the right place and that nothing got cut off. If the columns are too narrow to show all the text, double-click the line between column headers to auto-fit the width. Delete the data from the first file only after you've confirmed the paste worked and saved both files.

Use VLOOKUP or INDEX/MATCH to join data from two files

Use this method when you have two files with related information that needs to match up—for example, one file has customer names and IDs, and another has order numbers and customer IDs. Instead of copying everything, you write a formula that looks up the matching row in the second file and pulls the information you need.

Open both files. In the file where you want the new information to appear, click the cell where you want the result. Type a VLOOKUP formula: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). Replace lookup_value with the cell that contains what you're searching for (like a customer ID). Replace table_array with the range in the other file that contains the data you're searching in. Replace col_index_num with the column number of the information you want to pull back (the first column is 1, the second is 2, and so on). Type FALSE at the end to find exact matches only.

For example, if you're looking up a customer ID from cell A2 in a table in the other file called Orders (columns A through D), the formula would be: =VLOOKUP(A2,[Orders.xlsx]Sheet1!$A$1:$D$100,3,FALSE). This looks for the value in A2 in the first column of the range and returns the value from the third column. Copy this formula down to all the rows that need it. If the other file is closed, Excel will ask you to open it or provide the file path.

Use the Consolidate tool for files with the same layout

If you have multiple files with identical column headers and structure—like monthly sales reports from different regions—the Consolidate tool combines them automatically. This is faster than copying each file manually and less error-prone than writing formulas.

Open a new blank spreadsheet or the file where you want the merged data to go. Click the Data tab at the top. Click Consolidate (in some versions of Excel, this is under Data > Consolidate). A dialog box will open. In the Function dropdown, choose what you want to do—Sum adds numbers together, Count counts cells, Average finds the mean, and so on. In the Reference field, click the folder icon and navigate to the first file. Select the range that contains your data (including headers), then click Add. Repeat for each file you want to merge. Click OK.

Excel will combine all the data according to the function you chose. If you want the merged file to update automatically when the original files change, check the "Create links to source data" box before clicking OK. This creates a connection, so if someone updates a number in one of the original files, the merged file will reflect that change.

Avoid common mistakes when merging

The most common error is pasting data into the wrong location and overwriting something you needed. Before you paste, always check that the destination cell is empty or that you're okay with replacing what's there. If you make a mistake, press Ctrl+Z when ready to undo.

Another mistake is not checking that column headers match. If one file calls a column "Date" and another calls it "Transaction Date," they'll look like different columns when you paste them side by side. Rename columns to match before merging, or you'll end up with duplicate information that looks separate.

When using VLOOKUP, the most common problem is that the lookup column (the first column in your range) doesn't match exactly. If one file has "John Smith" and the other has "john smith" or "Smith, John," the formula won't find a match. Clean up your data first—make sure names, IDs, and dates are formatted the same way in both files.

Save your merged file with a clear name

After merging, save the new file with a name that shows what it contains and when it was created. Instead of "Merged File," use something like "Sales_Jan_Feb_2024" or "Customer_Orders_Combined." This makes it easier to find later and tells anyone else looking at it what data is inside.

Keep the original files in a separate folder. You may need to go back to them if you find an error or need to re-merge with updated information. If you're merging files regularly (like monthly reports), create a template file with the structure and formulas already set up, then just paste new data into it each time.

Frequently Asked Questions

Can I merge files that have different column headers?

Yes, but you'll need to rename the columns first so they match. Open each file and change the headers to be identical, then merge. If you use VLOOKUP, the lookup column must match exactly, but other columns can have different names as long as you're pulling the right column number.

What if one file is much larger than the other?

Copy and paste still works, but it may take a few seconds. If the file is very large (more than 100,000 rows), Excel may slow down. In that case, use VLOOKUP or Consolidate instead, which handle large files more efficiently. You can also split the large file into smaller chunks and merge them separately.

Do I need to keep both original files after merging?

Yes, keep them as backups. If you find an error in the merged file or need to re-merge with updated data, you'll have the originals to work from. Store them in a folder labeled "Source Files" or similar so you know not to delete them.

Can I undo a merge if I make a mistake?

If you just pasted, press Ctrl+Z to undo. If you've already saved the file, you can't undo. This is why saving your original files first is important—you can always start over. If you used formulas like VLOOKUP, you can edit or delete the formula without affecting the original files.

What's the difference between copy-paste and VLOOKUP?

Copy-paste adds all the data from one file into another, which works when you want everything. VLOOKUP pulls only specific information based on a match, which works when you have two related files and need to connect them. Use copy-paste for combining similar data; use VLOOKUP when you need to look up specific values.

This guide is for general information only and is not medical, financial, legal, or other professional advice. For decisions specific to your situation, consult a qualified professional. See our Editorial Policy.