How to Merge Multiple Excel Files Into One: 5 Methods That Actually Work

How-to · Updated July 2026 · ~8 min read

Every month-end, someone emails you twelve branch workbooks and expects a single consolidated file by lunch. Learning how to merge multiple Excel files into one is one of those skills that quietly saves finance teams hours — but only if you pick the right method for the job. Below are five ways to combine Excel files, from a two-minute manual copy to a refreshable Power Query pipeline, with the exact Excel 365 menu paths and honest advice on when each one wins.

The 5 ways to merge multiple Excel files into one

Before the steps, here's the short version so you can jump to what you need:

1. Manual copy-paste — fastest for two or three small files, one time only.
2. Move or Copy Sheet — clean way to pull specific tabs into one workbook.
3. Power Query from Folder — the robust choice; merge Excel files into one workbook and re-run it next month with one click.
4. A VBA loop — full control for odd or repetitive jobs where Power Query feels heavy.
5. AI (HISAB 360) — describe the merge in plain English and let it build the pipeline.

The mistake most people make is using method 1 for a job that really needs method 3. If you'll ever do the same merge again, skip ahead to Power Query.

Method 1: Manual copy-paste

No setup, no macros — just open the files side by side and move the data yourself.

1. Open the destination workbook and every source file.
2. In a source file, click the corner triangle (or press Ctrl + A) to select the used range.
3. Ctrl + C, switch to the destination sheet, click the first empty cell, Ctrl + V.
4. Repeat for each file, stacking data under the previous block.

When to use it: two or three small files, a one-off task, and you can eyeball the result. When to avoid it: anything you'll repeat, anything with more than a few hundred rows, or files where the columns don't line up perfectly. Manual paste is where copy-paste errors are born — a shifted column or a missing header row won't announce itself.

Method 2: Move or Copy Sheet

When the goal is to gather specific worksheets — not append rows, but keep each file's tab intact inside one workbook — this is the tidiest route.

1. Open both the source and the destination workbooks.
2. In the source file, right-click the sheet tab you want.
3. Choose Move or Copy…
4. In the To book dropdown, select your destination workbook.
5. Pick where it should land under Before sheet.
6. Tick Create a copy (leave it unchecked and the tab moves out of the source).
7. Click OK.

When to use it: consolidating separate tabs — say, a P&L, a balance sheet, and a cash-flow tab from three files — into one reporting workbook. Watch out for: duplicate sheet names (Excel adds "(2)"), and formulas that reference the old workbook, which keep pointing back to the source file until you break the links.

Method 3: Power Query from Folder — the robust one

This is the method worth learning properly. Point Power Query at a folder, and it reads every file inside, stacks them into one table, and — crucially — lets you refresh when new files land. Drop next month's thirteenth branch file into the folder, hit Refresh, and the merge updates itself. This is how to consolidate spreadsheets without redoing the work every period.

First, put all the files you want to combine into a single folder, and make sure each file has the same column layout (same headers, same order). Then:

1. Go to Data tab → Get Data → From File → From Folder.
2. Browse to your folder and click Open.
3. In the preview window, click the Combine dropdown → Combine & Transform Data.
4. In the Combine Files dialog, pick a sample sheet or table that represents all your files, then click OK.
5. The Power Query Editor opens with all files already stacked. Here you can remove junk columns, filter out blank rows, fix data types, or trim a stray header row.
6. Notice the Source.Name column Power Query adds automatically — it tells you which file each row came from. Keep it; auditors love it.
7. Click Close & Load to drop the combined table onto a new sheet.

Next month, select any cell in the result and press Data → Refresh All (or Ctrl + Alt + F5). No re-import, no re-paste.

When to use it: recurring consolidations, more than a handful of files, or any dataset you'll report on again. The one catch: files must share a consistent structure. If column names drift between branches, tidy them first — or Power Query will helpfully create a new column for every variation.

Method 4: A VBA loop

When files are inconsistent, or you need each source as its own tab with custom naming, a short macro gives you total control.

1. Press Alt + F11 to open the VBA editor.
2. Insert → Module.
3. Paste the code below and adjust the folder path.
4. Press F5 to run.

