How to merge Excel files: every method compared
By the Analistable team · Updated · 13 min read
To merge Excel files, decide first whether you want one workbook with many tabs or one sheet with all the rows. For a few files, use Move or Copy (tabs) or copy-paste (rows). For files you merge every week, use Power Query's From Folder, which refreshes in one click. To avoid formulas entirely, drop the files into a browser tool such as our free Excel combiner.
First decide what “merged” should look like
“Merge Excel files” covers three different jobs, and the right method depends on which one you need:
- Stack — every file has the same columns (monthly sales, regional exports) and you want one long table. This is the most common case.
- Collect tabs — you want one workbook that contains each file as its own sheet, without changing the data.
- Join — the files hold different information about the same things (orders in one, customers in another) and you want to line them up side by side by an ID. That is a lookup or join, covered in our guide to joining spreadsheets on a common column.
Which method should you use?
| Method | Best for | Repeatable | Works on Mac | Skill needed |
|---|---|---|---|---|
| Copy and paste | 2–3 small files, once | No | Yes | None |
| Move or Copy sheet | Collecting tabs into one workbook | No | Yes | None |
| Power Query: From Folder | Stacking many files every week or month | Yes (Refresh) | Yes, Microsoft 365 | Low |
| VSTACK formula | Stacking tabs inside one workbook | Yes (live) | Yes, Microsoft 365 | Low |
| VBA macro | Custom rules on Windows desktops | Yes | Partly | High |
| Power Automate + Office Scripts | Files in OneDrive or SharePoint on a schedule | Yes (scheduled) | Web-based | Medium |
| Browser combiner | Quick one-off merges without formulas or uploads | Re-drop files | Yes | None |
Method 1: copy and paste (a few files, once)
Open the first file, select the data including headers, and paste it into a new sheet. For every other file, copy the rows without the header row and paste them under the last row. Add a column that says which file each block came from before you forget.
It works for two or three files. Beyond that, the risk of skipping rows or pasting a header twice grows quickly. Our step-by-step guide to combining two Excel files into one shows the safe order of operations.
A few habits make a manual merge much safer:
- Click any cell in the source data and press Ctrl+A (Cmd+A on a Mac) to select the whole block, rather than dragging, so no rows are left behind.
- Paste with Paste Special → Values when the source contains formulas. Otherwise formulas that point at other sheets or workbooks can break, or keep linking back to the source file.
- Before pasting, compare the header rows of the two files side by side. Copy-paste matches columns by position, so a file with Region and Product swapped will put products under Region without any warning.
- Write down each file's row count before you start and check the total at the end (see the worked example below).
Method 2: Move or Copy (one workbook, many tabs)
- Open the destination workbook and every source workbook.
- In a source workbook, right-click the sheet tab and choose Move or Copy….
- In To book, pick the destination workbook, tick Create a copy, and click OK.
- Repeat for each sheet, then rename tabs so their source is obvious.
To move several tabs at once, hold Ctrl (Cmd on a Mac) and click each tab before choosing Move or Copy. If the destination already has a tab with the same name, Excel names the copy “Sales (2)” or similar, so rename duplicates straight away. If the source sheets contain formulas that refer to other tabs in their own workbook, the copies keep pointing back to the original file; check Data → Edit Links (Workbook Links in recent Microsoft 365 versions) afterwards and break any links you don't want.
You end up with one file that has many tabs, which is not the same as one table. If the goal is a single sheet, continue with combining sheets into one.
Method 3: Power Query From Folder (the repeatable way)
Put every file in one folder, then in Excel choose Data → Get Data → From File → From Folder, pick the folder and click Combine & Transform Data. Power Query reads the first file as a sample, applies the same steps to all of them and stacks the result into one table, adding a Source.Name column.
Next month, drop the new file into the folder and click Data → Refresh All. This is the best method when the same merge happens every period. It is available in Excel for Windows, and in Microsoft 365 for Mac. See combining files from a folder with Power Query for every step, including how to handle files whose columns differ.
- Put only the files you want to merge in one folder, and close them.
- Choose Data → Get Data → From File → From Folder, browse to the folder and click OK (Open on some versions).
- The preview lists every file with its name, extension, modified date and folder path. Click Combine → Combine & Transform Data.
- In the Combine Files dialog, pick the sheet or table to use from the sample file (the first file by default) and click OK.
- In the Power Query Editor, check each column's data type, filter out anything that isn't data, then click Close & Load.
Behind the scenes Power Query creates a helper group containing a Sample File, a parameter, a Transform File function and a Transform Sample File query. Steps you add to Transform Sample File, such as removing two title rows or promoting headers, run against every file in the folder, so clean-up belongs there rather than in the final combined query.
Two details catch people out. First, From Folder includes files in subfolders, so an “Archive” folder inside the source folder gets merged too; filter the Folder Path column to exclude it. Second, while a workbook in the folder is open, Excel creates a hidden lock file whose name starts with ~$; filter the Name column to exclude names that begin with those characters, or the refresh may fail.
The Combine Files dialog takes one sheet from each file. When every workbook has several sheets and you want all of them, start from a blank query (Data → Get Data → From Other Sources → Blank Query), open the Advanced Editor and paste a query like this, changing the folder path:
let
Source = Folder.Files("C:\Data\Sales"),
Workbooks = Table.SelectRows(Source, each [Extension] = ".xlsx" and not Text.StartsWith([Name], "~$")),
WithSheets = Table.AddColumn(Workbooks, "Sheets", each Excel.Workbook([Content], true)),
Expanded = Table.ExpandTableColumn(WithSheets, "Sheets", {"Name", "Data", "Kind"}, {"Sheet", "Data", "Kind"}),
OnlySheets = Table.SelectRows(Expanded, each [Kind] = "Sheet"),
Tagged = Table.AddColumn(OnlySheets, "Tagged", each Table.AddColumn([Data], "Source", (r) => [Name] & " / " & [Sheet])),
Combined = Table.Combine(Tagged[Tagged])
in
CombinedExcel.Workbook([Content], true) reads each workbook and uses the first row of each sheet as headers, and Table.Combine stacks the sheets by column name, so a column that exists in only some sheets is kept and left blank elsewhere. The Source column records both the file and the sheet each row came from.
Method 4: formulas, macros and automation
- VSTACK (Microsoft 365, Excel 2024) stacks ranges into one live array:
=VSTACK(Jan:Mar!A2:D100). It works across tabs in one workbook, not across closed files. See dynamic arrays for combining data. - VBA can open every file in a folder and copy its rows. It is flexible but needs macro-enabled files and maintenance.
- Power Automate with Office Scripts can merge files saved in OneDrive or SharePoint on a schedule. See merging Excel files with Power Automate and Office Scripts to combine workbooks.
VSTACK is the right choice when the “files” have already been gathered as tabs in one workbook, for example after Method 2. Because it is a formula, the combined range grows as soon as rows are added to any tab, with no refresh. Its limits: it matches columns by position, not by header, and the referenced workbooks must be open, so it is a poor fit for a folder of files that changes every month.
A VBA macro for a folder of workbooks
If Power Query is not an option, for example because your files need custom handling before merging, a short macro does the job on Excel for Windows. Save it in a macro-enabled workbook (.xlsm) stored outside the source folder, create a sheet called Merged with your headers in row 1 starting from column B (column A will hold the file name), and run it:
Sub MergeWorkbooksInFolder()
Dim folderPath As String, fileName As String
Dim target As Worksheet, src As Workbook
Dim nextRow As Long, lastRow As Long, lastCol As Long
folderPath = "C:\Data\Sales\"
Set target = ThisWorkbook.Worksheets("Merged")
nextRow = 2 ' row 1 holds the headers
Application.ScreenUpdating = False
fileName = Dir(folderPath & "*.xlsx")
Do While fileName <> ""
Set src = Workbooks.Open(folderPath & fileName, ReadOnly:=True)
With src.Worksheets(1)
lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
If lastRow > 1 Then
.Range(.Cells(2, 1), .Cells(lastRow, lastCol)).Copy target.Cells(nextRow, 2)
target.Range(target.Cells(nextRow, 1), target.Cells(nextRow + lastRow - 2, 1)).Value = fileName
nextRow = nextRow + lastRow - 1
End If
End With
src.Close SaveChanges:=False
fileName = Dir()
Loop
Application.ScreenUpdating = True
End SubThe macro copies rows 2 to the last filled row of each file's first sheet, writes the file name beside them and moves on. It assumes every file has the same columns in the same order and that column A is always filled, because that is how it finds the last row. Run it on a fresh Merged sheet each time; running it twice without clearing the sheet first leaves the earlier rows in place and appends a second set below them, which you will spot as doubled totals.
Method 5: a browser tool, with no formulas and no upload
If you need the merged table now and won't repeat the job, drop the files into the Analistable Excel combiner. It reads every sheet, lines up columns by header name, adds a source column and lets you download one CSV. The files are processed inside your browser, so nothing is uploaded — useful for payroll, customer or finance data. We explain why that matters in merging Excel files without uploading them.
Problems that break a merge
- Headers that differ slightly (“Order ID” vs “OrderID”) create two columns. Rename them to match first, or read combining files with different columns.
- Extra rows above the header (titles, notes, blank lines) shift every column. Remove them, or skip them in Power Query with Remove Top Rows.
- Dates and IDs stored as text in some files but as numbers in others stop sorting and lookups working. Set one data type per column after merging.
- Excel's row limit is 1,048,576 rows per sheet. Larger merges need Power Query's data model, a CSV, or a database.
Keep a source column. When a number looks wrong later, it tells you which file to check.
Troubleshooting a Power Query merge
| Symptom | Likely cause | Fix |
|---|---|---|
| Refresh fails with “The column 'X' of the table wasn't found” | A new file renamed or dropped a column that a step refers to by name | Rename the column in the source file, or edit the step that names it |
| Every row appears twice | The same file is in the folder twice (for example “Sales - Copy.xlsx”), or the output was saved into the source folder | Remove copies and keep the merged workbook in a different folder |
| One file's columns are blank | Its headers are spelled differently, so its values land in separate columns | Rename headers in Transform Sample File, or fix them at source |
| Dates show as five-digit numbers | The column was typed as a number, or one file stores dates as text | Set the column type to Date after the files are combined |
| “DataFormat.Error” on refresh | Text such as “n/a” in a column typed as a number | Clean the value at source, or use Replace Errors on that column |
| Files from last year appear | From Folder reads subfolders too | Filter the Folder Path column, or move archives elsewhere |
Worked example: checking a merge of three regional files
Three regional workbooks with the same columns (Order ID, Date, Product, Amount) are merged with From Folder. Before trusting the result, count the rows per file. In the merged table, a pivot table with Source.Name in Rows and Count of Order ID in Values gives the per-file counts; compare them with the number of data rows in each original file:
| File | Data rows in file | Rows in merged table | Difference |
|---|---|---|---|
| North.xlsx | 1,240 | 1,241 | +1 |
| South.xlsx | 980 | 980 | 0 |
| West.xlsx | 1,105 | 1,106 | +1 |
| Total | 3,325 | 3,327 | +2 |
Two extra rows. Filtering the merged table for blank Order IDs finds one empty row from North.xlsx, where someone had formatted a row below the data, and one row from West.xlsx whose Product cell says “Total”. Both are fixed once, in Transform Sample File, with a filter that keeps only rows where Order ID is not blank and Product is not “Total”. After refreshing, the merged table holds 3,325 rows, matching the three files.
Row counts catch missing and extra rows; they don't catch values that changed type on the way in. So also total a numeric column per file, in the source and in the merged table. If the amounts match too, the merge is complete.
Repeating the merge every month
- Agree a file-naming pattern with whoever sends the files, such as
Sales_2026-10_North.xlsx, so the source column sorts by period. - Save each new file into the source folder; move last year's files to an archive folder outside it.
- Open the merged workbook and click Data → Refresh All.
- Check the per-file row counts and totals in your checking pivot table.
- Only then refresh the reports or pivot tables built on the merged table.
If the people sending files sometimes add or rename a column, ask them for a template with locked headers. Most failed refreshes come from a header that changed, not from Power Query itself. For a set-up that runs with no one clicking Refresh, see combining Excel files automatically.
Which method for which situation
- Two files, once, same columns → copy and paste, then check row counts.
- A workbook you need to send with each file as its own tab → Move or Copy.
- Twelve monthly files now, one more every month → Power Query From Folder.
- Tabs already in one workbook, and the totals should update as you type → VSTACK.
- Files arrive by email or in SharePoint and nobody should have to open Excel → Power Automate with Office Scripts.
- Sensitive data, no time to build a query, or no Power Query on the machine → a browser combiner that runs locally.
For a feature-by-feature look at the add-ins and apps that do this, see the best tools to merge Excel files and Power Query vs Analistable for combining files.
Every guide in this topic
- Alternatives to the Ablebits Merge Tables Wizard
Alternatives to Ablebits Merge Tables Wizard for matching and merging Excel tables: XLOOKUP, Power Query Merge, and Analistable — features and trade-offs.
- E-commerce marketplace sales roll-up template
Free workbook that stacks Amazon, eBay and Etsy order exports and rolls them up by marketplace and SKU: orders, units, fees, refunds and net sales.
- Excel “file format and extension don’t match” when merging files
The file format and extension don't match error means an .xls is really HTML, XML or CSV. Identify the real format and convert it before you merge.
- How to combine Excel files on a Mac
Combine Excel files on a Mac with Power Query From Folder in Microsoft 365, Move or Copy, or a browser tool, plus what's different from Excel for Windows.
- How to combine Excel files with different columns
Merge Excel files whose columns differ in order, name or number. Stop Power Query dropping columns, align headers fast, or combine them free online.
- How to combine multiple Excel files into one automatically
Three ways to merge Excel files automatically: Power Query refresh on open, a VBA macro that loops through a folder, or a scheduled Power Automate flow.
- How to combine multiple Excel files into one spreadsheet
Combine multiple Excel files into one sheet with Power Query From Folder: every step, how to handle different sheet names, and a no-formula alternative.
- How to combine two Excel files into one
Combine two Excel files into one: stack rows when columns match, copy sheets into one workbook, or join side by side on a shared ID. Steps for each case.
- How to combine workbooks with Office Scripts
Use two Office Scripts and a Power Automate flow to combine Excel workbooks in OneDrive or SharePoint quickly, with copy-ready TypeScript code.
- How to merge data from closed workbooks in Excel
Pull data from closed Excel workbooks: what external references can do, why INDIRECT returns #REF!, and when Power Query is the better route.
- How to merge Excel files with Power Automate
Build a Power Automate flow that merges Excel files from OneDrive or SharePoint into one master table on a schedule, plus the limits that trip people up.
- How to merge Excel files without uploading them
Most online Excel mergers upload your files. Learn which methods keep data on your computer, how to check whether a tool uploads, and why it matters.
- Kutools Combine alternatives: built-in Excel and browser options
Kutools for Excel's Combine wizard against built-in Excel features and browser tools: what each handles, platform limits and when the add-in is worth it.
- Merging files larger than Excel’s row limit
Merged data over 1,048,576 rows, Excel crashing or a file too large to open? Load to the Data Model, keep queries as connections, or query the files instead.
- Power Query vs Analistable for combining files
Power Query or Analistable for combining spreadsheets? Compare setup, repeatability, joins, Google Sheets support, privacy and when each is the better choice.
- The best tools to merge Excel files, compared
Excel's Power Query, VBA, Power Automate, add-ins like Ablebits and Kutools, Python and browser tools for merging Excel files: features, limits and fit.
Frequently asked questions
- How do I merge multiple Excel files into one?
- Put the files in one folder and use Data → Get Data → From File → From Folder → Combine & Transform Data in Excel. For a one-off merge without formulas, drop them into a browser combiner that stacks the rows for you.
- Can I merge Excel files without opening each one?
- Yes. Power Query's From Folder reads every workbook in a folder without you opening them, and a browser combiner reads them when you drop them in.
- How do I merge Excel files with different columns?
- Match columns by header name rather than position. Power Query and the Analistable combiner both do this: a column missing from one file is left blank for that file's rows.
- What is the difference between merging and consolidating?
- Merging puts all rows together. Consolidating also summarises them — for example, totals per product across all files. Excel's Consolidate command and pivot tables do the summarising.
- Can I merge Excel files on a Mac?
- Yes. Move or Copy and copy-paste work the same way as on Windows, and Microsoft 365 for Mac includes Power Query. VBA macros that open other workbooks may need changes for Mac file paths. See our guide to combining Excel files on a Mac.
- How do I merge Excel files that each have several sheets?
- Power Query's Combine Files dialog takes one sheet per file. To take every sheet from every file, use a blank query with Folder.Files, Excel.Workbook and Table.Combine, as shown above, which also records the file and sheet name for each row.