Analistable

How to merge Excel files: every method compared

By the Analistable team · Updated · 5 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.

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.

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.

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.

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.

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.