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.

Excel combine sheets

Excel combine sheets

Excel combine sheets

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.

Excel combine sheets

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

consolidate option

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

consolidate option

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

consolidate option

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

consolidate option

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

consolidate option

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

Excel combine sheets


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.

Excel combine sheets

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

=VLOOKUP(B5,January!B4:D11,3,FALSE)

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.

VLOOKUP function

➤ Press ENTER
➤ Drag down the Fill Handle Tool

VLOOKUP function

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

VLOOKUP function

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

=VLOOKUP(B5,February!B4:D11,3,FALSE)

VLOOKUP function

=VLOOKUP(B5,March!B4:D11,3,FALSE)

VLOOKUP function


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.

Excel combine sheets

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

=INDEX(January!D5:D11,MATCH(B5,January!B5:B11,0))

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.

  • MATCH(B5,January!B5:B11,0) → returns the row index number
    Output → 1
  • INDEX(January!D5:D11,MATCH(B5,January!B5:B11,0)) becomes
    INDEX(January!D5:D11,1)
    Output → $4,629.00

INDEX-MATCH function

➤ Press ENTER
➤ Drag down the Fill Handle Tool

INDEX-MATCH function

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

INDEX-MATCH function

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

=INDEX(February!D5:D11,MATCH(B5,February!B5:B11,0))

INDEX-MATCH function

=INDEX(March!D5:D11,MATCH(B5,March!B5:B11,0))

Excel combine sheets


Method-4: Using INDIRECT-ADDRESS Formula

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

Excel combine sheets

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

=INDIRECT("January!"&ADDRESS(ROW(D5),COLUMN(D5)))
  • ROW(D5)→returns the row number of the cell D5
    Output→ 5
  • COLUMN(D5)→returns the column number of the cell D5
    Output→ 4
  • ADDRESS(ROW(D5),COLUMN(D5)) becomes
    ADDRESS(5,4)
    Output→$D$5
  • INDIRECT(“January!”&ADDRESS(ROW(D5),COLUMN(D5))) becomes
    INDIRECT(“January!”&”$D$5”)INDIRECT(“January!$D$5”)
    Output→$4,629.00

INDIRECT-ADDRESS function

➤ Press ENTER
➤ Drag down the Fill Handle Tool

INDIRECT-ADDRESS function

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

INDIRECT-ADDRESS function

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

=INDIRECT("February!"& ADDRESS(ROW(D5),COLUMN(D5)))

INDIRECT-ADDRESS function

=INDIRECT("March!"& ADDRESS(ROW(D5),COLUMN(D5)))

Excel combine sheets


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.

Excel combine sheets

Power Query

Excel combine sheets

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

Power Query

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

Power Query

➤ Write the following formula in the formula box

=Excel.CurrentWorkbook()

Power Query

➤ Press ENTER

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

Power Query

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

Power Query

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

Excel combine sheets

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

Power Query

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

Power Query

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

Power Query

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

Excel combine sheets

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.

Excel combine sheets

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

VBA code

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

VBA code

After that, a Module will be created.

VBA code

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.

VBA code

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

VBA code

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

VBA code

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

Excel combine sheets

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.

VBA code


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

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo