How to Merge Sheets in Excel – Must-Know Tips and Techniques

This is an overview.

Download Practice Workbook

How to Merge Excel Sheets in One File

1. Consolidate Data from Multiple Worksheets in a Single Worksheet

You have two different datasets containing a Sales Report from New York and a Sales Report from Florida in two different worksheets. You want to merge these two datasets in a new worksheet to show the Final Sales Report.

• Create a new worksheet for the Final Sales Report.
• Go to the Data tab and select Consolidate.

The Consolidate dialog box will open:

• Set the function to sum.
• In the Reference box, select and add the datasets from those two worksheets.
• Check Top row, Left column.
• Check Create links to source data to make the list dynamic.
• Click OK.

The Final Sales Report will be displayed.

• Click the Plus Icon as shown below to see the details of individual product sales.

2. Merge Data from Multiple Worksheets with the Power Query

Combine multiple datasets from multiple worksheets with the Power Query. Combine the marks of Physics of classes A and B from two different sections. Below, there are two different datasets: Physics-A and Physics-B.

Convert these two datasets into tables:

• Select B4:D14.
• Press Ctrl+T.
• A dialog box: Create Table will open.
• Click OK.

• A table will open. Click the Table Design and rename the Table Name as Physics_A.

• Follow the steps above to create another table for Physics-B and rename it as Physics_B.
• In the Data tab, click Get Data.
• Choose From Other Sources.
• Click Blank Query.

The Power Query Editor will open.

• In the formula bar, enter the code below.

• Click the Double Arrow as shown below.
• In the dialog box, check Select All Columns.
• Click OK.

• In the combined dataset, click Close & Load.

The combined data table will be displayed in the new worksheet: Query1.

How to Merge Sheets in Excel Using a VBA Code?

• Click Visual Basic in the Developer tab.

• Choose Module in Insert.

• Enter the code below in the module.

Sub combine_multiple_sheets()
Dim Row_1, Col_1, Row_last, Column_last As Long
Dim headers As Range
Set WB = ThisWorkbook
Set wX = WB.Sheets.Add
wX.Name = "Consolidated"
Set headers = Application.InputBox("Choose the Headers", Type:=8)
headers.Copy wX.Range("A1")
Row_1 = headers.Row + 1
Col_1 = headers.Column
For Each Ws In WB.Worksheets
    If Ws.Name <> "Consolidated" Then
        Ws.Activate
        Row_last = Cells(Rows.Count, Col_1).End(xlUp).Row
        Column_last = Cells(Row_1, Columns.Count).End(xlToLeft).Column
        Range(Cells(Row_1, Col_1), Cells(Row_last, Column_last)).Copy wX.Range("A" & wX.Cells(Rows.Count, 1).End(xlUp).Row + 1)
    End If
Next Ws
Worksheets("Consolidated").Activate
End Sub

• Press F5 to run the code.
• In the Input dialog box, select the Headers of the first table in the first worksheet.
• Click OK.

A new worksheet: Consolidated will be displayed with the merged sheets.

Things to Remember

• Check that the sheets have a similar structure.
• Determine the range you want to merge.
• Ensure compatible data types to merge columns.
• Avoid duplicate headers across sheets.

Frequently Asked Questions (FAQ)

Q1. Can I merge Excel sheets into one?

Yes, you can merge Excel sheets into one.

• Consolidate Data: Use the “Consolidate” feature to combine data from multiple sheets into one. Go to the “Data” tab, click “Consolidate,” select the range in each sheet, and specify the consolidation settings.
• Power Query: Use Power Query to merge multiple sheets. Go to the “Data” tab, click “Get Data,” choose “Combine Queries,” and select the sheets you want to merge.

Q2. How to combine 100 Excel files into one?

To combine 100 Excel files into one, you can follow these steps:

• Create a new Excel file.
• Open the first Excel file you want to merge.
• Select all the data in the first file (Ctrl+A) and copy it (Ctrl+C).
• Paste the copied data into the new file (Ctrl+V).
• Repeat these steps for each file.
• Save the new Excel file.

Merge Sheets in Excel: Knowledge Hub

<< Go Back To Merge in Excel | Learn Excel