If you have ever ended a long week with seven workbooks open at once — regional sales, branch inventory, a contractor's invoice tracker — and someone asks for a single combined report by Monday, you already understand why how to merge Excel sheets is one of the most searched Excel questions on the internet. The phrase looks simple. The reality is that Excel has at least three different jobs hiding behind that one verb, and picking the wrong one wastes hours.
Merging in Excel can mean stitching multiple tabs inside the same workbook into one master tab. It can mean pulling 20 separate files from a folder into one big sheet. It can also mean joining two datasets together by a shared key — the sort of thing a SQL person would call a join, and an Excel person calls VLOOKUP, XLOOKUP, or a Power Query merge.
Each task uses a different tool. Trying to solve a folder-of-files problem with VLOOKUP, or a join problem with Move or Copy, is the spreadsheet equivalent of trying to open a tin can with a screwdriver.
This guide walks through all three. You will see when to right-click a tab and pick Move or Copy, when to fire up Power Query and append a folder of files, when to write a small VBA loop because the data lives in 200 workbooks nobody wants to open by hand, and when a single XLOOKUP solves the problem in a minute.
Every method here is built into Excel — no add-ins, no paid tools, nothing that goes away when IT updates Office. By the end you will know exactly which path to take the next time someone drops a stack of spreadsheets on your desk.
Before opening Excel, name the problem. Three jobs hide under the same phrase, and they need three different tools.
You have a workbook with twelve monthly tabs, each with the same column headers — Date, Region, Product, Units, Revenue. You want one tab with all the rows underneath each other so a pivot table can read the whole year at once. This is an append, sometimes called a union. The right tool is Power Query > Combine Queries > Append, or a quick VBA loop if you do it once and walk away.
You have a shared folder where each branch drops a weekly file named branch-04-2026-week-19.xlsx. Twenty branches times fifty-two weeks is over a thousand files a year, all with the same layout. You need them all in one workbook so head office can analyze them. The right tool is Power Query > Get Data > From File > From Folder. Excel reads every file in the folder, stacks them, and refreshes when new files arrive.
You have a customer list with name and customer ID, and a separate orders list with customer ID, date, and amount. You want one combined view — name, date, amount — joined on customer ID. This is a join, not an append. The right tool is XLOOKUP, VLOOKUP, INDEX-MATCH, or a Power Query Merge (not Append). The data does not stack — it lines up side by side.
Get the job right and the tool picks itself. Get it wrong and you waste an afternoon.
Same columns across tabs or files? You need an append — use Power Query Append or Get Data From Folder. Different columns that share a key like customer ID or product code? You need a join — use XLOOKUP, VLOOKUP, or Power Query Merge. When in doubt, look at the headers. If they match, stack them. If they do not, join them.
Move or Copy is the simplest way to merge Excel sheets when the data lives in two or three workbooks and you only need to do it once. Open every source workbook plus your destination workbook so they all appear in the same Excel window. Right-click any sheet tab in a source workbook and pick Move or Copy. In the dialog, change the To Book dropdown to your destination workbook, pick the position, tick Create a Copy if you want to keep the original in place, and click OK. The tab jumps across, formulas, formatting, and all.
Repeat for each tab you want to bring over. Five minutes and you are done. It is not elegant, but for a five-tab merge once a quarter it beats setting up a query.
The catch — Move or Copy gives you many tabs inside one workbook. It does not stack them into one tab. If your goal is one single tab with all the rows, you still need an extra step. Either copy each tab's rows underneath the previous one by hand (fine for three small tabs, painful for ten), or use Power Query Append on the moved tabs, which is the next method.
Power Query lives on the Data tab in every modern version of Excel. It is a free, built-in tool that reads data from almost anywhere, transforms it with clicks instead of formulas, and writes the result into a sheet you can refresh whenever the sources change. For merging sheets, Append is the move.
Start by turning each sheet you want to merge into an Excel Table — click anywhere in the range and press Ctrl+T. Give each table a clear name in the Table Design tab (Sales_Jan, Sales_Feb, and so on). Then on the Data tab, click Get Data > From Other Sources > Blank Query, or right-click any table and pick Get Data > From Table/Range to start a query for each one.
Once each table is loaded into the Power Query Editor (you will see it under Queries & Connections), click Home > Append Queries > Append Queries as New. Pick Three or More Tables, move all your tables to the right pane, and click OK. Power Query stacks them in column order — so column headers must match exactly. Hit Close & Load and the result lands on a new sheet. The big payoff comes the next month — add new rows to any source table, then Data > Refresh All, and the combined sheet updates instantly.
Use Move or Copy by right-clicking each sheet tab, then either copy rows underneath each other manually or run a quick Power Query Append on the result. Best when the merge is a one-off and the source workbooks already live on your desktop. Takes minutes, no setup, no refresh needed.
Use Power Query Append Queries as New. Turn each tab into an Excel Table, load each into Power Query, then append them all into one master query. The merged result refreshes with one click whenever any source tab changes. Perfect for monthly tabs in the same workbook.
Use Power Query Get Data From Folder. Point Power Query at the folder, let it discover every file inside, and combine them automatically. When a new file lands in the folder next week, refresh and it joins the merge. Ideal for branches, contractors, or daily exports.
This is a join, not a merge — use XLOOKUP, VLOOKUP, or Power Query Merge Queries. Match rows by customer ID, product code, or any unique field, and the result is one wide table with columns from both sources. Use when datasets describe the same things from different angles.
The single most powerful merge method in Excel handles the case most people give up on — twenty, fifty, or a thousand workbooks sitting in a folder. Doing that by hand is a full day's work. Power Query does it in under a minute and refreshes every week from then on.
Put every source file into one folder. They must share the same sheet layout — same column headers in the same order. Subfolders are fine, Power Query walks them recursively. On the Data tab, click Get Data > From File > From Folder. Browse to your folder and click OK. Power Query shows a preview of every file it found — file name, date modified, path, and the binary contents.
Click Combine & Transform Data. Excel asks which sheet inside each file to combine — pick the one you want (often Sheet1 or a named sheet) and click OK. Behind the scenes Power Query builds a helper function that opens every file, extracts the chosen sheet, and stacks the results. You land in the Power Query Editor with all the rows already merged, plus a Source.Name column showing which file each row came from. Clean up anything weird (filter out blank rows, fix column types) and click Close & Load.
From now on, every time a new file lands in the folder, click Data > Refresh All. The merge picks up the new data automatically. This single workflow has replaced entire reporting teams at companies that used to consolidate files by hand every Monday morning.
Power Query covers most cases, but sometimes you cannot use it — corporate-locked Excel, files with wildly different layouts, or a one-off job for a colleague who only has Excel 2010. VBA still handles all of these.
Press Alt+F11 to open the VBA editor. Insert > Module, then paste in a short loop. Something like this works for stacking a folder of files into one sheet — adjust the folder path, the sheet name, and the start row to match your data:
Sub MergeAllFiles()
Dim folder As String, file As String
Dim wb As Workbook, dest As Worksheet
folder = "C:\Reports\"
Set dest = ThisWorkbook.Sheets("Master")
file = Dir(folder & "*.xlsx")
Do While file <> ""
Set wb = Workbooks.Open(folder & file)
wb.Sheets(1).UsedRange.Offset(1).Copy dest.Cells(dest.Rows.Count, 1).End(xlUp).Offset(1)
wb.Close False
file = Dir
Loop
End Sub
Run it once with F5 and every .xlsx in the folder dumps its rows into your Master sheet. The Offset(1) skips the header on each source file so you do not end up with the header line repeated. Save the file as .xlsm so the macro sticks around for next time.
If the question is how to merge Excel documents into one file rather than into one sheet, the path is shorter. Open the destination workbook, then open each source workbook. Right-click any tab in a source workbook, pick Move or Copy, change To Book to your destination, tick Create a Copy, and click OK. The whole tab — every formula, every chart, every named range — lands in the destination workbook with a single click. Repeat for each tab in each source file. Five minutes for five files, less than half an hour for twenty.
What you get is one workbook with many tabs, not one big stacked sheet. That is usually what people mean by merge multiple Excel sheets into one file — they want one .xlsx they can email, not a single mega-table. If you also need a stacked version for analysis, run Power Query Append on the moved tabs afterwards. Two steps, one file, every formula preserved.
The opposite case — multiple tabs in one workbook, and you want a single tab holding every row — is the classic Power Query Append job. Convert each tab to a Table with Ctrl+T, load each Table into Power Query as a connection-only query, then Append Queries as New across all of them. The merged Table lands on a fresh sheet and refreshes whenever a source tab changes. This is the workflow used by every analyst who has ever inherited a workbook with twelve monthly tabs and a request for an annual chart.
Two terms float around in tutorials and they confuse beginners. Combine and Append in Power Query mean essentially the same job — stacking rows on top of each other where the columns line up. Combine is the wider menu label, used when Power Query helps you point at multiple sources (like a folder full of files). Append is the specific transform that stacks two or more existing queries into one.
If you load each file or sheet into its own query first, you Append. If you start from a folder and let Power Query discover the files, you Combine — and behind the scenes, Combine writes an Append for you. Same output, different starting point.
Job 3 from earlier — different columns, shared key — needs a join, not an append. If your customer table has Name and Customer_ID, and your orders table has Customer_ID, Date, and Amount, appending them produces a useless lump with half the cells empty. Joining them produces one row per order with the customer's name attached. The classic Excel join is XLOOKUP, which replaced VLOOKUP in 2020 and works in both directions, returns multiple columns at once, and handles errors gracefully. The formula =XLOOKUP(C2, Customers[Customer_ID], Customers[Name]) drops the customer's name next to every order. Drag down, done.
For bigger jobs, Power Query Merge does the same thing visually with no formulas. Load both tables as queries, then Home > Merge Queries, pick the key column in each, choose the join type (Left Outer, Right Outer, Full Outer, Inner), and expand the result. The output is a refreshable joined table sitting in your workbook.
The fastest way to make a merge fail is mismatched headers. Power Query stacks rows by column name, so two slightly different headers split your data across two columns. Standardize every source before you start — or fix the names in Power Query with a Rename Columns step right after the source is loaded.
The second-biggest mistake is mixing append and join. If you find yourself with rows half-full of nulls after a merge, you needed XLOOKUP or Power Query Merge, not Append. Reverse the call. Joining puts columns side by side, appending puts rows on top of each other. They produce wildly different outputs.
The third mistake is forgetting to refresh. Power Query merges do not update by themselves. After source data changes, you must hit Data > Refresh All. Set it in your monthly checklist or schedule a refresh on workbook open (File > Options > Advanced > General > Ask to update automatic links — turn this on, or use Connection Properties to auto-refresh).
Real-world files are rarely perfectly aligned. Branch 4 has an extra column for State. Branch 7 puts Date in the second column instead of the first. Branch 12 added a Notes column. Power Query handles all of it gracefully — Append Queries does not require identical column counts.
Columns that exist in one source but not another get filled with null in the merged result. You can drop or keep those columns afterwards. For columns that mean the same thing but are named differently, add a Rename step in each source query before the Append so the data lines up correctly.
For really messy layouts where the data starts at different row positions in each file, add Promote Headers and Remove Top Rows steps inside each source query to normalize the shape before the Append. Five extra clicks and the merge handles any layout you throw at it.
The point of learning these tools is not to do a merge once. It is to set up a workflow that runs forever with one click. The pattern looks like this — every source file or tab follows a fixed naming convention, lives in a fixed location, and has identical column headers. One workbook holds the Power Query that combines them. Whenever someone needs the merged report, they open that workbook, click Refresh All, and walk away. The output is a single sheet with every row from every source, plus a Source.Name column tracing each row to its origin.
Set this up once for a recurring report — weekly branch sales, monthly project hours, quarterly inventory — and you reclaim hours every cycle. The first setup takes thirty to sixty minutes. Every subsequent run takes ten seconds. That return-on-investment is why Power Query has quietly become the most valuable Excel skill of the last decade.
Power Query covers almost every merge case, but VBA still wins in a few specific situations. If your IT department locks down Power Query but leaves macros enabled, VBA is your only option. If the source files have wildly different layouts that change each week, a VBA loop with custom logic per file beats fighting Power Query's static schema. And if the merge needs to do something Power Query cannot — like apply a different transformation depending on the file name, or write the result back to multiple sheets — VBA gives you the procedural control to handle it.
For everything else, Power Query is faster to build, easier to maintain, and survives Excel version changes better than macros.
Merging Excel sheets is one of those skills where ten minutes of learning saves hundreds of hours over a career. The trick is naming the job first — append, combine from folder, or join — and reaching for the matching tool. Move or Copy handles small one-off merges. Power Query Append handles same-shape tabs. Power Query Get Data From Folder handles bulk files. XLOOKUP and Power Query Merge handle joins. VBA covers the last 5 percent of edge cases.
Pick one method this week and apply it to a real report you actually produce. Set up the query, save the workbook, and the next time the request comes in you will be done before you finish your coffee. Practice on tasks you genuinely need to solve — that builds the speed and the muscle memory that separate casual users from the colleagues everyone calls when a merge job lands at 4 pm on a Friday.
If you are preparing for a Microsoft Office certification or a job that lists Excel as a core requirement, work through practice questions that mirror real on-screen merge scenarios. Reading about Power Query is useful, but timing yourself on an actual Append or From Folder job is what builds the speed employers notice in the first week of a new role.