How to Combine Rows from Multiple Sheets in Excel (4 Methods)

In this tutorial, we will demonstrate different methods of how to combine rows from multiple sheets in Excel. While working with multiple sheets in Excel, we might have the need to combine rows from multiple sheets on another sheet. Excel doesn’t provide us with any feature to perform this task with a single click. To do this we have to follow some specific steps. Throughout this article, we will use unique datasets to make you understand better the methods to combine rows from multiple sheets in Excel.

Depending on the type of work and the convenience to perform, we can use 4 different methods to combine rows from multiple sheets in Excel. The main motive of the 4 methods is the same. We will select a specific method to perform our work based on the data type in our dataset.

1. Use Consolidate Option to Combine Rows from Multiple Excel Sheets

First and foremost, we will use the Excel Consolidate feature to combine rows from multiple sheets in Excel. The use of the Excel Consolidate feature is the quickest way to combine rows. But we can only combine numeric values with this feature. If we want to combine text this method will not help. In the following screenshot, we can see a dataset of salespeople and their sales amounts for the months of January and February in two different sheets. We will combine the rows of these two sheets in a new sheet named Consolidate.

So, let’s see the steps to how we can perform this action.

STEPS:

• Firstly, go to the sheet Consolidate. Select cell B4.

• Secondly, go to the Data tab and select the option Consolidate from the section ‘Data Tools’.

• So, a new dialogue box named Consolidate will appear.
• Thirdly, in the dialogue box, we will select the SUM function. You can use any one of the available functions to consolidate your data.
• Also, click on the ‘Collapse Dialogue Icon’ in the reference box.

• An input box like this will appear.

• Next, click on the sheet January. Select the range (B4:C8) and click on the ‘Collapse Dialogue Icon’ again.

• The above action will input the selected range in the reference box.
• Then click on Add to input the range in the ‘All references’ box.

• Similarly, add the range of sheet February.
• After that, check the box of ‘Top row’ and ‘Left column‘. Now, click on OK.

• Lastly, we can see that the above command combines rows from sheets January and February in the third sheet named Consolidate.

2. Using VBA to Combine Rows from Multiple Sheets in Excel

If you want to be an advanced Excel user and want to combine rows from multiple sheets in Excel more dynamically, you can use VBA (Visual Basics for Applications) code. We will combine rows from two datasets with the use of VBA code.

In the first screenshot, we can see a sheet named Department that contains ‘Employee ID’ and their ‘Full Name’.

In the second screenshot, we can see a sheet named Department that consists of ‘Employee ID’ and their working Department.

Now we will combine the rows of these two datasets with VBA code. Let’s see the steps to perform this action.

STEPS:

• First, go to the Developer tab and select the option ‘Visual Basic’.

• Next, a new window will open. Go to the Insert tab and select the option Module.

• The above action will insert a module named ‘Module 1’. Click on ‘Module 1’ and we will get a blank VBA module.

• Then, insert the following code in the blank module:

Sub combinesheets()
Dim firstRow, firstCol, LR, LC As Long
Dim headers As Range
Set x = Worksheets("Combined")
Set wb = ThisWorkbook
Set headers = Application.InputBox("Select the Headers", Type:=8)
headers.Copy x.Range("A1")
firstRow = headers.Row + 1
firstCol = headers.Column
Debug.Print firstRow, firstCol
For Each ws In wb.Worksheets
If ws.Name <> "Combined" Then
ws.Activate
LR = Cells(Rows.Count, firstCol).End(xlUp).Row
LC = Cells(firstRow, Columns.Count).End(xlToLeft).Column
Range(Cells(firstRow, firstCol), Cells(LR, LC)).Copy _
x.Range("A" & x.Cells(Rows.Count, 1).End(xlUp).Row + 1)
End If
Next ws
Worksheets("Combined").Activate
End Sub

• Click on the Run or press the F5 key to run the code.

• Finally, the above actions combine the rows of the two sheets that we selected in a new sheet named Combined.

3. Using Excel Power Query to Combine Rows from Multiple Sheets

Excel’s ‘Power Query’ is a powerful tool for combining and analyzing data. While we have a problem combining data from two different sheets the first thing that comes to our mind is using ‘Power Query’. We can easily combine rows from multiple sheets in Excel with the use of ‘Power Query’.

When using ‘Power Query’ to combine data from different sheets, the data must be in an ‘Excel Table’ format or at least in named ranges. The procedure given here would not work if the data was not in an ‘Excel Table’.

In the first image, we can see a screenshot of the dataset of sheet ‘Name-1’ which is in table format.

Here, select any cell from the table. Go to the ‘Table Design’ tab and rename the table to ‘Group-1’.

In the second image, we can see a screenshot of the dataset of sheet ‘Department-1’ which is in table format.

