How to Merge Multiple Sheets in Excel to One Sheet?

Sharing is caring!

Managing data across several worksheets can quickly become difficult. If you are wondering how to merge multiple sheets in Excel to one sheet, there are several simple methods you can use.

Microsoft Excel provides built-in features like Copy and Paste, Move or Copy, Power Query, and VBA macros for different situations. In this guide, we will explain each method step by step so you can choose the one that works best for your needs.

Before You Merge Excel Sheets

Before combining worksheets, make sure:

Keeping your worksheets organized makes the merging process much smoother.

Method 1: Merge Multiple Sheets Using Copy and Paste

This is the easiest method when you only have a few worksheets.

Step 1: Create a New Worksheet

Insert a blank worksheet that will become your master sheet.

Step 2: Copy the First Worksheet

Select all the data from the first worksheet, including the headers.

Press:

Ctrl + A → Ctrl + C

Paste it into the new worksheet.

Step 3: Copy Remaining Sheets

Go to the second worksheet.

Copy only the data rows and skip the headers to avoid duplicates.

Paste the data below the last used row in the master sheet.

Repeat this process for every worksheet.

Best For

• Small workbooks
• 2 to 5 worksheets
• One-time projects

Pros

• Very easy
• No special Excel skills required

Cons

• Time-consuming for large datasets
• Easy to make mistakes

Method 2: Merge Multiple Sheets Using Power Query

Power Query is one of the best tools in Microsoft Excel for combining worksheets. It is available in Excel 2016, Excel 2019, Excel 2021, and Microsoft 365.

It is especially useful when you regularly update your workbook.

Steps

1. Open Excel.
2. Select the Data tab.
3. Click Get Data.
4. Choose From File > From Workbook.
5. Select your workbook.
6. In the Navigator window, select all worksheets you want to merge.
7. Click Transform Data.
8. Use the Append Queries option.
9. Load the combined data into a new worksheet.

Whenever the original worksheets change, simply click Refresh, and Excel updates the merged data automatically.

Best For

• Large datasets
• Monthly reports
• Frequently updated workbooks

Advantages

• Automatic refresh
• Very fast
• Handles thousands of rows efficiently
• Reduces manual work

Method 3: Merge Sheets Using Consolidate

Excel includes a Consolidate feature that combines numerical data from multiple worksheets.

Steps

1. Create a blank worksheet.
2. Open the Data tab.
3. Click Consolidate.
4. Select a function such as:
   • Sum
   • Average
   • Count
   • Max
   • Min
5. Add the data ranges from each worksheet.
6. Click OK.

Excel combines the selected values into one worksheet.

Best For

• Budget reports
• Financial statements
• Sales summaries
• Expense tracking

Limitation

The Consolidate feature summarizes data instead of stacking every row together.

Method 4: Merge Sheets Using VBA Macro

If you regularly combine many worksheets, a VBA macro can automate the process.

Basic Process

1. Press Alt + F11.
2. Open the Visual Basic Editor.
3. Insert a new module.
4. Add a VBA macro that copies data from each worksheet.
5. Run the macro.

The macro automatically copies data from every worksheet into one master sheet.

Best For

• Advanced Excel users
• Repetitive tasks
• Large workbooks

Benefits

• Extremely fast
• Fully automated
• Saves hours of manual work

Drawback

Requires some knowledge of Visual Basic for Applications (VBA).

Method 5: Use Move or Copy Sheet

Sometimes you simply want all worksheets in one workbook before merging them.

Steps

1. Right-click the worksheet tab.
2. Select Move or Copy.
3. Choose the destination workbook.
4. Repeat for each worksheet.

After moving all sheets into one workbook, you can combine their data using any of the earlier methods.

Comparison of Methods to Merge Multiple Sheets in Excel

Common Problems When Merging Excel Sheets

While merging worksheets, you may encounter a few common issues.

1) Duplicate Headers

If each worksheet contains column headings, remove the extra headers before pasting data into the master sheet.

2) Different Column Order

All worksheets should use the same column layout. Rearrange columns before merging if necessary.

3) Blank Rows

Delete empty rows to keep your merged worksheet clean.

4) Different Data Formats

Ensure columns use consistent formats such as:

• Dates
• Currency
• Percentages
• Text

This prevents sorting and calculation errors.

5) Duplicate Records

Use Excel’s Remove Duplicates feature after merging if the same records appear in multiple worksheets.

Tips for Merging Excel Sheets Efficiently

Follow these best practices to save time and avoid errors:

• Keep identical column names across all worksheets.
• Use Excel Tables for better data management.
• Prefer Power Query for recurring reports.
• Save your workbook before making major changes.
• Refresh Power Query whenever source data changes.
• Check totals after merging to verify accuracy.
• Remove unnecessary formatting before combining data.

Which Method Should You Choose?

The right method depends on your workbook size and workflow.

For most users, Power Query is the best choice because it combines worksheets quickly, minimizes manual effort, and allows you to refresh the merged data whenever the source worksheets are updated.

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.