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

This is an overview.

Merge Sheets

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.

Datasets of Different Worksheets

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

Clicking on Consolidate Option from Data Tab

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.

 Using the Consolidate Dialogue Box

The Final Sales Report will be displayed.

 Merging two Datasets into One

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

Clicking on Plus Icon to See the Details


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.

Datasets of Different Worksheets

Convert these two datasets into tables:

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

 Creating Tables

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

Renaming the Table from Table Design

  • 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.

Pressing on Blank Query from Drop Down Menu

The Power Query Editor will open.

  • In the formula bar, enter the code below.
=Excel.CurrentWorkbook()
  • Click the Double Arrow as shown below.
  • In the dialog box, check Select All Columns.
  • Click OK.

Pasting the formula and Pressing OK

  • In the combined dataset, click Close & Load.

Combined Data Appears in Power Query Editor

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

A combined Table is Created


How to Merge Sheets in Excel Using a VBA Code?

Combining Data

  • Click Visual Basic in the Developer tab.

Enabling Visual Basic from Developer Tab

  • Choose Module in Insert.

Clicking on Module from Insert Tab

  • 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.

Choosing the Headers from the Worksheets

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

 A New Dataset Combining Two Worksheets


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

Get FREE Advanced Excel Exercises with Solutions!
Zahid Shuvo
Zahid Shuvo

Zahid Hasan Shuvo, a Naval Architecture and Marine Engineering graduate from BUET, Bangladesh, has contributed nearly a year to the Exceldemy Project as an Excel and VBA Content Developer. Within this timeframe, he has crafted over 8 tutorial articles, and besides offering valuable solutions to aid users effectively. Zahid also expresses keen interests in Excel & VBA, Data Analysis, Machine Learning, AI Engines, and Prompt Engineering, showcasing a diverse skill set and contributing to the dynamic environment of... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo