Combine Excel Files and Sheets
Four different jobs share the word "combine" here, and picking the wrong one costs an afternoon. Many separate files with matching columns want a folder query: it stays refreshable, and it records which file every row came from. Several sheets inside one existing workbook want the same tool pointed at a different source. One whole sheet moving house is not a query at all — it is a tab sent between two open files, formatting and charts included. And Consolidate, which people find while hunting for the others, stacks nothing whatsoever: it adds matching cells up into a summary block.
The formula
Data > Get Data > From File > From Folder many separate .xlsx files, same columns, one refreshable table
Data > Get Data > From File > From Workbook several sheets inside one other file
Right-click sheet tab > Move or Copy move or duplicate one whole sheet into another open workbook
Data > Consolidate totals across identically laid-out sheets, not a stacked table
Data > Refresh All re-run a Power Query combine after the source files changeA worked example
A folder holds twelve files, jan.xlsx through dec.xlsx, each with one sheet and the same five column headers.
Data > Get Data > From File > From Folder, pick the folder, then Combine & Transform Data
One table of all twelve months in a new sheet, with a Source.Name column reading "jan.xlsx", "feb.xlsx" and so on. Drop a thirteenth file into the folder and Data > Refresh All picks it up without any rework — that refresh is the reason to prefer this over copying and pasting twelve times.
Which one do I need?
| If you want to… | Use |
|---|---|
| Several separate workbook files that share the same column headers | Power Query: Data > Get Data > From File > From Folder > Combine & Transform Data — the only option here that refreshes |
| One sheet you want to move or duplicate into another workbook | Right-click the sheet tab > Move or Copy, pick the target under "To book", and tick "Create a copy" if the original should stay |
| Many sheets inside one workbook that need stacking into a single table | Data > Get Data > From File > From Workbook, select the sheets, then append them in the Power Query editor |
| Identically laid-out sheets where you only want the totals | Data > Consolidate with Sum — it produces a summary block, not a combined row-by-row table |
| Two versions of the same workbook, and you want the differences rather than a merge | Comparing is a separate task — a merge would hide exactly the rows you are looking for |
| The files have different column orders or extra columns | Fix the headers before combining — Power Query appends by column name, so a stray "Qty" against "Quantity" produces two columns full of nulls |
| You're on Excel for Mac | Get Data > From Folder is Excel for Windows only — combine From Workbook one file at a time, or use a macro |
Frequently asked questions
What is the difference between merging sheets and merging workbooks?
Sheets are tabs inside one file, so combining them never involves opening anything else — Move or Copy or Power Query From Workbook handles it. Workbooks are separate files on disk, and combining those means reading each file, which is what Power Query From Folder is built for.
Does Move or Copy combine the data on two sheets?
No. It relocates or duplicates an entire sheet as its own tab; the rows are never stacked together. If the copied sheet contained formulas pointing at the old workbook, those become external links back to the source file, which is why totals sometimes keep changing after the move.
Why does selecting several sheet tabs at once not merge them?
Ctrl-clicking multiple tabs groups them so that typing or formatting applies to all of them at once. It is an editing mode, not a merge, and forgetting to ungroup afterwards is a common way to overwrite data on sheets you were not looking at.
Do I have to redo the combine when the source files change?
Not with Power Query. Data > Refresh All re-reads every file in the folder, including files added since you built the query, and rebuilds the combined table. Copy-and-paste combines have to be redone by hand every time.
New guides and tools, once a month
DE + EN · double opt-in · no spam