How to Merge Multiple Excel Files Into One — 4 Methods

Two Ways to Merge: Stack Rows vs Separate Sheets

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

• Stack rows — all data from every file ends up in one continuous table. Best for files with identical column headers (e.g., monthly sales exports). The header row is kept once, all data rows are stacked below it.
• Separate sheets — each input file becomes its own sheet in the output workbook. Best when files have different structures, or when you want to keep data separated while having it all in one file.

Method 1: Manual Copy-Paste (Works, But Doesn't Scale)

For two or three small files, copy-paste is the quickest approach:

1. Open all the workbooks you want to merge
2. In the first file, select all data rows (not the header): click the first data cell, then Ctrl+Shift+End to select to the last used cell
3. Copy (Ctrl+C) and paste into the destination workbook below the existing data
4. Repeat for each additional file

When to avoid it: Copy-paste is error-prone with more than three files, doesn't scale to 10+ files, and has no record of what was merged — making it difficult to audit or repeat. One misaligned paste can corrupt your entire dataset silently.

Method 2: Power Query (Best for Recurring Merges)

Power Query can merge all files in a folder automatically — add a new file to the folder and refresh to include it:

1. Put all files to merge into a single folder
2. In Excel, go to Data → Get Data → From File → From Folder
3. Browse to the folder and click OK
4. Power Query shows a preview of all files — click Combine → Combine & Load
5. Select the sheet name to extract from each file (useful if all files have the same sheet structure)
6. Click OK — Excel stacks all rows from every file into one table

The query stores the folder path. When you add new files or update existing ones, click Data → Refresh All and the merged table updates automatically. This makes it the best choice for monthly or weekly reporting consolidation.

Method 3: VBA Macro (For Complex Merges)

For merging files with multiple sheets, or combining data from specific named ranges, a VBA macro gives you full control:

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 row
            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

To run it: press Alt+F11 to open the VBA Editor, insert a new Module, paste the code, and press F5. Update the path variable to your folder location.

Method 4: Free Merge Excel Tool (No Software Required)

For a fast, no-code solution that works on any computer, the ExcelErrorFinder Merge Excel Files tool handles both merge modes in your browser:

1. Open the free merge tool
2. Upload all the files you want to combine (drag and drop multiple files at once)
3. Reorder files if the sequence matters (e.g., January before February)
4. Choose Stack rows (combines all data into one sheet, removes duplicate headers) or Separate sheets (one sheet per input file)
5. Click Merge & Download

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

Tips for a Clean Merge

• Consistent headers — when stacking rows, all files must have identical column headers in the same order. One extra or renamed column will cause misalignment.
• Audit after merging — run the free spreadsheet auditor on the merged file to catch inconsistent formulas or broken references introduced during the merge.
• Check row counts — add up the row counts in each source file and verify the merged total matches. This is the fastest way to confirm nothing was skipped.
• Remove duplicate header rows — if you stacked files with headers, check for rows that are copies of the header (e.g., a row that says "Date,Region,Amount" in the middle of your data). Filter and delete these.