Merge Excel Files into One

Merge multiple Excel files into one workbook

Keeping each source file as its own tab is the safest merge: nothing is reshaped, and you can always see which numbers came from where. Pick Into one workbook in the tool above and every sheet from every file you dropped becomes a tab in one .xlsx, named after its source. Sheets that would collide get the file name prefixed, so Sheet1 from three different files stays three distinct tabs.

Use this when the files are genuinely different things — a budget, a headcount plan and an actuals export — or when you want one file to send rather than one table to analyse. When the files are the same shape repeated over time, stacking them into one sheet is what you actually want.

Combine Excel files into one sheet (append the rows)

Twelve monthly exports with identical columns are twelve slices of one table. Choose Into one sheet and the rows are stacked into a single sheet, with columns matched by header name rather than by position — so a file that happens to put Region before Amount still lands in the right columns. A column that only some files carry is kept and left empty elsewhere, so combining Excel files into one never silently drops data.

Leave Add a column with the source file name ticked. Once the rows are pooled, that column is the only thing that can tell you which file a bad row came from — and it makes a pivot table by month or by region free.

How to combine Excel files by copy and paste

For three or four files, manual still wins. Open the first file, then in each other file select the data without the header row (Ctrl+Shift+End from the first data cell), copy, and paste into the first free row of the master sheet. Two things go wrong here often enough to be worth naming: pasting over the last used row instead of under it, and files whose columns are in a different order. Sort the columns to match before you paste, or use one of the methods above, which match on the header instead.

How to merge two Excel spreadsheets by a common column

If the two sheets describe the same records and you want to widen them — orders plus the customer details for each order — you want a join, not an append. With Excel 365, XLOOKUP is the clearest way to merge two Excel spreadsheets on a shared key:

=XLOOKUP(A2, Sheet2!$A:$A, Sheet2!$B:$D, "not found")

That returns columns B:D from Sheet2 for the ID in A2, and writes not found instead of #N/A where there is no match — which matters, because #N/A quietly poisons every SUM downstream. On older Excel the equivalent is =VLOOKUP(A2,Sheet2!$A:$D,2,FALSE), with the caveat that the key has to be the leftmost column of the lookup range.

For more than a couple of columns, Power Query’s Merge Queries is better: it brings all of them across in one step and lets you pick the join type (left outer keeps every row on the left, inner keeps only matches, full outer keeps everything).

How to merge Excel files with Power Query

Power Query is the right answer when the merge repeats. Set it up once and a refresh redoes the whole thing, including files that did not exist when you built it.

1. Put the files to merge in one folder, and nothing else.
2. Data → Get Data → From File → From Folder, then choose that folder.
3. Press Combine & Transform Data and pick the sheet or table to take from a sample file. Power Query builds the helper queries for you.
4. Clean up types in the editor if needed, then Close & Load.
5. Drop a new file into the folder and hit Data → Refresh All — it is picked up.

To append two sheets already open in one workbook instead, load each with Data → From Table/Range and then use Home → Append Queries. The folder connector is a Windows Excel feature; Power Query on Mac supports fewer connectors, so on a Mac the tool above or VSTACK is usually the faster route.

How to merge Excel sheets with VSTACK

On Excel 365 and Excel 2024, VSTACK merges Excel sheets with a formula that stays live — edit a source sheet and the merged range updates itself:

=VSTACK(Jan!A2:D200, Feb!A2:D200, Mar!A2:D200)

Point each range at the data rows only, so the headers are not repeated mid-table. Because the ranges are padded out to row 200, you will get empty rows wherever a month was short; wrap the result to drop them:

=LET(s, VSTACK(Jan!A2:D200, Feb!A2:D200, Mar!A2:D200), FILTER(s, CHOOSECOLS(s,1)<>""))

The limits worth knowing: VSTACK aligns by position, not by header, so the sheets must already have their columns in the same order, and it needs the source sheets in the same workbook (a reference into a closed workbook will not recalculate).

Merge Excel files and remove duplicates

Pooling exports that overlap — say two reports that both contain the last week of March — leaves duplicate rows. Merge first, de-duplicate second: select the merged range and use Data → Remove Duplicates, ticking only the columns that actually define a duplicate (an order ID, usually — not every column, or two rows that differ by a stray trailing space will both survive).

If you would rather see the overlap before deleting anything, that is the job of the find duplicates in two columns guide: conditional formatting to highlight them, or =COUNTIF(B:B,A2)>0 to flag and filter them into a list first.

Doing this in the browser

The merger at the top of this page reads .xlsx, .xlsm and .csv, matches columns by header, and writes a real .xlsx — all with your files staying on your own machine. If the merged table is where the work actually starts, the same data opens in Tablize, an online data table with a formula panel, so you can filter, pivot and chart the pooled rows without going back to desktop Excel.

Related guides on this site: compare two Excel files for spotting what changed between them, and compare two Excel sheets for differences inside one workbook.

FAQ

Can I merge two Excel spreadsheets and remove duplicates?

Yes — merge first, then de-duplicate. Stack both sheets into one (with the tool above, Power Query’s Append Queries, or VSTACK), then select the merged range and use Data → Remove Duplicates, ticking only the columns that define a duplicate. In Power Query the same step is Home → Remove Rows → Remove Duplicates, and it re-runs every time you refresh.

How do I merge multiple Excel files into one?

For a one-off, drop the files into the tool above and pick “Into one workbook” to keep each file as its own sheet, or “Into one sheet” to stack all the rows together. For a folder you will merge again and again, Power Query’s Data → Get Data → From File → From Folder is worth setting up once, because a refresh picks up new files automatically.

What happens if my Excel files have different columns?

The tool matches columns by header name rather than by position, so files whose columns are in a different order still line up. A column that only some files have is kept and left empty for the rows that do not have it — nothing is dropped. Power Query’s Append Queries behaves the same way.

Can I combine Excel files without Power Query?

Yes. With Excel 365 or Excel 2024, =VSTACK(Sheet1!A2:D100, Sheet2!A2:D100) stacks ranges into one live array. Without dynamic arrays, copy-paste still works for a handful of files — put the columns in the same order first, and paste under the last used row rather than over it.

Is it safe to merge Excel files online?

It depends on the tool. Many online mergers upload your workbook to a server, which is a problem for anything with customer, payroll or financial data in it. The merger on this page reads and writes the files entirely in your browser — the spreadsheets never leave your machine, and there is no signup.

Can I merge Excel sheets from different workbooks by a shared ID?

That is a join, not an append. Use =XLOOKUP(A2, [Book2]Sheet1!$A:$A, [Book2]Sheet1!$B:$B, "not found") to pull matching values across, or Power Query’s Merge Queries when you want to bring several columns over at once and choose the join type.