Guides
How to Merge Multiple Excel Files into One (One Table or One Tab Each)
Updated 2026-10-18
A dozen branches send in their monthly sheets and you need one combined file. Or there is one file per month and you want them in a single workbook. First decide which result you want:
- one table: all rows stacked, ready for filtering and pivot tables;
- one tab each: every file (or sheet) becomes its own tab in a new workbook, unchanged.
Method 1: copy and paste
With a few files this is the quickest: open each one, select the data (without the header), copy, and paste below the last row of the combined sheet.
What goes wrong:
- if the columns are in a different order in one file, the data lands in the wrong columns and it's easy to miss;
- a file pasted twice or skipped is hard to spot later;
- nothing tells you which row came from which file unless you add a column by hand.
Method 2: Move or Copy (one tab each)
To give every file its own tab in a new workbook:
- Create a blank workbook and open the files to merge.
- Right-click a sheet tab in a source file → Move or Copy.
- Pick the new workbook under To book, tick Create a copy, OK.
Formatting and formulas come along, but you repeat this for every file, and formulas that point at other sheets may turn into external links to the original file.
Method 3: Power Query From Folder (one table)
In Excel 2016 and later:
- Put the files in one folder.
- Data → Get Data → From File → From Folder, and pick the folder.
- Click Combine → Combine & Load and choose a sample sheet.
The upside: add files to the folder later and a refresh picks them up. The downside: several steps, and when one file has a different layout or a title row above the header you need to fix things in the query editor, which is a hurdle if you've never used it.
Method 4: merge online in one step
With xlszip's merge tool you select all the files and drop them in:
- by default everything goes into one table, with two columns on the left naming the source file and source sheet;
- headers are matched by text, not position: Department, Amount in one file and Amount, Department in another end up in the same columns; a column only some files have gets its own column;
- each sheet's header is the fullest row among its first 10, so title rows above the header stay out of the data;
- tick Keep each sheet as its own tab to get one tab per sheet instead, with formatting, column widths, merged cells and frozen panes copied, named after the file;
- files that can't be read (encrypted, damaged) are skipped and listed in the report.
Keep in mind: formulas become their last calculated values (their references would point at the wrong cells in a new file), and images and charts are not carried over. Up to 100 files at once, 30 MB in total.
After merging
- Insert a pivot table on the combined sheet and group by the source file column.
- Sending it out? Run it through Clean for sharing to remove author info.
- Need the opposite, one master sheet split by department? See Split an Excel sheet by column.