How to Combine Rows from Multiple Sheets in Excel (4 Easy 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.


Download Practice Workbook

You can download the practice workbook from here.


4 Methods to Combine Rows from Multiple Sheets in Excel

Depending on the type of our work and 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. Combine Rows from Multiple Sheets in Excel Using Consolidate Option

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 amount for months January and February in two different sheets. We will combine the rows of these two sheets in a new sheet named Consolidate.

Combine Rows from Multiple Sheets in Excel Using Consolidate Option

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

STEPS:

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

Combine Rows from Multiple Sheets in Excel Using Consolidate Option

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

Combine Rows from Multiple Sheets in Excel Using Consolidate Option

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

Combine Rows from Multiple Sheets in Excel Using Consolidate Option

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

Combine Rows from Multiple Sheets in Excel Using Consolidate Option

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

Read More: How to Combine Rows in Excel (6 Methods)


2. Use 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’.

Use VBA to Combine Rows from Multiple Sheets in Excel

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

Use VBA to Combine Rows from Multiple Sheets in Excel

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

Use VBA to Combine Rows from Multiple Sheets in Excel

STEPS:

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

Use VBA to Combine Rows from Multiple Sheets in Excel

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

Use VBA to Combine Rows from Multiple Sheets in Excel

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

Read More: How to Consolidate Data in Excel from Multiple Worksheets (3 Ways)


Similar Readings


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

Excel ‘Power Query’ to Combine Rows from Multiple Sheets

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

Excel ‘Power Query’ to Combine Rows from Multiple Sheets

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

Excel ‘Power Query’ to Combine Rows from Multiple Sheets

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

Excel ‘Power Query’ to Combine Rows from Multiple Sheets

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.

Excel ‘Power Query’ to Combine Rows from Multiple Sheets

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

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


Load 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’.

Load Combined Data to Another Worksheet

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

Load Combined Data to Another Worksheet

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

Read More: How to Combine Data from Multiple Sheets in Excel (4 Ways)


Things to Remember While Using ‘Power Query’

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.

Things to Remember While Using ‘Power Query’

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

Things to Remember While Using ‘Power Query’

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.

Things to Remember While Using ‘Power Query’

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.

Things to Remember While Using ‘Power Query’

  • 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 value Group. So header row doesn’t come into consideration.


4. Apply IFERROR and VLOOKUP Functions to Combine Rows from Multiple Sheets in Excel

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 lookup data in a vertically structured table.

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

Apply IFERROR and VLOOKUP Functions to Combine Rows from Multiple Sheets in Excel

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

Apply IFERROR and VLOOKUP Functions to Combine Rows from Multiple Sheets in Excel

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

Apply IFERROR and VLOOKUP Functions to Combine Rows from Multiple Sheets in Excel

Let’s see the steps to perform this action.

STEPS:

  • In the beginning, select cell C5. Insert the formula in that cell:
=IFERROR(VLOOKUP(B5,'Name (2)'!B5:D6,2),"Not Found")

Apply IFERROR and VLOOKUP Functions to Combine Rows from Multiple Sheets in Excel

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

Apply IFERROR and VLOOKUP Functions to Combine Rows from Multiple Sheets in Excel

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

Apply IFERROR and VLOOKUP Functions to Combine Rows from Multiple Sheets in Excel

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

Apply IFERROR and VLOOKUP Functions to Combine Rows from Multiple Sheets in Excel

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

Apply IFERROR and VLOOKUP Functions to Combine Rows from Multiple Sheets in Excel

  • Now, select cell C7 and insert the following formula:
=IFERROR(VLOOKUP(B7,'Department (2)'!B5:D6,2,FALSE),"Not Found")

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

Read More: How to Combine Multiple Excel Sheets into One Using Macro (3 Methods)


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.


Related Articles

Mukesh Dipto

Mukesh Dipto

Hi there! I am Mukesh Dipto. I graduated with a bachelor's degree in engineering. Currently, I am working as a technical content writer in ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include sports and meeting new people. I also enjoy sports. My favorite sports are football (to watch and play) and Badminton (play).

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo