Analistable

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?

Methods for merging Excel files compared
MethodBest forRepeatableWorks on MacSkill needed
Copy and paste2–3 small files, onceNoYesNone
Move or Copy sheetCollecting tabs into one workbookNoYesNone
Power Query: From FolderStacking many files every week or monthYes (Refresh)Yes, Microsoft 365Low
VSTACK formulaStacking tabs inside one workbookYes (live)Yes, Microsoft 365Low
VBA macroCustom rules on Windows desktopsYesPartlyHigh
Power Automate + Office ScriptsFiles in OneDrive or SharePoint on a scheduleYes (scheduled)Web-basedMedium
Browser combinerQuick one-off merges without formulas or uploadsRe-drop filesYesNone

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)

  1. Open the destination workbook and every source workbook.
  2. In a source workbook, right-click the sheet tab and choose Move or Copy….
  3. In To book, pick the destination workbook, tick Create a copy, and click OK.
  4. 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.

  1. Put only the files you want to merge in one folder, and close them.
  2. Choose Data → Get Data → From File → From Folder, browse to the folder and click OK (Open on some versions).
  3. The preview lists every file with its name, extension, modified date and folder path. Click Combine → Combine & Transform Data.
  4. In the Combine Files dialog, pick the sheet or table to use from the sample file (the first file by default) and click OK.
  5. 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
    Combined

Excel.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 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 Sub

The 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

Symptoms, causes and fixes when merging a folder of workbooks
SymptomLikely causeFix
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 nameRename the column in the source file, or edit the step that names it
Every row appears twiceThe same file is in the folder twice (for example “Sales - Copy.xlsx”), or the output was saved into the source folderRemove copies and keep the merged workbook in a different folder
One file's columns are blankIts headers are spelled differently, so its values land in separate columnsRename headers in Transform Sample File, or fix them at source
Dates show as five-digit numbersThe column was typed as a number, or one file stores dates as textSet the column type to Date after the files are combined
“DataFormat.Error” on refreshText such as “n/a” in a column typed as a numberClean the value at source, or use Replace Errors on that column
Files from last year appearFrom Folder reads subfolders tooFilter 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:

Row counts before and after the merge
FileData rows in fileRows in merged tableDifference
North.xlsx1,2401,241+1
South.xlsx9809800
West.xlsx1,1051,106+1
Total3,3253,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

  1. Agree a file-naming pattern with whoever sends the files, such as Sales_2026-10_North.xlsx, so the source column sorts by period.
  2. Save each new file into the source folder; move last year's files to an archive folder outside it.
  3. Open the merged workbook and click Data → Refresh All.
  4. Check the per-file row counts and totals in your checking pivot table.
  5. 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

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.