Analistable

How to combine multiple Excel files into one automatically

By the Analistable team · Updated · 5 min read

The simplest automatic merge is Power Query From Folder with Refresh data when opening the file turned on: drop new files in the folder and the combined sheet updates when you open it. For a button that runs on demand, use a VBA macro that loops through the folder. For merges that must run on a schedule without anyone opening Excel, use Power Automate.

Part of our guide: How to merge Excel files: every method compared

Pick the level of automation

Automatic merge options
MethodRuns whenNeedsBest for
Power Query refreshYou open the file or click RefreshExcel 2016+ or Microsoft 365Most people
VBA macroYou click a buttonWindows desktop Excel, macros enabledCustom rules
Power AutomateOn a schedule or when a file arrivesMicrosoft 365 business account, OneDrive/SharePointUnattended merges

Option 1: Power Query that refreshes itself

  1. Build the combine with Data → Get Data → From File → From Folder → Combine & Transform Data (full steps in combining Excel files into one spreadsheet).
  2. In Data → Queries & Connections, right-click the combined query and choose Properties.
  3. Tick Refresh data when opening the file. Optionally tick Refresh every N minutes for a workbook that stays open.
  4. Click OK and save.

From now on, saving a new file into the folder is the only manual step.

Making the automatic refresh robust

A query that runs unattended has to cope with whatever lands in the folder. Four changes prevent most failures:

  • Filter the file list. In the query's first steps, keep only .xlsx files and drop names starting with ~$ (lock files from open workbooks). Filter the Extension and Name columns with the column filter menus.
  • Take the first sheet by position, not by name, by changing the navigation step in Transform Sample File to Source{0}[Data], so a file whose tab is called “Sheet1” instead of “Sales” still loads.
  • Keep the output workbook outside the folder. If the combined workbook sits in the source folder, it will be read as one of its own inputs.
  • Store the folder path once. Use Home → Manage Parameters to create a FolderPath parameter and refer to it in the Source step, so moving the folder means changing one value.

For files in SharePoint or Teams, use Data → Get Data → From File → From SharePoint Folder instead of a synced local path; the query then refreshes for anyone with access to the site, not only on the PC where the folder happens to be synced.

Showing when the data was last refreshed

When a workbook refreshes itself, readers need to know how current it is. Create a blank query with this formula, name it LastRefresh and load it to a cell above the report:

= #table({"Last refreshed"}, {{DateTime.LocalNow()}})

Because it refreshes with everything else, the cell always shows the time of the last successful refresh. Pair it with a row count per file (Group By on Source.Name) to spot a file that loaded empty.

Option 2: a VBA macro

This macro opens every .xlsx file in a folder, copies the first sheet's data (the header once) into a sheet called Combined, and closes each file without saving. It assumes each file's data starts in A1.

Sub CombineWorkbooks()
    Dim folder As String, f As String
    Dim src As Workbook, dest As Worksheet
    Dim nextRow As Long

    folder = "C:\Reports\Monthly\"   ' keep the trailing backslash
    Set dest = ThisWorkbook.Worksheets("Combined")
    dest.Cells.Clear
    nextRow = 1
    Application.ScreenUpdating = False

    f = Dir(folder & "*.xlsx")
    Do While f <> ""
        Set src = Workbooks.Open(folder & f, ReadOnly:=True)
        With src.Worksheets(1).UsedRange
            If nextRow = 1 Then
                .Copy dest.Cells(1, 1)
                nextRow = .Rows.Count + 1
            ElseIf .Rows.Count > 1 Then
                .Offset(1).Resize(.Rows.Count - 1).Copy dest.Cells(nextRow, 1)
                nextRow = nextRow + .Rows.Count - 1
            End If
        End With
        src.Close SaveChanges:=False
        f = Dir()
    Loop

    Application.ScreenUpdating = True
End Sub

Save the workbook as .xlsm, press Alt+F11, insert a module, paste the code and change the folder path. Add a button via Developer → Insert → Button. The macro pastes by position, so all files need the same column order.

Two small changes cover the most common requests:

  • To include older .xls files and macro-enabled .xlsm files, change the pattern to Dir(folder & "*.xls*").
  • If the macro workbook is saved in the same folder, wrap the loop body in If f <> ThisWorkbook.Name Then … End If so it doesn't try to open itself.

To run the macro automatically whenever the workbook opens, call it from a Workbook_Open procedure in the ThisWorkbook module. Macros only run if the file is trusted, so files received by email or downloaded from the internet may have macros blocked until the user unblocks the file or it is saved in a trusted location.

Option 3: Power Automate on a schedule

If the files live in OneDrive or SharePoint, a cloud flow can merge them every night: a Recurrence trigger, List files in folder, then for each file read its rows and add them to a master workbook. Excel's connector only reads data formatted as a table, and Office Scripts make large merges much faster. Full walkthrough: merging Excel files with Power Automate.

Which should you choose?

  • Files arrive monthly and someone opens the report anyway → Power Query refresh.
  • Rules Power Query can't express, Windows only → VBA.
  • Nobody should have to open Excel, files are in Microsoft 365 → Power Automate.
  • You only need the merged data occasionally → drop the files into the browser combiner.

Troubleshooting automatic merges

When the automatic merge goes wrong
SymptomCauseFix
Refresh fails with “The key didn't match any rows”A new file's sheet has a different nameUse Source{0}[Data] in Transform Sample File
Rows appear twiceA copy of a file (“sales (1).xlsx”) was saved in the folderRemove the copy; add a duplicate check on an ID column
Refresh works for you but not a colleagueThe query points at your local or synced pathUse From SharePoint Folder or a shared network path
The macro stops on a fileThe file is password-protected or corruptOpen it manually; add error handling that logs and skips it

Frequently asked questions

How do I make Excel combine files automatically?
Set up Power Query From Folder, then in the query's Properties tick Refresh data when opening the file. New files in the folder are included the next time the workbook opens.
Can a macro combine all Excel files in a folder?
Yes. A VBA loop using Dir() can open each file, copy its rows to a master sheet and close it. It needs macros enabled and works best on Windows.
Can Excel merge files on a schedule without opening Excel?
Not by itself. Power Automate can run a scheduled flow that reads files in OneDrive or SharePoint and writes their rows into a master workbook.

Related guides