How to merge multiple Excel files at once
Merging two files is easy. The real question is what to do when you have five, ten, or a whole folder of them — weekly exports, one file per store, one per team. Here are the three approaches that scale, and when each one wins.
Method 1 — Drag them all into an online merge tool
The fastest path for a one-off batch:
- Open SheetToolkit's Merge tool.
- Select all your files at once (or drag the whole selection in) —
.xlsxand.csvcan be mixed. - Pick your output: Append rows (everything stacked into one sheet) or Combine into sheets (one tab per source file).
- Preview, then download the merged
.xlsx.
Two things make this safe with many files: columns are matched by header name (files with different layouts still align), and the optional source column tags every row with the file it came from, so nothing gets lost in the pile. Processing is local — your files never leave your browser.
Method 2 — Power Query for a whole folder
If the files live in one folder and share the same structure, Excel can ingest the folder itself:
- Data → Get Data → From File → From Folder → pick the folder.
- Combine & Transform → Excel builds one combined table.
- Next month, drop the new files in the folder and hit Refresh — no rework.
This is the best method for a recurring pipeline. Its weakness: it assumes consistent headers; renamed or missing columns break the combine step and need manual fixing in the query editor.
Method 3 — VBA, if you need custom logic
A macro can loop through files and copy each one's data into a master sheet, applying any custom rule along the way (filter rows, rename columns, skip sheets). Powerful, but you maintain the code, and macros are often blocked on company machines. Choose this only when Methods 1 and 2 genuinely can't express your logic.
Which one should you pick?
| Your situation | Best method |
|---|---|
| One-off batch, mixed files, want it done now | Online tool |
| Same folder, same structure, every week/month | Power Query |
| Custom transformation rules, comfortable coding | VBA |
FAQ
- Is there a limit to how many Excel files I can merge?
- Online: SheetToolkit merges up to 3 files free, unlimited with Pro. Power Query: limited only by memory.
- Can I merge Excel files with different columns?
- Yes — with header-name matching, each column finds its place and missing columns are left blank. Position-based methods (copy-paste, naive VBA) will misalign them.
- How do I merge Excel files without opening each one?
- Both the online tool and Power Query read the files directly — you never open them individually.
- Is it safe for confidential data?
- SheetToolkit processes files locally in your browser (nothing is uploaded). Power Query and VBA stay on your machine too. Avoid upload-based converters for sensitive data.