Sub MergeWorkbooks()
    Dim folderPath As String, fileName As String
    Dim sourceWb As Workbook, masterWb As Workbook
    Dim ws As Worksheet, newName As String

    folderPath = "C:\Reports\Branches\"   ' keep the trailing backslash
    Set masterWb = ThisWorkbook
    fileName = Dir(folderPath & "*.xlsx")

    Application.ScreenUpdating = False
    Do While fileName <> ""
        Set sourceWb = Workbooks.Open(folderPath & fileName)
        For Each ws In sourceWb.Worksheets
            ws.Copy After:=masterWb.Sheets(masterWb.Sheets.Count)
            ' sheet names must be unique and <= 31 characters
            newName = Left(Replace(fileName, ".xlsx", "") & "_" & ws.Name, 31)
            On Error Resume Next
            masterWb.Sheets(masterWb.Sheets.Count).Name = newName
            On Error GoTo 0
        Next ws
        sourceWb.Close SaveChanges:=False
        fileName = Dir
    Loop
    Application.ScreenUpdating = True
    MsgBox "Done. Merged all files in " & folderPath
End Sub

When to use it: you need every worksheet copied as a separate tab, custom names, or logic Power Query can't express easily. Remember: sheet names must be unique and 31 characters or fewer — the On Error lines above stop a duplicate name from halting the run, but rename collisions still get skipped, so check the result.

Method 5: Let HISAB 360 build the merge for you — plain English in, pipeline out

Every method above asks you to know the tool before you touch the data: which menu, which dialog, which line of M or VBA. HISAB 360 flips that around. It's an AI assistant that lives inside Excel, and instead of building the query yourself, you tell it the outcome you want. Under the hood it writes the same Power Query folder pipeline from method 3 — you just don't have to hand-build it.

Open the HISAB panel and type the job the way you'd say it to a colleague:

"Merge every workbook in this folder into one table, matching columns by header. Add a column showing which file each row came from, and make it refreshable so next month I just drop in the new file and hit refresh."

HISAB reads that, confirms the folder, and generates a folder-to-table Power Query — the robust, repeatable version, not a one-time paste. The M code it drops into the query editor looks like this:

let
    Source = Folder.Files("C:\Reports\Branches"),
    OnlyExcel = Table.SelectRows(Source, each [Extension] = ".xlsx"),
    Combined = Table.AddColumn(OnlyExcel, "Data",
                 each Excel.Workbook([Content], true){0}[Data]),
    Expanded = Table.ExpandTableColumn(Combined, "Data",
                 Table.ColumnNames(Combined{0}[Data])),
    #"Kept Source Name" = Table.RenameColumns(Expanded, {{"Name", "Source.Name"}})
in
    #"Kept Source Name"

Because it went the Power Query route, you inherit everything that makes method 3 worth learning: columns are matched by header name, not position, so a branch that orders its columns differently still lines up; the Source.Name column tags every row with its origin file; and the whole thing is refreshable — Data → Refresh All re-runs it when new files land. HISAB loads the combined table onto a fresh sheet, and you're done.

The real payoff is the follow-up. Once the table exists you can keep talking to it in plain English — "filter out the rows where Amount is blank," "add a Month column from the file name," "load this to the Data Model instead of a sheet" — and HISAB edits the query steps for you. You get method 3's durability at the pace of a conversation, which is handy when you know exactly what you want but not the M behind it. It's one option among the five here; the manual routes above work perfectly well on their own, and HISAB simply writes them for you when you'd rather describe the merge than build it.

Which method should you choose?

• One-off, two or three files: manual copy-paste.
• Gathering specific tabs into one book: Move or Copy Sheet.
• Recurring merge, consistent files: Power Query from Folder — every time.
• Odd structures or per-file tabs: a VBA loop.
• You'd rather describe it than build it: an AI assistant like HISAB 360.

The rule of thumb: if you'll do this merge more than once, invest the ten minutes in Power Query. The first run costs a little; every run after is a single click.

Frequently asked questions

Try HISAB 360 on your own workbook

HISAB 360 is an AI assistant inside Excel for accountants and finance teams — it writes macros, Power Query and formulas from plain English, and connects two-way to QuickBooks, Xero, Zoho Books, Odoo, FreshBooks and Sage. The 15-day trial is the full product, no card required.