How to merge two excel files

Answer

Merging two Excel files is a process of combining data from separate workbooks into a single workbook. This is common when consolidating reports, tracking data, or combining datasets from multiple sources. The approach depends on whether the files have identical structures or need matching by a specific column.

Method 1: Copy and Paste Data

This method works when the datasets are small and have similar structures.

1. Open both Excel files.

2. Select the range of data in the first file by clicking and dragging or pressing Ctrl + A (Windows) / Command + A (Mac).

3. Copy the data using Ctrl + C (Windows) / Command + C (Mac).

4. Switch to the second file and select the cell where you want to paste the data.

5. Paste the data using Ctrl + V (Windows) / Command + V (Mac).

6. Check for duplicate headers. Remove or adjust them to maintain consistency.

This method is simple but can be error-prone if the files are large or have inconsistent formatting.

Method 2: Using Power Query

Power Query is a robust tool in Excel for merging files automatically, especially when dealing with multiple files or tables.

1. Open a new Excel workbook.

2. Go to the Data tab and select Get Data → From File → From Workbook.

3. Select the first Excel file and click Import.

4. In the Navigator window, select the sheet or table and click Load.

5. Repeat steps 2–4 for the second Excel file.

6. Go to Data → Get Data → Combine Queries → Append Queries.

7. Choose the tables from both files and click OK.

8. Review the combined table. Remove duplicates or blank rows if necessary.

9. Click Close & Load to save the merged dataset into a new sheet.

Power Query ensures consistent formatting and works well for larger datasets.

Method 3: Using Formulas (VLOOKUP / XLOOKUP)

This method is suitable when the files need to be merged based on a common column, such as ID numbers or dates.

1. Open both Excel files.

2. In the target file, select the first cell where data from the second file will appear.

3. Use the formula: 

   =VLOOKUP(A2, '[File2.xlsx]Sheet1'!$A$1:$D$100, 2, FALSE)

• A2 is the lookup value in your main file.

• '[File2.xlsx]Sheet1'!$A$1:$D$100 is the range in the second file.

• 2 indicates the column number to return from the second file.

• FALSE ensures an exact match.

4. Press Enter and drag the formula down for all rows.

5. Verify that all matched data appears correctly.

For Excel 365 users, XLOOKUP can replace VLOOKUP for more flexibility: 

=XLOOKUP(A2, '[File2.xlsx]Sheet1'!$A$1:$A$100, '[File2.xlsx]Sheet1'!$B$1:$B$100, "Not Found")

Method 4: Merge Using Macros (Advanced)

Macros automate the merging process for multiple files or frequent merges.

1. Press Alt + F11 to open the VBA editor.

2. Go to Insert → Module and paste this code:

   Sub MergeExcelFiles()

       Dim wb As Workbook

       Dim ws As Worksheet

       Dim FolderPath As String

       Dim FileName As String

       FolderPath = "C:\YourFolderPath\" 'Change this path

       FileName = Dir(FolderPath & "*.xlsx")

       Do While FileName <> ""

           Set wb = Workbooks.Open(FolderPath & FileName)

           For Each ws In wb.Sheets

               ws.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

           Next ws

           wb.Close SaveChanges:=False

           FileName = Dir

       Loop

   End Sub

3. Press F5 to run.

4. All sheets from Excel files in the folder are copied into the current workbook.

Macros are ideal when merging dozens of files in one go.

Tips to Avoid Errors

1. Ensure all files have consistent column headers and data formats.

2. Remove blank rows and unnecessary spaces before merging.

3. Save a backup before performing merges.

4. Check for duplicate data after merging.

Using these methods, Excel allows merging two files either manually, via formulas, with automation using Power Query, or programmatically using macros. Each method fits different data sizes and complexity levels.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report