How to Merge Sheets in Excel? (Must-Know Tips and Techniques)

Merging sheets in Excel helps put data together, so it’s easier to look at and work with. It also makes working with others on the same data simpler by combining everything in one place.

We have used Microsoft 365 while writing this article. But all the methods are also applicable to the other versions of Excel as well.

This article covers two methods- Consolidate Feature and Power Query tool for merging data from different sheets in one file in Excel.

Furthermore,  you will learn all the processes along with steps with proper illustrations and instructions for merging sheets in one Excel file. Merging Multiple sheets helps to arrange a vast number of datasets.

Download Practice Workbook

How to Merge Excel Sheets in One File?

Here, we will discuss how you can merge two different sheets into one.  We have two methods to do this as follows.

1. Consolidate Data from Multiple Worksheets in a Single Worksheet

Let’s say, we have two different datasets containing a Sales Report from New York and a Sales Report from Florida in two different worksheets. We will merge these two datasets in a new worksheet to show the Final Sales Report. Have a look at the datasets below, that we will combine.

First, create a new worksheet where you want the Final Sales Report to be generated. Now, follow the steps ahead to combine two datasets in this worksheet.

• From the Data tab, go to the Consolidate icon shown in the picture.

The Consolidate dialogue box will appear. In the Box,

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

• Finally, the Final Sales Report will appear as follows.

• You can click on the Plus Icon as shown in the picture to see the details of any individual product sales.

2. Merge Data from Multiple Worksheets with Power Query

In this example, we will also combine multiple datasets from multiple worksheets but with the Power Query Tool. We will be combining the marks of Physics from two different sections of classes A and B. Below, there are two different datasets of Physics-A and Physics-B.

To combine those two datasets using the Power Query tool of Excel, we need to convert these two datasets into tables. So, here is how to convert the datasets into tables.

• First, select the data range from B4 to D14.
• Then, press Ctrl+T.
• A dialogue box named Create Table will appear. There, make sure to click OK.

• A table will appear. Now, click on the Table Design and rename the Table Name as Physics_A.

• Similarly, follow the steps above to make another table for Physics-B. And Rename it as Physics_B.

When the creation of two tables Physics_A and Physics_B are completed. We have to start working with Power Query. Follow the steps ahead.

• From the Data tab, click on the Get Data.
• Then, from the drop-down menu, click on From Other Sources.
• Now, click on Blank Query.

• Power Query Editor will appear as follows.
• There, in the formula bar, paste the code below.

• Then, click on the Double Arrow as shown in the picture below.
• A dialogue box will appear, there check Select All Columns.
• Finally, press OK.

• A combined dataset will appear in the Power Query Editor. Now, just simply click on Close & Load.

• Finally, a combined data table will appear in the new worksheet named Query1.

How to Merge Sheets in Excel Using VBA Code?

We will merge two sheets into one for the dataset mentioned previously with VBA macros. Have a look at the dataset that we are going to merge and prepare a new combined dataset in a different worksheet. Follow the steps below to learn the use of VBA macros to merge sheets.

• Click on Visual Basic from the Developer tab.

• Now, click on Module from the Insert tab.

• Paste 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.
• Then, a dialogue box named Input will appear. From there, select the Headers from the first table of the first worksheet.
• Then, click on OK.

• A new worksheet named Consolidated will appear here with merged sheets.

Things to Remember

• Check that the sheets have a similar structure.
• Power Query is available for the versions after 2010.
• Determine the range of data you want to merge.
• Note the names of the worksheets.
• Ensure compatible data types for merging 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 on “Consolidate,” select the range of each sheet, and specify the consolidation settings.
• Power Query: If you have Excel 2010 or later versions, you can use Power Query to merge multiple sheets. Go to the “Data” tab, click on “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 where you want to combine the data.
• Open the first Excel file you want to merge.
• Select all the data in the first file (Ctrl+A) and copy it (Ctrl+C).
• Switch to the new Excel file and go to the desired starting cell.
• Paste the copied data into the new file (Ctrl+V).
• Repeat steps 2-5 for each of the remaining 99 Excel files, pasting the data below the previously pasted data in the new file.
• After pasting all the data from the 100 files, save the new Excel file.

Conclusion

To summarize, we have discussed the process of merging sheets with the Consolidated Data feature, and Power Query. Using these methods, you can combine two or more sheets easily and effectively.  You can also use the VBA code as mentioned.

Yet, if you have any confusion, or find any trouble with any of the steps especially the provided VBA macros, please make sure to comment. Our team will respond as soon as possible.

Merge Sheets in Excel: Knowledge Hub

<< Go Back To Merge in Excel | Learn Excel