Analistable

The best tools to merge Excel files, compared

By the Analistable team · Updated · 6 min read

For most people the best tool is already installed: Power Query in Excel merges a folder of files and refreshes in one click. Add-ins such as Ablebits and Kutools add point-and-click wizards on Windows. Python suits very large or scheduled jobs. A browser tool is quickest for a one-off merge — choose one that processes files locally.

Part of our guide: How to merge Excel files: every method compared

How we compared them

We compared each option on the features that decide the choice: whether it stacks and joins, whether it repeats without redoing the work, which platforms it runs on, and where your data goes. Details come from each product's documentation; check current pricing and licences on the vendor's site.

First, which kind of merge do you need?

“Merge Excel files” covers three different jobs, and the best tool depends on which one you mean:

Three kinds of merge
JobExampleStrongest tools
Stack rows from files with the same columnsTwelve monthly sales exports into one listPower Query From Folder, VSTACK, browser combiners
Join files side by side on a keyAdd each customer's region from the CRM export to the ordersXLOOKUP, Power Query Merge, add-in wizards, pandas
Summarise several files into one totalAdd up identical budget sheets from five departmentsExcel's Consolidate command, SUM across sheets, pivot tables

Most online mergers only stack. If you need a join, check that the tool can match on a column before you start. For summarising, Excel's built-in Consolidate command is often enough.

Comparison

Tools for merging Excel files
ToolStack filesJoin on a keyRepeatablePlatformsData stays local
Excel Power QueryYesYes (Merge Queries)YesWindows, Mac (Microsoft 365)Yes
VBA macroYesWith codeYesWindows (Mac partly)Yes
Power AutomateYesWith effortScheduledCloud (Microsoft 365)Within your tenant
Ablebits add-inYesYes (Merge Two Tables)Saved scenariosWindows; some tools for Mac/webYes
Kutools for ExcelYes (Combine)YesPartlyWindowsYes
Python (pandas)YesYesYesAnyYes
Typical online mergerYesRarelyNoBrowserUsually uploaded
AnalistableYesYes (ask in plain language)Re-drop or reconnectBrowserYes

Power Query: the default choice

Built into Excel 2016 and later. From Folder stacks every file in a folder, Merge Queries joins tables, and Refresh repeats everything. The learning curve is a few minutes for simple merges. See combining files from a folder.

Add-ins: Ablebits and Kutools

Paid Excel add-ins that wrap common tasks in wizards — combining sheets, merging tables by key, removing duplicates. Useful if you do these tasks daily on Windows and prefer dialogs to queries. Both are installed on each computer, so check with IT first. Our Ablebits alternative page covers when a browser tool is enough.

Code: VBA and Python

VBA runs inside Excel and can do anything Excel can, but macro-enabled files are often blocked by email filters and security policies. Python with pandas handles millions of rows and runs on a schedule, but needs an installation and someone who can maintain the script. See merging DataFrames in pandas.

Online tools

Fast and free, but most upload your files to a server. Check before using one with personal or confidential data — our guide to merging without uploading shows how. Analistable's free combiner processes files in your browser, and the full product lets you ask questions across the combined data.

Recommendation by situation

  • Same merge every month → Power Query.
  • Daily Excel power user on Windows who wants wizards → an add-in.
  • Millions of rows or a scheduled pipeline → Python.
  • Quick one-off, sensitive data → a browser-local tool.

The same job in each tool

Task: combine twelve monthly sales workbooks, each with the same columns, into one table.

What the work looks like
ToolWhat you doNext month
Power QueryFrom Folder → Combine & Transform → choose the sheet → Close & LoadSave the file in the folder, Refresh All
VSTACK (Excel 365)Copy all sheets into one workbook, then one VSTACK formula over a 3D referenceCopy the new sheet into the block
Add-in wizardOpen the combine wizard, tick the workbooks and sheets, choose optionsRun the wizard again, or reuse a saved scenario where offered
PythonLoop over the files with pandas read_excel, then concat and saveRun the script again
Browser toolDrop the twelve files and download the combined fileDrop the files again

The real difference is the second column of effort: Power Query and Python cost more the first time and almost nothing afterwards, while wizards and browser tools cost the same small amount every time.

Built-in Excel options people overlook

  • VSTACK and HSTACK (Microsoft 365) stack ranges with a formula that updates when the sources change. See dynamic arrays for combining data.
  • Consolidate (Data → Consolidate) sums or averages identically laid-out ranges from several sheets or workbooks into one summary.
  • The Data Model (Windows) relates tables without merging them, so a pivot table can use columns from both. See Excel Data Model relationships.
  • Move or Copy puts sheets from several workbooks into one file, which is sometimes all “merge” really means.

When the files are large

An Excel worksheet holds 1,048,576 rows. When the combined data could exceed that, choose a tool that doesn't need to load the result into a sheet: Power Query loading to the Data Model, Power BI, Python, or a database such as DuckDB. Add-ins and VBA write into worksheets, so they share the sheet limit. If the files are CSV rather than Excel, the options widen; see merging large CSV files.

If some of the files are Google Sheets

Excel-based tools need Google Sheets downloaded first, which creates a copy that goes stale. If the data lives in Google Sheets, merge it there with IMPORTRANGE or QUERY (merging Google spreadsheets), or use a tool that reads Google Sheets and Excel files together, such as the Sheets & Excel combiner.

Questions to ask before you choose

  1. Is this a one-off or will it repeat? Repeating jobs justify Power Query or a script.
  2. Do I need to stack, join or summarise? Rule out tools that can't do the job.
  3. Are the columns identical in every file? If not, choose a tool that matches columns by header, not position.
  4. Will the result fit in a worksheet?
  5. Does the data include personal or confidential information? If so, avoid tools that upload files.
  6. Can I install software on this computer, or is IT approval needed for add-ins and Python?
  7. Who will maintain it when I'm away? A wizard or a documented query is easier to hand over than a macro.

Frequently asked questions

What is the best free way to merge Excel files?
Power Query, built into Excel 2016 and later, is free and repeatable. For a quick one-off, a browser-local tool needs no setup.
Do I need an add-in to merge Excel files?
No. Excel's Power Query covers stacking and joining. Add-ins mainly make these tasks quicker through wizards.
Which tools can merge Excel files on a Mac?
Power Query in Microsoft 365 for Mac, Python, and browser tools all work on a Mac.
Which tool can merge Excel files with different column orders?
Any tool that matches columns by header name: Power Query Append or Table.Combine, pandas concat, and browser combiners that align headers. Copy-paste, VSTACK and simple macros match by position instead.
What if the combined file has more than a million rows?
A worksheet can't hold it. Load the result to Excel's Data Model, use Power BI, or merge with Python or a database instead.

Related guides