How to Combine Sheets in Excel (6 Easiest Ways)

If you are looking for some of the easiest ways to combine sheets in Excel, then you are in the right place. By following the procedures of this article, you will be able to combine data of multiple sheets into a single sheet easily.

Download Workbook

6 Ways to Combine Sheets in Excel

Here, we have three sheets named January, February, and March containing sales records of these months. In these data tables, the first two columns are the same for all of the sheets but the only difference is in the value of sales.

For creating the article, we have used Microsoft Excel 365 version, you can use any other versions according to your convenience.

Method-1: Using Consolidate Option in Excel to Combine Sheets

Here, we will use the Consolidate option, to sum up, the sales value of the three months of the three sheets January, February, and March for each product.

Steps:
➤ Select the cell D5 (where you want to have the consolidated data)
➤ Go to Data Tab >> Data Tools Group >> Consolidate Option

Then, Consolidate wizard will open up
➤ Select Sum Option (or any other option as per your needs) in the Function box

After that, we have selected the data of the Sales column of the January sheet in the Reference box
➤ Click Add

In this way, the reference will be added up in the All references box

Similarly, repeat this referencing task for the other two sheets February and March
➤ Press OK

Finally, you will get the sales values summed up for each product.

Method-2: Using Excel VLOOKUP Function to Combine Sheets

Here, to combine the data of the three sheets we have created a new sheet named VLOOKUP where we have pasted the common data of the Product and Sales Person column and now we will combine the sales data from the three sheets by using the VLOOKUP function.

Steps:
➤ Select the output cell D5
➤ Type the following formula

Here, B5 is the lookup value Apple, January!B4:D11 is the table array where January! Is the sheet name, 3 is the column number of which we want the values and FALSE is for an exact match.

➤ Press ENTER
➤ Drag down the Fill Handle Tool

Then, you will get the sales record of January month from the January sheet in the January column.

Similarly, you can get the sales record for  February and March by using the following formulas

Method-3: Using INDEX-MATCH Formula to Combine Sheets

In this section, we will use the INDEX function and the MATCH function to combine the sales data of the three sheets in a single sheet.

Steps:
➤ Select the output cell D5
➤ Type the following formula

Here, B5 is the lookup value Apple, January!B5:B11 is the lookup array where January! Is the sheet name, 0 is for an exact match, and January!D5:D11 is the reference for the return range.

➤ Press ENTER
➤ Drag down the Fill Handle Tool

In this way, you will get the sales record of the January month from the January sheet in the January column.

Similarly, you can get the sales record for  February and March by using the following formulas

Method-4: Using INDIRECT-ADDRESS Formula

You can use the INDIRECT function and the ADDRESS function to combine multiple sheets into one sheet.

Steps:
➤ Select the output cell D5
➤ Type the following formula

➤ Press ENTER
➤ Drag down the Fill Handle Tool

Then, you will get the sales record of January month from the January sheet in the January column.

Similarly, you can get the sales record for  February and March by using the following formulas

Method-5: Using Excel Power Query to Combine Sheets

In this section, we will use Power Query to combine multiple sheets into one sheet. But prior to that, we need to convert the data ranges of different sheets into a Table or named range as the following figures.

For this purpose, we have created three tables of the three sheets named January_month, February_month, and March_month.

Steps:
➤ Create a new sheet (here, it is Power Query)
➤ Go to Data Tab >> Get Data Dropdown >> From Other Sources Dropdown >> Blank Query Option

After that, a blank Power Query Editor will open up.

➤ Write the following formula in the formula box

➤ Press ENTER

Then, the name of the tables will appear
➤ Select the double arrow sign in the Content column

After that, a dialog box will pop up.
➤ Select the columns you want to have in your sheet and unclick the option Use original column name as prefix
➤ Press OK

Finally, you will get the combined data of the three sheets in the following table.

If you don’t want to change the combined data of this table after refreshing then you have to follow some more steps.

➤ Select the filter sign of the Name column
➤ Follow Text Filters Group >> Ends With Option

After that, the Filter Rows wizard will open.
➤ Type _month (the common portion of each table’s name) in the box besides ends with Option
➤ Press OK

Now, the task is completed and you can close this editor.
➤ Go to Home Tab >> Close & Load Dropdown >> Close & Load Option

In this way, you will be able to combine the data of multiple sheets into one sheet-like below.

Related Content: How to Merge Columns in Excel (4 Ways)

Method-6: Using VBA Code

You can use a VBA code for combining multiple sheets into one. For this purpose, we have created a sheet named Combined where we will have the combined data.

Step-01:
➤ Go to Developer Tab >> Visual Basic Option

Then, the Visual Basic Editor will open up.
➤ Go to Insert Tab >> Module Option

After that, a Module will be created.

Step-02:
➤Write the following code

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

Here, we have declared firstRow, firstCol, LR, LC as Long and set the sheet name Combined as x.

firstRow and firstCol are set as the first row and column of the data tables and then we have run the FOR loop for the other sheets except for the Combined sheet as we will combine our data in this sheet.

LR and LC will determine the last row and column of each sheet and then a range is formed using the firstRow, firstCol, LR, LC; in this way, this loop copies the data of each sheet for this range and pastes it into the Combined sheet.

Finally, we will activate this sheet to see the result.

➤ Press F5
After that, the Input wizard will open up where you have to select the headers from any of the sheets.

So, we have selected the headers from the March (3) sheet.
➤ Press OK

Afterward, you will get the combined data in the Combined Sheet

Related Content: How to Merge Multiple Sheets into One Sheet with VBA in Excel (2 Ways)

Practice Section

Here, we have provided a blank sheet named Practice where you can try the ways to combine the sheets here. Do it Yourself.

Conclusion

In this article, we have tried to cover the easiest ways to combine sheets in Excel effectively. Hope you will find it useful. If you have any suggestions or questions, feel free to share them with us.

Related Articles

• How to Combine Graphs in Excel (Step-by-Step Guideline)
• Combine Rows in Excel (6 Methods)
• How to Combine Columns into One List in Excel (4 Easy Ways)