Again, select any cell from the table. Go to the ‘Table Design’ tab and rename the table to ‘Group-2’.

So, let’s see the steps to use ‘Power Query’ to combine rows.

STEPS:

• Firstly, go to Data > Get Data > From Other Sources > Blank Query.

• A new window named ‘Power Query Editor’ will appear.
• Secondly, insert the following formula in the formula bar:

• Press Enter. This will display the names of all the tables in the entire workbook.
• Thirdly, click on the double-pointed arrow from the content header cell.

• Next, from all the available options check the columns that you want to combine.
• Do not forget to uncheck the option ‘Use original column name as prefix’.
• Now, click on OK.

• The above command combines the rows of the sheets that we selected.

Loading Combined Data to Another Worksheet

Now will load the combined data of the above step into another worksheet. To do this follow the below process:

• First, go to the File tab.
• Next, select the option ‘Close & Load To’.

• Then, a new dialogue box will appear. Check the option ‘New worksheet’ in that box.
• Now, click on OK.

• Finally, the above action loads the combined rows in a new sheet named ‘Query 1’.

Notes: If we follow the above method to combine rows from multiple sheets in excel we might face a problem. Look at the following image. Our query table named ‘Query1’ consists of 9 rows including the headers.

Then, right-click on the table name and refresh it.

So, we can see that the loaded row numbers have changed to 16 from 9. It happens because the data from our new table ‘Query1’ will be added every time we will refresh.

Now, if we don’t want to change the row number with refresh we will follow the below steps.

STEPS:

• First, click on the drop-down of the header cell ‘Name’.
• Next, hover the cursor on the option ‘Text Filters’
• Then, select the option Contains.

• A new dialogue box will appear. In the field text of the Contains option insert the value, Group.
• Click on OK.

• After that, refresh the table again.

• Lastly, we can see that no new rows load with a refresh. The loaded row number is showing 8 because this time we are only calculating rows that contain the value Group. So header row doesn’t come into consideration.

4. Joining IFERROR and VLOOKUP Functions to Combine Rows

We can easily combine rows from multiple sheets in Excel by using the combination of IFERROR and VLOOKUP functions.

The IFERROR function delivers a value you provide; If a formula calculates to an error, otherwise, it provides the formula’s result.

The VLOOKUP function in Excel allows you to look up data in a vertically structured table.

The first screenshot gives us an overview of the dataset of the sheet named ‘Name (2)’.

In the second screenshot, we have the dataset of the sheet named ‘Department (2)’.

In this method, we want to combine the rows of sheets ‘Name (2)’ and ‘Department (2)’ in a new sheet named VLOOKUP.

Let’s see the steps to perform this action.

STEPS:

• In the beginning, select cell C5. Insert the formula in that cell:

• Press Enter. So, we can see the value of cell C5 of the sheet ‘Name (2)’ in our selected cell.

• Next, select cell C5. Move the mouse cursor to the bottom right corner of the selected cell so that it turns into a plus (+) sign like the following image.
• Then, click on the plus (+) sign and drag the Fill Handle. horizontally to cell D10 to copy the formula of cell C5 in other cells. We can also double-click on the plus (+) sign to get the same result.

• After that, if we drag the Fill Handle tool from D5 to D10 we can see all the row values of sheet ‘Name (2)’ in our new sheet.

• Then, if we drag the Fill Handle tool one step down more it will give us the value ‘Not Found’ because there is no employee with the corresponding ‘Employee ID’ in the ‘Name (2)’ sheet.

• Now, select cell C7 and insert the following formula:

• Press Enter. We can see the ‘Full Name’ in cell C7.

• Next, drag the Fill Handle tool horizontally from cell C5 to D5.

• After that, drag the Fill Handle tool again from cell D7 to D8. This action combines the rows of sheet ‘Department (2)’ with the rows of the sheet ‘Name (2)’ in the new sheet named VLOOKUP.

• Finally, if we drag the Fill Handle tool one step down more we will get the value ‘Not Found’. It happens because the value with the corresponding ‘Employee ID’ is not present in the sheet named ‘Department (2)’.

🔎 How Does the Formula Work?

• VLOOKUP(B5,’Name (2)’!B5:D6,2): This part finds the value of cell B5 in sheet ‘Name (2)’.
• IFERROR(VLOOKUP(B5,’Name (2)’!B5:D6,2),”Not Found”): This returns the lookup value. If the value is not found, return ‘Not Found’.

Download Practice Workbook

You can download the practice workbook from here.

Conclusion

In conclusion, this post will show you how to combine rows from multiple sheets in Excel. To put your skills to the test, use the practice worksheet that comes with this article. Please leave a comment below if you have any questions. Our team will try to reply to you as soon as possible. In the future, keep an eye out for more unique Microsoft Excel solutions.

<< Go Back To Merge Sheets in Excel | Merge in Excel | Learn Excel