Yes, you can merge multiple sheets in Excel using Power Query (Get & Transform Data) without writing a single formula or VBA macro. The core method is to load each sheet as a separate query, then use the Append Queries command to stack them into one table. This works for sheets in the same workbook or across different files, and it refreshes automatically when your source data changes.
What Power Query Merge Actually Does
Power Query, available in Excel 2016 and later (including Microsoft 365), is a data transformation engine. The term “merge” is often used loosely; in Power Query there are two distinct operations:
- Append – stacks rows from two or more tables vertically (like putting sheets on top of each other). This is what you need when your sheets contain the same columns, e.g., sales data for January, February, March.
- Merge – joins columns from two tables based on a matching key (like a VLOOKUP but more powerful). Use this when you have customer info on one sheet and orders on another, and you want to bring them together horizontally.
For combining multiple sheets with identical structure, you want Append. If your sheets have different columns and you need to match rows, use Merge.
Step-by-Step: Append Sheets from the Same Workbook
Here’s the exact procedure for Excel 365 on Windows (steps are similar on Mac, but the ribbon is under Data > Get Data).
- Go to Data > Get Data > From File > From Excel Workbook (or From Workbook on Mac).
- Select your workbook file (even if it’s the one you’re working in) and click Import.
- In the Navigator dialog, you’ll see a list of all sheets and tables. Click on the first sheet name, then hold Ctrl and click on the other sheets you want to combine. (On Mac, use Cmd.)
- Click Transform Data (not Load). This opens the Power Query Editor with multiple queries, one per sheet.
- In the Queries pane on the left, select the first query (e.g., “Sheet1”). Then go to Home > Append Queries > Append Queries as New.
- In the dialog, choose “Three or more tables” (if you have more than two) and add the other queries to the right side. Click OK.
- A new query named “Append1” appears. You can rename it (e.g., “AllSheets”). Review the preview, then click Close & Load to place the combined table in a new worksheet.
That’s it. The result is a single table with all rows from all selected sheets. If you later add or change data in any source sheet, right-click the combined table and choose Refresh to update it.
How to Choose Between Append and Merge
Many users confuse these terms. Here’s a quick decision guide:
- You have sales data for each region in separate sheets, all with columns: Date, Product, Units, Revenue → Append them.
- You have a “Products” sheet with ProductID and Price, and an “Orders” sheet with ProductID and Quantity → Merge them on ProductID.
- You have sheets with different column orders or extra columns – Append still works, but you may need to reorder columns after appending.
- You need to combine sheets from different workbooks – you can load each file as a separate query and then Append them, but it’s easier to use Data > Get Data > From File > From Folder if all files are in one folder.
Merging Sheets from Multiple Workbooks (Folder Method)
If you have dozens of Excel files in a folder, each with the same sheet layout, Power Query can combine them all at once:
- Put all files in one folder (and make sure they have the same structure).
- In Excel, go to Data > Get Data > From File > From Folder.
- Browse to the folder and click OK.
- In the dialog, click Combine & Transform Data. Power Query will detect the common sheet (e.g., “Sheet1”) and combine all files.
- It adds a column called “Source.Name” so you know which file each row came from. You can remove it if not needed.
- Click Close & Load.
This method is far faster than manually appending each file, and it handles new files added to the folder automatically on refresh (as long as you refresh the query).
Common Problems and How to Fix Them
Even with the right steps, you might hit these issues:
- Sheets have different column names – Append will still work, but it will create separate columns for each unique name, leaving blanks. Fix: rename columns in each query so they match before appending.
- Data types are inconsistent – e.g., one sheet has “123” as text, another as number. Power Query will often auto-detect, but if you get errors, select the column and change the type in the Transform tab.
- Header rows appear in the middle of data – If a sheet has a title row above the header, you need to remove top rows before appending. Use Home > Remove Rows > Remove Top Rows.
- Append Queries is greyed out – This happens when you haven’t selected a query in the Queries pane. Click on any query first.
- You accidentally loaded each sheet separately – That’s fine; you can still create a new append query from the Queries pane, but it’s easier to start over with the Ctrl+click method.
Power Query vs. Manual Copy-Paste vs. VBA
You have three main ways to combine sheets, and each has trade-offs:
- Manual copy-paste – Fast for a one-time small job, but error-prone and doesn’t update when source changes.
- VBA macro – Can automate, but requires coding knowledge, and macros can’t be undone with Ctrl+Z. Always save a copy of your workbook before running a macro.
- Power Query – No code, fully refreshable, handles large data, and works with external files. The only downside is a slight learning curve.
For most users, Power Query is the best balance of power and ease. It’s also built into Excel, so there’s no extra cost.
Frequently Asked Questions
Can I merge sheets with different column orders?
Yes, but you should reorder columns in each query before appending so the data aligns correctly. Use the Choose Columns or Reorder Columns features in Power Query.
Will the merged table update automatically when I change the source sheets?
Not automatically unless you set the query to refresh on file open (Data > Queries & Connections > Properties > Refresh on File Open). Otherwise, you need to right-click the table and choose Refresh, or press Ctrl+Alt+F5.
Can I merge sheets from different workbooks without opening them?
Yes, use the From Folder method described above, or load each workbook as a separate query and then append them. Power Query reads the files directly from disk.
Why does my appended table have blank columns?
That happens when the source sheets have different column names. Power Query treats each unique name as a separate column. Standardize the headers across all sheets before appending.
Is there a limit to how many sheets I can merge?
No hard limit in Power Query, but performance may slow with hundreds of sheets or millions of rows. In practice, you can combine dozens of sheets without issue.
Does this work in Excel for the web?
Power Query is not available in Excel for the web. You must use the desktop version (Windows or Mac) to create and refresh queries. The web version can only view the results.
Related
- Excel Flexi Timesheet Template UK: How to Build One
- Excel First Letter Uppercase Rest Lowercase?
- Excel Eye Center Provo: Services, Doctors & What to Know
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Close Multiple Windows in Excel: Keep Workbooks OpenTo close multiple Excel windows without closing a workbook, close each window separately: save any changes, select a…
- Excel Min Sec Format: Display Minutes and SecondsTo display minutes and seconds in Excel, select the cells, press Ctrl+1, choose Number > Custom, and enter…
- How to Add Page Numbers in ExcelTo add page numbers in Excel for Microsoft 365 on Windows, open the worksheet in Page Layout view,…
- Archive Emails in Outlook to a PST FileTo archive Outlook emails in a PST file, use classic Outlook for Windows: go to File > Open…
- How to Remove a Section Break in Microsoft Word CorrectlyTo remove a section break in Microsoft Word, show formatting marks with Home > ¶, click immediately before…
- How to Create a 1-Inch Hanging Indent in Microsoft WordTo create a 1-inch hanging indent in Word, select the paragraph or paragraphs, open the Paragraph settings, choose…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.