“Merge” and “combine” get used for three different jobs in Excel, and picking the wrong method wastes an afternoon. This guide is about the common one: taking data that lives in several files or several sheets and bringing it together into a single list or summary. Joining cells for layout (Merge & Center) and reconciling two edited copies of one workbook are different jobs, linked at the end.
The right method depends on how many sources you have and whether you want every row or just the totals.
Which method fits the job?
| You have | You want | Use |
|---|---|---|
| Many files, same columns | One combined table | Power Query, From Folder |
| A few sheets or files | The rows stacked together | Append in Power Query, or copy and paste |
| Several sheets of numbers | A summary (totals, averages) | Data → Consolidate |
| Sheets spread across workbooks | Them gathered into one file first | Move or Copy sheet |
| Dozens of files, on a schedule | It done automatically | A short VBA macro |
The first three cover almost every case. Power Query is the modern workhorse and the one to learn first, because it refreshes: when the source files change, you click Refresh instead of redoing the work.
How do I combine many Excel files into one?
When you have a folder of files with the same layout — twelve monthly exports, one file per region, a batch of identical forms — Power Query combines them in a few clicks.
First, put only those files in one folder. Power Query includes every file in the folder and its subfolders, so a stray copy or a ~$ lock file gets pulled in too.
Then, in Excel, go to Data → Get Data → From File → From Folder, select the folder, and in the dialog choose Combine → Combine & Transform Data. As Microsoft’s documentation puts it, that will “combine all the files with a query, and then launch the Power Query Editor.” Review the preview, then Close & Load to drop the stacked result onto a sheet.
Two things decide whether the result is clean. Each file “should have the same schema with consistent column headers, data types, and number of columns” — though “the columns do not have to be in the same order as the matching is done by column names.” So headers that agree matter more than column order. And because the query remembers the folder, adding next month’s file and clicking Refresh re-combines everything with no extra work.
How do I stack two sheets, or a few files, together?
For just a couple of sources, you don’t need a folder. If the second sheet’s columns line up under the first, copy its used range and paste it directly below — the low-tech answer that’s completely fine for a one-off.
For something you’ll repeat, load each sheet into Power Query (Data → Get Data → From Other Sources → Blank Query, or From Table/Range) and use Append Queries to stack them into one. Append is the “every row” operation: two lists of 500 rows become one list of 1,000, matched by column name.
If the sheets live in other workbooks, bring them into one file first: right-click the sheet tab, choose Move or Copy, pick the destination workbook, tick Create a copy, and click OK. Now they’re tabs in one file and any method above applies.
How do I combine sheets into a summary instead?
Sometimes you don’t want every row — you want the totals. Twelve regional sheets with the same rows and columns, and you need one sheet with the sums. That’s Consolidate, not append.
On the sheet where the result should go, select the top-left cell of the output area, then click Data → Consolidate (in the Data Tools group). In the Function box pick the summary you want — the default is Sum, and Count, Average, Max, and Min are there too. Add each source range in the Reference box with Add, and tick Top row and/or Left column under “Use labels in” so Excel aligns by your headers.
Consolidate works two ways. By position assumes every source has the same order and labels — the case for sheets built from one template. By category handles sources that “use the same labels” but aren’t “arranged in the same order,” matching on the labels instead. Either way you get one summarized block, not a longer list.
What about combining dozens of files automatically?
When it’s the same job every week over many files, a short VBA macro that opens each file in a folder, copies its rows, and appends them to a master sheet removes the clicks entirely. Power Query’s From Folder already covers most of this without code, so reach for a macro when you need custom logic Power Query can’t express, or an unattended, scheduled run. Either way, keep the source files untouched and write into a fresh master, so a bad run never damages an original.
How do I know the merge didn’t quietly lose data?
This is the step people skip. Combining is easy to get subtly wrong: a header spelled two ways splits one column into two, a file gets left out of the folder, a de-duplicate step removes rows that only looked identical, an append silently drops a column that one file was missing. The combined sheet still looks plausible — it’s just wrong.
The fast check is a comparison. Put the combined workbook next to a source, or next to last period’s combined version, and look at what actually changed: the row counts, the totals, the specific values. A dropped block or a doubled region shows up immediately instead of hiding inside a bigger file.
The short version
To combine files or sheets into one list, Power Query’s From Folder is the repeatable workhorse for many files, Append stacks a few, and plain copy-paste is fine for a one-off. To combine numbers into a summary, use Data → Consolidate with SUM. To gather sheets from different workbooks, Move or Copy them into one file first. Then confirm the result: combining is quiet when it goes wrong, so compare the combined file against a source to be sure nothing was lost.
Two related jobs that aren’t this one: joining cells for layout is merging cells in Excel, and reconciling two edited copies of the same workbook is comparing two Excel files.
