How to Merge All Worksheets in Excel Into One

Combining data from multiple worksheets into a single sheet is one of the most common Excel tasks — and one of the most misunderstood. Whether you're consolidating monthly reports, combining team submissions, or pulling together data from a dozen tabs, Excel gives you several ways to get there. The right approach depends on your data structure, your Excel version, and how much you want to automate the process.

Why Merging Worksheets Isn't One-Size-Fits-All

Before diving into methods, it helps to understand what "merging" actually means in Excel. There are two fundamentally different goals:

Stacking data — appending rows from multiple sheets into one continuous table (same columns, more rows)
Combining side-by-side — placing data from different sheets next to each other (adding columns)

Most users want the first option. But even within that, there's a difference between a one-time manual merge and a dynamic merge that updates when source sheets change. Knowing which you need shapes everything else.

Method 1: Copy and Paste (Manual, No Formulas)

The simplest approach — and the right one for small, one-off tasks.

Open the workbook with all your sheets
Go to the first source sheet, select all data (Ctrl+A or Cmd+A on Mac)
Copy it (Ctrl+C)
Navigate to your destination sheet and paste (Ctrl+V)
Repeat for each sheet, pasting below the previous data

What works well: Fast, no technical knowledge required, works in any Excel version.

What to watch for: Headers will repeat with each paste if you're not careful. Skip the header row on every sheet after the first. Also, this creates a static snapshot — changes to source sheets won't update the merged sheet.

Method 2: Power Query (Dynamic, Built-In, Recommended for Recurring Tasks) 🔄

Power Query is Excel's built-in data transformation tool, available in Excel 2016 and later (including Microsoft 365). It can automatically pull data from all sheets in a workbook and stack it into one table — and refresh when the source data changes.

Basic Power Query approach:

Go to Data → Get Data → From File → From Workbook
Select your file, then in the Navigator, check Select Multiple Items and pick all relevant sheets
Click Transform Data to open the Power Query editor
From here, you can append all queries: Home → Append Queries → Append Queries as New
Select all the sheet queries and append them
Click Close & Load to push the merged data into a new sheet

For workbooks where the sheets follow a consistent structure, Power Query also supports loading and combining all sheets in one step using Get Data → From File → From Workbook, selecting the whole file and using the built-in combine function.

What works well: Dynamic — refresh the query and it re-merges automatically. Handles large datasets well. Doesn't require formulas or macros.

What to watch for: All sheets must have the same column structure for a clean stack. If headers differ between sheets, Power Query will create mismatched columns. You'll also need to remove any auto-added "Source" columns if you don't want them in your final output.

Method 3: VBA Macro (Flexible, Automated, Requires Basic Scripting)

For users comfortable with Excel's built-in macro editor, a VBA script can loop through every sheet in a workbook and copy its data to a master sheet. This approach is highly customizable.