How to Merge Excel Files Without Losing Data

Choose Your Merge Mode First

Before choosing a method, decide what the output should look like:

• Stack rows — all data ends up in one continuous table. Best for files with identical headers (monthly sales exports, regional reports). One header row at the top, all data below.
• Separate sheets — each input file becomes its own sheet. Best when files have different structures, or when recipients need the data separated but in one workbook.

Method 1: Power Query (Best for Recurring Merges)

Power Query can merge all files in a folder automatically. When you add a new file to the folder and click Refresh, the merged output updates without any manual steps:

1. Put all files to merge into a single folder
2. In Excel: Data → Get Data → From File → From Folder
3. Browse to the folder and click OK
4. Click Combine → Combine & Load
5. Select the sheet name to extract from each file (all files must have the same sheet name)
6. Click OK — Excel stacks all rows from every file into one table automatically

To update when new files are added: Data → Refresh All. This is the best method for monthly or weekly consolidation workflows.

Method 2: Free Merge Tool (Fastest for One-Off Tasks)

The ExcelErrorFinder Merge Excel Files tool handles both merge modes in your browser with no software required:

1. Open the free merge tool
2. Upload all the files to combine (drag and drop multiple files at once)
3. Reorder files if the sequence matters (January before February, etc.)
4. Choose Stack rows or Separate sheets
5. Click Merge & Download

Files are processed entirely in your browser — nothing is uploaded to any server. The output is a single .xlsx file ready to open in Excel.

Method 3: VBA Macro (For Complex Merges)

When you need to merge files with specific sheet names, merge only certain columns, or process a folder of 50+ files, a VBA macro gives full control. Press Alt+F11, insert a Module, and paste:

Sub MergeWorkbooks()
    Dim path As String
    Dim file As String
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim destWs As Worksheet
    Dim lastRow As Long

    path = "C:\Users\YourName\Documents\MergeFolder\"
    Set destWs = ThisWorkbook.Sheets(1)
    lastRow = destWs.Cells(Rows.Count, 1).End(xlUp).Row

    file = Dir(path & "*.xlsx")
    Do While file <> ""
        Set wb = Workbooks.Open(path & file)
        For Each ws In wb.Sheets
            ws.UsedRange.Offset(1, 0).Copy ' Skip header
            destWs.Cells(lastRow + 1, 1).PasteSpecial xlPasteValues
            lastRow = destWs.Cells(Rows.Count, 1).End(xlUp).Row
        Next ws
        wb.Close False
        file = Dir()
    Loop
    MsgBox "Merge complete."
End Sub

Update path to your folder. Run with F5.

Verify the Merge — Do Not Skip This

A merge that silently drops rows is worse than no merge. Always verify:

1. Before merging: note the row count in each source file (Ctrl+End to find the last row)
2. Add up the row counts across all files (subtract 1 per file for the header if stacking)
3. After merging: the row count in the merged file should equal that total
4. If stacking: filter for any row where the first column matches your header text (e.g., "Date" or "Region") — these are duplicate header rows that crept in and need to be deleted

Frequently Asked Questions

How do I merge multiple Excel files into one without losing data?

Use Power Query for recurring merges or the free merge tool for one-off tasks. Both methods preserve all rows and handle headers correctly. Always verify row counts after merging.

Should I stack rows or keep files as separate sheets?

Stack rows when all files have identical column headers and you want one table for analysis. Use separate sheets when files have different structures or when you want data separated but in one workbook.

How do I verify the merge did not lose any rows?

Count rows in each source file, add them up, compare to the merged total. They should match exactly. Also check for duplicate header rows in the middle of the data if you stacked rows.