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
| Method | Runs when | Needs | Best for |
|---|---|---|---|
| Power Query refresh | You open the file or click Refresh | Excel 2016+ or Microsoft 365 | Most people |
| VBA macro | You click a button | Windows desktop Excel, macros enabled | Custom rules |
| Power Automate | On a schedule or when a file arrives | Microsoft 365 business account, OneDrive/SharePoint | Unattended merges |
Option 1: Power Query that refreshes itself
- Build the combine with Data → Get Data → From File → From Folder → Combine & Transform Data (full steps in combining Excel files into one spreadsheet).
- In Data → Queries & Connections, right-click the combined query and choose Properties.
- Tick Refresh data when opening the file. Optionally tick Refresh every N minutes for a workbook that stays open.
- 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
.xlsxfiles 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 SubSave 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 Ifso 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
| Symptom | Cause | Fix |
|---|---|---|
| Refresh fails with “The key didn't match any rows” | A new file's sheet has a different name | Use Source{0}[Data] in Transform Sample File |
| Rows appear twice | A copy of a file (“sales (1).xlsx”) was saved in the folder | Remove the copy; add a duplicate check on an ID column |
| Refresh works for you but not a colleague | The query points at your local or synced path | Use From SharePoint Folder or a shared network path |
| The macro stops on a file | The file is password-protected or corrupt | Open 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.