How to combine multiple Excel files into one automatically

Pick the level of automation

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.

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.

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.

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

• Alternatives to the Ablebits Merge Tables Wizard
• Power Query vs Analistable for combining files
• How to combine multiple Excel files into one spreadsheet
• How to combine two Excel files into one
• How to combine Excel files with different columns