Merge Excel files into one sheet or one workbook

One sheet or one workbook?

• All rows in one sheetFor monthly exports, branch reports or survey downloads that share the same columns. The result is one table you can filter, sort or pivot.
• One sheet per fileFor collecting separate workbooks into a single file with tabs, such as one tab per department or per supplier. Each tab is copied as it is, without mixing rows.

Columns in a different order are matched by name

Files from different people rarely have identical columns. When the first row holds column names, columns are matched by name, ignoring upper and lower case and extra spaces, so “Amount” in one file and “ amount” in another become one column wherever they sit. The result has every column that appears in any file, in order of first appearance. Cells of a file that lacks a column stay empty, and the tool lists those columns so you can spot a misspelled header before you use the data.

If the files have no header row, untick the option and rows are stacked by position: column A under column A, B under B.

Merge and remove duplicates

When the same record appears in two files, such as an order exported twice, tick “Remove exact duplicate rows”. A row is removed only when every merged column has the same value as an earlier row. The source columns are not taken into account, so a row repeated in two files counts as a duplicate. For duplicates by one or a few columns (an email, an ID), or with case and accent differences, use Remove duplicates on the merged file.

What is kept, and what is not

• Always kept: cell values, dates, and number formats such as currency, percentages or leading zeros stored as text.
• Formulas are replaced by their last calculated value, because references would point to the wrong cells after merging.
• One sheet per file also keeps column widths and merged cells. With “Keep cell formatting” it copies fonts, colours, fills, borders, alignment, row heights, frozen panes and cell notes from .xlsx and .xlsm files.
• Not copied: charts, images, pivot tables, conditional formatting, data validation and macros. Colours and fonts are not kept in “All rows in one sheet” mode.
• CSV files become real numbers when the value is clearly numeric (a comma decimal is understood when the file uses semicolons). Codes with leading zeros and long IDs stay as text.

How to merge Excel files without a tool

Excel can do it, with some effort:

• Power Query: Data › Get Data › From File › From Folder, then “Combine & Transform”. It is powerful and refreshable, but the files must sit in one folder and have consistent columns, and the steps differ between Windows and Mac.
• Move or Copy: right-click a sheet tab › Move or Copy › choose the target workbook. It works for a few tabs, one at a time, and keeps formatting.
• Copy and paste each range under the previous one. Fine for two files, error-prone for twenty, and it does not align columns in a different order.

This tool is faster when you just need the result once, on any computer, including Mac, Chromebook or a phone.

Known limitations

• The limit is your device’s memory: hundreds of thousands of rows work on a recent computer; on phones keep the total under about 20 MB.
• A single Excel sheet holds 1,048,576 rows. If the merged rows do not fit, they continue in a second sheet (“Merged 2”).
• Password-protected (encrypted) files cannot be opened. Remove the password in Excel first.
• The header must be in the first non-empty row of each sheet. Title rows above the header become data rows.
• Sheet names are shortened to 31 characters and made unique, as Excel requires.