Note: The other languages of the website are Google-translated. Back to English
Log in  \/ 
x
or
x
Register  \/ 
x

or

Excel Tutorial: Combine Sheets

In Excel, combining sheets is a common but somewhat difficult task for most of Excel users, especially green hands. Here this tutorial, listing almost all combining scenarios you may face and providing relative professional solutions for you. You can quickly find the answer by clicking the link in below navigation list, or you can learn the combining skills by following below links one by one if you are interested in.

In Excel, combining can be categorized into two types, one is combining contents only, the other is combining contents and doing some calculations.

The Navigations of This Tutorial

Combine Contents

1. Combine all sheets into one sheet

1.1 Combine sheets into one sheet with VBA

1.2 Combine tables or named rannges with Query (Excel 2016 or later versions)

1.3 Combine sheets into one sheet with a handy tool

1.4 Extension about combining sheets into one

2. Combine workbooks into one workbook

2.1 Combine workbooks into one workbook by the Move or Copy command

2.2 Combine multiple workbooks (in one folder) into one workbook with VBA code

2.3 Combine multiple workbooks (across multiple folders) into one workbook by using a handy tool

3. Combine specific sheets from multiple workbooks into one workbook

3.1 Combine specific sheets from workbooks (all workbooks in the same folder)

3.2 Combine specific sheets from multiple workbooks (across multiple folders) with a handy tool

4. Combine two sheets based on key column

4.1 Combine two tables based on one key column by using Query (Excel 2016 or later versions)

4.2 Combine two sheets based on one key column by using Excel functions

4.3 Combine two sheets based on one key column by a handy tool

5. Combine two sheets based on two columns

6. Combine sheets with same headers

6.1 Combine all sheets with same headers by using VBA

6.2 Combine sheets with same headers by using a handy tool

Consolidate

1. Combine sheets and do some calculations

1.1 Combine sheets and do calculates with Consolidate feature

1.2 Combine sheets and do calculations with a handy tool

2. Combine multiple sheets into PivotTable

Google Sheet

1. Combine google sheets into one sheet

1.1 Combine google sheets into one sheet by the Filter function

1.2 Combine google sheets into one sheet by the IMPORTRANGE function

2. Combine google sheets into one workbook

Extension

1. Combine sheets then remove duplicates

1.1 Combine sheets then remove duplicates by Remove Duplicates

1.2 Combine sheets then remove duplicates by a powerful tool

2. Combine sheets of same name into one workbook

3. Combine same ranges across sheets into one sheet

Note

In this tutorial, I create some sheets and data for better explain the methods, you can change the references for you need when you use below VBA code or formulas, or you can download the samples for trying methods directly.


Combine Contents

1. Combine all sheets into one sheet

Here is a workbook with 4 sheets needed to be combined together to one sheet.

doc combine sheets in to one sheet 1  doc combine sheets in to one sheet 1
doc combine sheets in to one sheet 1 doc combine sheets in to one sheet 1

doc sampleClick to download the sample file

1.1 Combine all sheets into one sheet with VBA code


In Excel, except the traditional method – Copy and Paste, you can use a VBA code to quickly merge all sheets into one sheet.

1. Press F11 and Alt keys to enable the Microsoft Visual Basic for Applications window.
doc combine sheets in to one sheet 1

2. Then in the popping window, click Insert > Module to insert a new blank module.
doc combine sheets in to one sheet 1

3. Copy and paste below code to the new module.

VBA: Combine all sheets into one

Sub CombineAllSheetsIntoOneSheet()
'UpdatebyExtendoffice
    Dim I As Long
    Dim xRg As Range
    On Error Resume Next
    Worksheets.Add Sheets(1)
    ActiveSheet.Name = "Combined"
   For I = 2 To Sheets.Count
        Set xRg = Sheets(1).UsedRange
        If I > 2 Then
            Set xRg = Sheets(1).Cells(xRg.Rows.Count + 1, 1)
        End If
        Sheets(I).Activate
        ActiveSheet.UsedRange.Copy xRg
    Next
End Sub

doc combine sheets in to one sheet 1

In the above code, the script “Combined” is the name of the sheet that place the combined contents, you can change this script to other as you need.

4. Press the F5 key to run the code, a sheet named Combined has been created in the front of all sheets to place all sheets contents.
doc combine sheets in to one sheet 1

1.2 Combine all tables or named ranges with Query (Excel 2016 or later versions)


If you work in Excel 2016 or later versions, the Query feature allows you to combine all created tables or named ranges into one at once time.

doc sampleClick to download the sample file

Firstly, make sure the ranges have been created as tables or named ranges, for creating table and named range, please refer to How to convert range to table or vice versa in Excel and Define and use names in formulas.

1. Open the workbook that you want to merge all sheets, click Data > New Query > From Other Sources > Blank Query.
doc combine sheets in to one sheet 1

2. In the popped-up Query Editor window, go to the formula bar, type below formula into it.

=Excel.CurrentWorkbook()

Press Enter key, all tables in the current workbook have been list.
doc combine sheets in to one sheet 1

3. Click the expand button beside Content and check Expand option and Select All Columns checkbox.
doc combine sheets in to one sheet 1

4. Click OK. All the tables are listed one by one.
doc combine sheets in to one sheet 1

You can right click at a column header to do other operations at the selected column.
doc combine sheets in to one sheet 1

5. Then click File > Close & Load To….
doc combine sheets in to one sheet 1

6. In the Load To dialog, check Table option, then choose the location you need to load the combined table in the Select where the data should be loaded section, click Load.
doc combine sheets in to one sheet 1

Now a new sheet is created to place all merged tables.
doc combine sheets in to one sheet 1

1.3 Combine all sheets into one sheet with a handy tool


If above methods cannot satisfy you, you can try a helpful and handy tool, Kutools for Excel. Its Combine feature is powerful which not only can combine all sheets into one sheet, also can complete advanced combined jobs, such as combine sheets into one workbook, combine sheets with same name, combine selected sheets only, combine sheets across files and so on.

1. Enable the workbook you want to combine its sheets, click Kutools Plus > Combine. A dialog pops to remind you of some notices about password, if the workbooks you want to use are not touch upon password, just click OK to continue.
doc combine sheets in to one sheet 1
doc combine sheets in to one sheet 1

2. In the Combine Worksheets – Step 1 of 3 window, check Combine multiple worksheets from workbooks into one worksheet option, click Next button.
doc combine sheets in to one sheet 1

3. In the Step 2 of 3 window, in default, the current workbook has been listed and checked in the Workbook list pane, and all sheets of the current workbook have been listed and checked in the Worksheet list pane, click Next to continue.

Note: if you have opened multiple workbooks, all opened workbooks are listed in the Workbook list, please check only the workbook you want to use.
doc combine sheets in to one sheet 1

4. In the last step, choose the combine mode as you need, you can choose Combine by row or Combine by column; then specify other options as you need. Click Finish button.
doc combine sheets in to one sheet 1

5. A dialog pops out for you to choose one folder to place the combined workbook, you can rename it in the File name bar, click Save to finish.
doc combine sheets in to one sheet 1

6. Now all sheets have been combined together, and a dialog pops out to ask you if save the settings as a scenario, click Yes or No as you like.
doc combine sheets in to one sheet 1

A new workbook pops out with the combining result listed, click the link of Output file to check the combined sheet.
doc combine sheets in to one sheet 1

Combine by row
doc combine sheets in to one sheet 1

Combine by column
doc combine sheets in to one sheet 1

1.4 Extension about combining sheets into one sheet


1.41 With a handy tool to only combine the selected sheets into one sheet

If you only want to combine some of sheets in a workbook into one sheet, there is no built-in feature in Excel can achieve it. But the Combine feature of Kutools for Excel can do it.

1. Enable the workbook you use and click Kutools Plus > Combine > OK to enable the Combine wizard.
doc combine sheets in to one sheet 1
doc combine sheets in to one sheet 1

2. In the Step 1 of 3 window, check Combine multiple worksheets from workbooks into one worksheet option, click Next button.
doc combine sheets in to one sheet 1

3. In the Step 2 of 3 window, in default, the current workbook has been listed and checked in the Workbook list pane, and all sheets of the current workbook have been listed and checked in the Worksheet list pane, uncheck the sheets that do not need to be combined, click Next to continue.
doc combine sheets in to one sheet 1

4. In the last step, specify options as you need. Click Finish button.
doc combine sheets in to one sheet 1

5. A dialog pops out for you to choose one folder to place the combined workbook, you can rename it in the File name bar, click Save to finish.
doc combine sheets in to one sheet 1

6. Now only the checked sheets have been combined together, and a dialog pops out to ask you if save the settings as a scenario, click Yes or No as you like.
doc combine sheets in to one sheet 1
doc combine sheets in to one sheet 1

1.42 With a handy tool to combine sheets from multiple workbooks into one sheet

For example, you want to combine all sheets of book1, book2 and book3 into one sheet as below screenshot shown, the Combine feature of Kutools for Excel also can help you.
doc combine sheets in to one sheet 1

After free installing Kutools for Excel, please do as below:

1. Enable the workbooks you use and click Kutools Plus > Combine > OK to enable the Combine wizard.

2. In the Step 1 of 3 window, check Combine multiple worksheets from workbooks into one worksheet option, click Next button.
doc combine sheets in to one sheet 1

3. In the Step 2 of 3 window, all opened workbooks have been listed and checked in the Workbook list pane, and all sheets of opened workbooks have been listed and checked in the Worksheet list pane, click Next to continue.
doc combine sheets in to one sheet 1

Tip: if you want to add workbooks to combine, click Add button to choose a path to add workbooks.
doc combine sheets in to one sheet 1

4. In the last step, choose the options as you need. Click Finish button.

5. A dialog pops out for you to choose one folder to place the combined workbook, you can rename it in the File name bar, click Save to finish.
doc combine sheets in to one sheet 1

6. Now all sheets in the opened workbooks have been combined together, and a dialog pops out to ask you if save the settings as a scenario, click Yes or No as you like.

Open the combined workbook you have saved, the sheets across workbooks have been merged into one sheet.
doc combine sheets in to one sheet 1


2. Combine multiple workbooks into one workbook

Here are three workbooks needed to be combined together into one workbook.

 Jan  Feb  Mar
doc combine sheets in to one sheet 1 doc combine sheets in to one sheet 1 doc combine sheets in to one sheet 1

doc samplejan.xlsx  doc samplefeb.xlsx  doc samplemar.xlsx

2.1 Combine workbooks into one workbook by the Move or Copy command


For only combining sheets across a few workbooks, the Excel’s Move or Copy command can do you a favor.

1. Open the first two workbooks you want to combine, activate the first workbook, select the sheets you want to move and then right click to enable the context menu, and click Move or Copy.
doc combine sheets in to one sheet 1

Tips

1) Right click at one tab and choose Select All Sheets from the context menu firstly if you want to move all sheets, then use the Move or Copy command.

2) Holding Ctrl key to select multiple non-adjacent sheets.

3) Select the first sheet and hold Shift key to select the last sheet to select multiple adjacent sheets.

2. In the Move or Copy dialog, in the To book drop-down list, choose the workbook you want to move the sheets to, here choose Jan, then specify the location you want to place the sheets in Before sheet section, click OK.
doc combine sheets in to one sheet 1

Note:

1) You’d better check Create a copy, or the original workbook will lose the worksheet after moving.

2) If you want to place all workbooks into a new workbook, choose (new book) in To book drop-down list.
doc combine sheets in to one sheet 1

How the sheets have been moved to the master workbook.
doc combine sheets in to one sheet 1

Repeat above steps to move all workbooks to one.

2.2 Combine multiple workbooks (in one folder) into one workbook with VBA code


To combine a lot of workbooks which all are in one folder, you can use a VBA code.

1. Enable the workbook that you want to merge all workbooks into, then press Alt + F11 keys to open the Microsoft Visual Basic for Applications window.
doc combine sheets in to one sheet 1

2. In the popped-out window, click Insert > Module to insert a new blank module.
doc combine sheets in to one sheet 1

3. Copy and paste below VBA code to the new module.

VBA: Combine multiple workbooks into current workbook

Sub GetSheets()
'Updated by Extendoffice
Path = "C:\Users\AddinTestWin10\Desktop\combine sheets\combine sheets into one workbook\"
Filename = Dir(Path & "*.xlsx")
  Do While Filename <> ""
  Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
     For Each Sheet In ActiveWorkbook.Sheets
     Sheet.Copy After:=ThisWorkbook.Sheets(1)
  Next Sheet
     Workbooks(Filename).Close
     Filename = Dir()
  Loop
End Sub

 doc combine sheets in to one sheet 1

Note

In the VBA code, the script "C:\Users\AddinTestWin10\Desktop\combine sheets\combine sheets into one workbook\" is the folder path where the workbooks you want to combine locate, please change it to meet your need.

If the workbooks you want to merge are in different folders, copy them into one folder firstly.

4. Press F5 key to run the code, then all workbooks have been copied to the end of the current workbook.
doc combine sheets in to one sheet 1

Tip: all copied sheets will use their original name, if you want to use the workbook name as prefix, please use below code:

Sub MergeWorkbooks()
'Updated by Extendoffice
Dim xStrPath As String
Dim xStrFName As String
Dim xWS As Worksheet
Dim xMWS As Worksheet
Dim xTWB As Workbook
Dim xStrAWBName As String
On Error Resume Next
xStrPath = "C:\Users\AddinTestWin10\Desktop\combine sheets\combine sheets into one workbook\"
xStrFName = Dir(xStrPath & "*.xlsx")
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set xTWB = ThisWorkbook
Do While Len(xStrFName) > 0
    Workbooks.Open Filename:=xStrPath & xStrFName, ReadOnly:=True
    xStrAWBName = ActiveWorkbook.Name
    For Each xWS In ActiveWorkbook.Sheets
    xWS.Copy After:=xTWB.Sheets(xTWB.Sheets.Count)
    Set xMWS = xTWB.Sheets(xTWB.Sheets.Count)
    xMWS.Name = xStrAWBName & "(" & xMWS.Name & ")"
    Next xWS
    Workbooks(xStrAWBName).Close
    xStrFName = Dir()
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

 doc combine sheets in to one sheet 1

2.3 Combine multiple workbooks (across multiple folders) into one workbook by using a handy tool


In sometimes, you may want to combine all workbooks in different folders into one workbook. For instance, to combine all workbooks in folder 2020 and 2021 to one workbook, there is no direct way in Excel can handle it.
doc combine sheets in to one sheet 1

However, the Combine feature of Kutools for Excel provides an option to combine workbooks across multiple folders into one.

After free installing Kutools for Excel, please do as below:

1. Enable Excel, click Kutools Plus > Combine, A dialog pops up to remind you of some notices about password, if the workbooks you want to use are not touch upon password, just click OK to continue.
doc combine sheets in to one sheet 1
doc combine sheets in to one sheet 1

2. In the Combine Worksheets – Step 1 of 3 window, check Combine multiple worksheets from workbooks into one workbook option, click Next button.
doc combine sheets in to one sheet 1

3. In the Step 2 of 3 window, click the arrow beside the Add button to display the drop-down menu, click Folder.
doc combine sheets in to one sheet 1

4. Then in the Select Folder dialog, choose one folder that you want to use, click Select Folder to add it to Workbook list section.
doc combine sheets in to one sheet 1
doc combine sheets in to one sheet 1

5. Repeat the above step to add all folders and list all workbooks in the Workbook list, click Next.
doc combine sheets in to one sheet 1

6. In the last step, choose options as you need. Click Finish button.
doc combine sheets in to one sheet 1

7. A dialog pops out for you to choose one folder to place the combined workbook, you can rename it in the File name bar, click Save to finish.
doc combine sheets in to one sheet 1

8. Now all sheets have been combined together, and a dialog pops out to ask you if save the settings as a scenario, click Yes or No as you like.
doc combine sheets in to one sheet 1

A new workbook pops out which lists the combining result, click the link of Output file to check the combined sheet.
doc combine sheets in to one sheet 1

Combine result

Note: in the combined workbook, the first sheet named Kutools for Excel lists some information about original worksheets and final combined sheets, you can delete it if you do not need.
doc combine sheets in to one sheet 1


3. Combine specific sheets from multiple workbooks into one workbook

If you only want to combine some sheets of workbooks into one workbook, not all sheets of each workbook, you can try below methods.

doc samplequarter-1.xlsx doc samplequarter-2.xlsx doc samplequarter-3.xlsx

3.1 Combine specific sheets from workbooks (all workbooks in the same folder) into one workbook with VBA code


1. Open a workbook to locate the combined worksheets, then press Alt + F11 keys to open the Microsoft Visual Basic for Applications window.

2. In the popped-out window, click Insert > Module to insert a new blank module.
doc combine sheets in to one sheet 1

3. Copy and paste below VBA code to the new module.

VBA: Combine specific sheets of multiple workbooks into current workbook

Sub MergeSheets2()
'Updated by Extendoffice
Dim xStrPath As String
Dim xStrFName As String
Dim xWS As Worksheet
Dim xMWS As Worksheet
Dim xTWB As Workbook
Dim xStrAWBName As String
Dim xI As Integer
On Error Resume Next
 
xStrPath = "C:\Users\AddinTestWin10\Desktop\combine sheets\combine specific sheets from multiple workbooks\"
xStrName = "A,B"
 
xArr = Split(xStrName, ",")
 
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set xTWB = ThisWorkbook
xStrFName = Dir(xStrPath & "*.xlsx")
Do While Len(xStrFName) > 0
Workbooks.Open Filename:=xStrPath & xStrFName, ReadOnly:=True
xStrAWBName = ActiveWorkbook.Name
For Each xWS In ActiveWorkbook.Sheets
For xI = 0 To UBound(xArr)
If xWS.Name = xArr(xI) Then
xWS.Copy After:=xTWB.Sheets(xTWB.Sheets.Count)
Set xMWS = xTWB.Sheets(xTWB.Sheets.Count)
xMWS.Name = xStrAWBName & "(" & xArr(xI) & ")"
Exit For
End If
Next xI
Next xWS
Workbooks(xStrAWBName).Close
xStrFName = Dir()
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True
 
End Sub

 doc combine sheets in to one sheet 1

Note

1) In the VBA code, the script " C:\Users\AddinTestWin10\Desktop\combine sheets\combine specific sheets from multiple workbooks\" is the path where the workbooks you want to combine locate, please change it to meet your need.

2) In the VBA code, the script "A,B" are the sheet names of the specific sheets I want to combine together from workbooks, change them as you need, using commas to separate each sheet name.

3) If the workbooks you want to merge are in different folders, copy them in one folder firstly.

4. Press F5 key to run the code, then only the specific worksheets have been copied to the end of the current workbook.
doc combine sheets in to one sheet 1

3.2 Combine specific sheets from multiple workbooks (across multiple folders) with a handy tool


If the workbooks you want to combine are in many different folders, you can try Kutools for Excel’s Combine feature.

After free installing Kutools for Excel, please do as below:

1. Enable Excel, click Kutools Plus > Combine, A dialog pops to remind you of some notices about password, if the workbooks you want to use do not touch upon password, just click OK to continue.
doc combine sheets in to one sheet 1
doc combine sheets in to one sheet 1

2. In the Combine Worksheets – Step 1 of 3 window, check Combine multiple worksheets from workbooks into one workbook option, click Next button.

doc combine sheets in to one sheet 1

3. In the Step 2 of 3 window, click the arrow beside the Add button to display the drop-down menu, click Folder.
doc combine sheets in to one sheet 1

4. Then in the Select Folder dialog, choose one folder that you want to use, click Select Folder to add it to Workbook list section.
doc combine sheets in to one sheet 1
doc combine sheets in to one sheet 1

5. Repeat above step to add all folders and list all workbooks in the Workbook list.
doc combine sheets in to one sheet 1

6. Then stay in Step 2 of 3 window, select one workbook in the Workbook list, and only check the sheets you want to use in Worksheet list, then, click Same sheet button. Now all same name sheets of workbooks in the Workbook list have been checked. Click Next to continue.
doc combine sheets in to one sheet 1

7. In the last step, choose options as you need. Click Finish button.
doc combine sheets in to one sheet 1

8. A dialog pops out for you to choose one folder to place the combined workbook, you can rename it in the File name bar, click Save to finish
doc combine sheets in to one sheet 1

9. Now the specific sheets have been combined together, and a dialog pops out to ask you if save the settings as a scenario, click Yes or No as you like.

A new workbook pops out which lists the combining result, click the link of Output file to check the combined sheet.

Combine result

Note: in the combined workbook, the first sheet named Kutools for Excel lists some information about original worksheets and final combined sheets, you can delete it if you do not need.
doc combine sheets in to one sheet 1


4. Combine two sheets based on key column

As below screenshot shown, there are two tables in two sheets, you want to combine these two tables into one based on one key column in Excel.

Table1  Table2 
doc combine sheets in to one sheet 1 doc combine sheets in to one sheet 1
Combined Table 
doc combine sheets in to one sheet 1


4.1 Combine two tables based on one key column by using Query (Excel 2016 or later versions)


The Query feature in Excel 2016 or later versions is very powerful, it supports to combine two tables based on a key column.

Before using the Query feature, make sure the ranges you want to combine have been created as tables.

doc samplecombine-two-tables-based-on-a-key-column-query.xlsx

1. Click at any cell of the first table, click Data > From Table in the Get & Transform group.

doc combine sheets in to one sheet 1

2. In the Query Editor dialog, click File > Close & Load To command. See screenshot:
doc combine sheets in to one sheet 1

3. Then in the popped-out Load To dialog, check Only Create Connection option. Click Load.
doc combine sheets in to one sheet 1

Now you can see the Workbook Queries pane display, and the table has been listed in the pane as a link.
doc combine sheets in to one sheet 1

Repeat above steps to add the second table to the Workbook Queries pane.
doc combine sheets in to one sheet 1

4. Then click Data > New Query > Combine Queries > Merge.

doc combine sheets in to one sheet 1

5. In the Merge window, choose the two tables you want to combine in the two drop-down lists separately. The table at the bottom will be combined into the table above.
doc combine sheets in to one sheet 1

6. The click at the key column you want to combine two tables based on, click OK.
doc combine sheets in to one sheet 1

7. The Merge1 – Query Editor window pops out, click the expand button beside NewColumn, then check all columns except the key column, uncheck Use original column name as prefix checkbox, click OK.
doc combine sheets in to one sheet 1

Now two tables have been merged into one based on the specified key column.
doc combine sheets in to one sheet 1

8. Click File > Close & Load To, in the Load To dialog, check Table option and specify the location you want to load. Click Load.
doc combine sheets in to one sheet 1 doc combine sheets in to one sheet 1

Now the two tables have been merged based on a key column.
doc combine sheets in to one sheet 1

4.2 Combine two sheets based on one key column by using Excel functions


If you only want to move one or two columns from one sheet to another and locate based on a key column, the Excel functions can help you.

For instance, move the complete data from sheet 2 to sheet 1 and based on column A to locate the data.

doc samplecombine-two-sheets-based-on-a-key-column-functions.xlsx

Sheet1  Sheet2 
doc combine sheets in to one sheet 1 doc combine sheets in to one sheet 1

4.21 Combine two sheets by a column with VLOOKUP

1. Copy and paste below formula beside the table in sheet 1:

=VLOOKUP(A2,Sheet2!$A$2:$B$5,2,FALSE)

Explanation:

A2: the first lookup value (key column);

Sheet2!$A$2:$B$5: the table array, the table contains two or more columns where the lookup value column and the result value column locating;

2: the column index, the specific column number (it is an integer) of the table_array, which you will return the matched value from.

2. Press Enter key to get the first result.
doc combine sheets in to one sheet 1

3. Then drag the autofill handle down to fill with all data.
doc combine sheets in to one sheet 1

4. Keep the formula cells highlighted, click Home tab, and go to format the cells as you need in the Number group.
doc combine sheets in to one sheet 1

For more information about VLOOKUP function, click here.

4.22 Combine two sheets by a column with a formula combine MATCH and INDEX functions

1. Copy and paste below formula beside the table in sheet 1:

=INDEX(Sheet2!$B$2:$B$5,MATCH(Sheet1!A2,Sheet2!$A$2:$A$5,0))

Explanation:

Sheet2!$B$2:$B$5: the column with the matching value you want to find;

Sheet1!A2: the first lookup value (in the key column);

Sheet2!$A$2:$A$5: the key column you want to combine two sheets based on.

2. Press Enter key to get the first result.
doc combine sheets in to one sheet 1

3. Then drag the autofill handle down to fill with all data.
doc combine sheets in to one sheet 1

4. Keep the formula cells highlighted, click Home tab, and go to format the cells as you need in the Number group.
doc combine sheets in to one sheet 1

For more about INDEX.

For more about MATCH.

4.3 Combine two sheets based on one key column by a handy tool


Except the Combine feature, there is another powerful feature – Tables Merge in Kutools for Excel, which can quickly and easily combine two sheets based on one key column.

doc samplecombine-two-sheets-based-on-a-key-column-handy-tool.xlsx

After free installing Kutools for Excel, please do as below:

1. Click Kutools Plus >Tables Merge.
doc combine sheets in to one sheet 1

2. In the Tables Merge – Step 1 of 5 window, select the table ranges into Select the main table and Select the lookup table section separately, click Next.
doc combine sheets in to one sheet 1

3. In the Step 2 of 5 window, check the key column you want to combine based on, click Next.
doc combine sheets in to one sheet 1

4. In the Step 3 of 5 window, you can check the columns that you want to update data based on the lookup table, if you do not need update data, just directly click Next.
doc combine sheets in to one sheet 1

5. In the Step 4 of 5 window, here check the columns you want to combine to the main table, then click Next.
doc combine sheets in to one sheet 1

6. In the last step, specify the options as you need, click Finish.
doc combine sheets in to one sheet 1

This feature supports to combine two sheets in different workbooks.


5. Combine two sheets based on two columns

Supposing, there are two tables in sheet1 and sheet2 separately, now, to move the data in column End_Dates from sheet2 to sheet1 based on the column Project and column Start_Date as below screenshot shown:

 Sheet1 Sheet2 
doc combine sheets in to one sheet 1 doc combine sheets in to one sheet 1
Combined Sheet 
doc combine sheets in to one sheet 1

doc sampleClick to download the sample file

In Excel, the built-in features do not support this operation, but the Tables Merge of Kutools for Excel can handle it.

After free installing Kutools for Excel, please do as below:

1. Click Kutools Plus > Tables Merge.
doc combine sheets in to one sheet 1

2. In the Tables Merge – Step 1 of 5 window, select the table ranges into Select the main table and Select the lookup table section separately, click Next.
doc combine sheets in to one sheet 1

3. In the Step 2 of 5 window, check the two key columns you want to combine based on, click Next.

Note: automatically, the related columns in lookup table will be matched, you can click at the column name in Lookup table columns to change them as you need.
doc combine sheets in to one sheet 1

4. In the Step 3 of 5 window, you can check the columns that you want to update data based on the lookup table, if you do not need update data, just directly click Next.

doc combine sheets in to one sheet 1

5. In the Step 4 of 5 window, here check the column(s) you want to combine to the main table, then click Next.
doc combine sheets in to one sheet 1

6. In the last step, specify the options as you need, click Finish.
doc combine sheets in to one sheet 1

Then the column(s) you want to add has been added to the end of the main table.
doc combine sheets in to one sheet 1

This feature supports to combine two sheets in different workbooks.


6. Combine sheets with same headers

To combine multiple sheets with same header as below screenshot shown:

doc combine sheets in to one sheet 1  doc combine sheets in to one sheet 1 
doc combine sheets in to one sheet 1 doc combine sheets in to one sheet 1
 Combined Sheet
doc combine sheets in to one sheet 1

doc sampleClick to download the sample file

6.1 Combine all sheets with same headers by using VBA


There is a VBA code can combine all sheets of a workbook with same headers.

1. Enable the workbook that you want to merge worksheets with the same header, then press Alt + F11 keys to open the Microsoft Visual Basic for Applications window.

2. In the popped-out window, click Insert > Module to insert a new blank module.
doc combine sheets in to one sheet 1

3. Copy and paste below VBA code to the new module.

VBA: Combine sheets with same header

Sub Combine()
'Update by Extendoffice
    Dim i As Integer
    Dim xTCount As Variant
    Dim xWs As Worksheet
    On Error Resume Next
LInput:
    xTCount = Application.InputBox("The number of title rows", "", "1")
    If TypeName(xTCount) = "Boolean" Then Exit Sub
    If Not IsNumeric(xTCount) Then
        MsgBox "Only can enter number", , "Kutools for Excel"
        GoTo LInput
    End If
    Set xWs = ActiveWorkbook.Worksheets.Add(Sheets(1))
    xWs.Name = "Combined"
    Worksheets(2).Range("A1").EntireRow.Copy Destination:=xWs.Range("A1")
    For i = 2 To Worksheets.Count
        Worksheets(i).Range("A1").CurrentRegion.Offset(CInt(xTCount), 0).Copy _
               Destination:=xWs.Cells(xWs.UsedRange.Cells(xWs.UsedRange.Count).Row + 1, 1)
    Next
End Sub

 doc combine sheets in to one sheet 1

4. Press the F5 key to run the code, a dialog pops out to ask you the number of header rows, type the number into the textbox. Click OK.
doc combine sheets in to one sheet 1

Now all sheets in this workbook have been combined in a new sheet named Combined.
doc combine sheets in to one sheet 1

6.2 Combine sheets with same headers by using a handy tool


The Combine feature of Kutools for Excel also can handle this job, and it supports to combine sheets across workbooks.

After free installing Kutools for Excel, please do as below:

1. Enable the workbook that you want to combine sheets with same headers, click Kutools Plus > Combine.
doc combine sheets in to one sheet 1

2. A dialog pops out to remind you of something about password, if the workbook(s) you use do not involve passwords, click OK to continue. In the popping out Combine Worksheets – Step 1 of 3 window, check Combine multiple worksheets from workbooks into one worksheet option, click Next.
doc combine sheets in to one sheet 1

3. In the Step 2 of 3 window, all sheets are listed and checked in Worksheet list section, click Next to go on.
doc combine sheets in to one sheet 1

Tips:

1) If you only want to combine some of sheets, just check the sheet names you need, and leave others unchecked.
doc combine sheets in to one sheet 1

2) If you want to add more workbooks to combine, click Add to add files or folders into the Workbook list section.
doc combine sheets in to one sheet 1

4. In the Step 3 of 3, check Combine by row option, and type the number of header rows in the Number of header rows textbox, specify other settings as you need. Click Finish.
doc combine sheets in to one sheet 1

5. Select a folder and give a name for the new workbook in the Specify the file name and location for the combined workbook dialog, click Save.
doc combine sheets in to one sheet 1

6. A dialog pops out to ask you if save the settings as a scenario, click Yes or No for you need. A workbook pops out to list the original workbook and new workbook links, click the new workbook path link to open it for checking.
doc combine sheets in to one sheet 1


Consolidate

1. Combine sheets and do some calculations

For example, there are three sheets with same row headers and column headers, now you want to combine them with same headers and sum the data as below screenshots shown.

doc combine sheets in to one sheet 1 doc combine sheets in to one sheet 1 doc combine sheets in to one sheet 1
Result 
doc combine sheets in to one sheet 1

doc sampleClick to download the sample file

1.1 Combine sheets and do calculates with Consolidate feature


In Excel, the Consolidate feature supports to combine sheets and do calculations.

1. Enable the workbook that you want to combine its sheets, and select a cell that you want to place the combined data, click Data > Consolidate.
doc combine sheets in to one sheet 1

2. In the Consolidate dialog, do below settings:

1) In the Function drop-down menu, choose the calculation you want to do after combining sheets.
doc combine sheets in to one sheet 1

2) Click the selection icon beside Browse button to choose the range to be combined and click Add to the All references section.
doc combine sheets in to one sheet 1

Repeat this step to add all ranges needed to be combined into the All references section.
doc combine sheets in to one sheet 1

3) In the Use labels in section, check the Top row and Left column checkboxes if the ranges have both of row header and column header.
doc combine sheets in to one sheet 1

4) If you want the combined contents to change as the sources data changes, check Create links to source data checkbox.
doc combine sheets in to one sheet 1

3. Click OK. The ranges have been combined and summed up based on headers.
doc combine sheets in to one sheet 1

Note: if the ranges you want to combine are in other workbooks, click Browse in the Consolidate dialog to choose the workbook, and then type the sheet name and range into the textbox and click Add to add the range to the All references section.
doc combine sheets in to one sheet 1

1.2 Combine sheets and do calculations with a handy tool


The Combine feature of Kutools for Excel supports combine sheets in multiple workbooks and do calculations into one sheet.

After free installing Kutools for Excel, please do as below:

1. Enable the workbook(s) that you want to consolidate, click Kutools Plus > Combine.
doc combine sheets in to one sheet 1

2. A dialog pops out to remind you of something about password, if the workbook(s) you use do not involve passwords, click OK to continue. In the popping out Combine Worksheets – Step 1 of 3 window, check Consolidate and calculate values across multiple workbooks into one worksheet option, click Next.
doc combine sheets in to one sheet 1

3. In the Step 2 of 3 window, all sheets of opened workbook(s) are listed and checked in Worksheet list section, click Next to go on.
doc combine sheets in to one sheet 1

Tip: if you only want to combine some of sheets, just check the sheet names you need, and leave others unchecked. If you want to add more workbooks to combine, click Add to add files or folders into the Workbook list section.
doc combine sheets in to one sheet 1

4. In the Step 3 of 3, specify the calculation, labels you need. Click Finish.
doc combine sheets in to one sheet 1

5. Select a folder and give a name for the new workbook in the Specify the file name and location for the combined workbook dialog, click Save.
doc combine sheets in to one sheet 1

6. A dialog pops out to ask you if save the settings as a scenario, click Yes or No for you need. A workbook pops out to list the original workbook and new workbook links, click the new workbook path link to open it for checking.
doc combine sheets in to one sheet 1


2. Combine multiple sheets into PivotTable

If your data is in a simple structure as below screenshot shown, you can combine the sheets into a PivotTable directly.

doc combine sheets in to one sheet 1 
doc combine sheets in to one sheet 1
doc combine sheets in to one sheet 1

doc sampleClick to download the sample

1. Enable the workbook you will use, click Customize Quick Access Toolbar > More Commands.
doc combine sheets in to one sheet 1

2. In the popping Excel Options window, choose All Commands from the Choose commands from section, then drag scroll bar to select PivotTable and PivotChart Wizard.
doc combine sheets in to one sheet 1

3. Click Add to add the PivotTable and PivotChart Wizard to the Customize Quick Access Toolbar, click OK.
doc combine sheets in to one sheet 1

4. Click the PivotTable and PivotChart Wizard command from the toolbar, and in the Step 1 of 3, check Multiple consolidation ranges and PivotTable options, click Next.
doc combine sheets in to one sheet 1 doc combine sheets in to one sheet 1

5. In the Step 2a of 3, check I will create the page fields option, click Next.
doc combine sheets in to one sheet 1

6. In the Step 2b of 3, click the selection icon to select the range you want to combine, click Add to add it to the All ranges section, repeat this step to add all ranges used for combining. Check 0 in the How many page fields do you want. Click Next.
doc combine sheets in to one sheet 1

7. Choose the location you want to create the PivotTable in Step 3 of 3, click Finish.
doc combine sheets in to one sheet 1

Now a pivot table has been created, specify the settings in PivotTable Fields pane as you need.
doc combine sheets in to one sheet 1

Note: if the structure of the data is complex, in my opinion, please combine sheets with general methods list above and then transform the result sheet to Pivot Table.


Google Sheet

1. Combine google sheets into one sheet

Supposing there are three google sheets needed to be combined into one as below screenshot shown:

doc combine sheets in to one sheet 1 doc combine sheets in to one sheet 1 doc combine sheets in to one sheet 1 

doc sampleClick to download the sample file

1.1 Combine google sheets into one sheet by the Filter function

In a sheet which you want to place the combined data, choose a cell, type below formula:

=({filter(A!A2:B, len(A!A2:A)); filter(B!A2:B, len(B!A2:A)); filter('C'!A2:B, len('C'!A2:A)) })
doc combine sheets in to one sheet 1

Then the data in sheet A, B and C has been copied.
doc combine sheets in to one sheet 1

In the formula:

A, B, C are the sheet names, A2:B is the data range in each sheet, A2:A is the first column of the each data range.

1.2 Combine google sheets into one sheet by the IMPORTRANGE function

This formula is frequently used to combine two ranges in google sheet.

Select a cell under the first data range, type below formula:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/13K4vuZukmS4-x0qJs0EAXQkneIdNqelruDBF5ff5xNQ/edit","B!A2:B4")
doc combine sheets in to one sheet 1

Then the data in sheet B has been copied below.
doc combine sheets in to one sheet 1

In the formula,

https://docs.google.com/spreadsheets/d/13K4vuZukmS4-x0qJs0EAXQkneIdNqelruDBF5ff5xNQ/edit

is the sheet location, you can find it in the web address bar.
doc combine sheets in to one sheet 1

B!A2:B4 is the cell range in the sheet B that you want to copied to the first range.

Note: if the cell that place the formula has been edited before, the formula will return #REF!
doc combine sheets in to one sheet 1


2. Combine google sheets into one workbook

If you want to combine sheets from multiple workbooks in Google Sheet, there is no quick way like Excel.

To combine google sheets from workbooks into one workbook, you can use the Copy to command of right-click Menu.

Right click at the sheet that you want to use, click Copy to > New spreadsheet or Existing spreadsheet.
doc combine sheets in to one sheet 1

If you choose New spreadsheet, a dialog pops out to remind you the sheet has been copied, click OK. Now the current sheet has been copied to a new workbook. You can click Open spreadsheet to check.
doc combine sheets in to one sheet 1

If you choose Existing spreadsheet, the Select a spreadsheet to copy this worksheet into dialog pops out.

1) Choose where the existing spreadsheet is;

2) Choose the workbook that you want to copy the sheet to in Files;

3) Or you can directly type the web address of the workbook you want to copy to in the Or paste a web address here bar.

4) Click Select.
doc combine sheets in to one sheet 1

If the sheet is copied into a new spreadsheet, the sheet name in the new workbook is the same as the original name, if the sheet is copied into an existing spreadsheet, the sheet name in the combined workbook will be added a prefix of Copy of.
doc combine sheets in to one sheet 1


Extension

1. Combine sheets then remove duplicates

Supposing there are two sheets which have some duplicates, we need to combine the data and remove the duplicates as below screenshot shown:

doc combine sheets in to one sheet 1 doc combine sheets in to one sheet 1 
General result Remove duplicates
doc combine sheets in to one sheet 1 doc combine sheets in to one sheet 1

doc sampleClick to download the sample

1.1 Combine sheets then remove duplicates by Remove Duplicates


In Excel, the Remove Duplicates feature helps you to quickly remove the duplicate data.

After using above methods to combine sheets, then select the combined data, click Data > Remove Duplicates.
doc combine sheets in to one sheet 1

In the Remove Duplicates dialog, choose the columns you want to remove duplicates from, you can check My data has headers to ignore the headers, click OK.
doc combine sheets in to one sheet 1

Then the duplicate rows have been removed.
doc combine sheets in to one sheet 1

1.2 Combine sheets then remove duplicates with a handy tool


If there are only two ranges needed to be combined and remove duplicates, the Tables Merge of Kutools for Excel can directly remove duplicates while combining.

After free installing Kutools for Excel, please do as below:

1. Enable the workbook that contains the data that you want to combine, click Kutools Plus > Tables Merge.
doc combine sheets in to one sheet 1

2. In the Tables Merge – Step 1 of 5 window, select the two ranges that you want to combine, and if the tables have two headers, check the Main table has header and Lookup table has header checkboxes, click Next.
doc combine sheets in to one sheet 1

3. In the Step 2 of 5 window, check the key column that you want to combine ranges based on, click Next.
doc combine sheets in to one sheet 1

4. In the Step 3 of 5 window, you can check the columns that you want to update data based on the lookup table, click Next to go to the last step.

Tip: if there is a new column in the lookup table which needed to add to the main table, there will be a step 4 of 5 window for adding columns.
doc combine sheets in to one sheet 1

5. In the Step 5 of 5 window, in the Add options section, check Add mismatched rows to the end of the main table checkbox, in the Update options section, check Only update the cells when there is data in the lookup table checkbox. Click Finish.
doc combine sheets in to one sheet 1

Then the two tables are combined into the main table without duplicates.
doc combine sheets in to one sheet 1


2. Combine worksheets of same names into one worksheet.

Supposing there are multiple workbooks with same name sheets, such as sheet1, sheet2, now to combine all sheets named sheet1 into one sheet, all sheets named sheet2 into one sheet as below screenshot shown, you can use the Kutools for Excel’s Combine feature to quickly deal with this job.

doc combine sheets in to one sheet 1 doc combine sheets in to one sheet 1 doc combine sheets in to one sheet 1
doc combine sheets in to one sheet 1 doc combine sheets in to one sheet 1 doc combine sheets in to one sheet 1

doc sample2020.xlsx doc sample2021.xlsx

After free installing Kutools for Excel, please do as below:

1. Enable the workbooks you will combine, click Kutools Plus > Combine.
doc combine sheets in to one sheet 1

2. A dialog pops out to remind you of something about password, if the workbook(s) you use do not involve passwords, click OK to continue. In the popping out Combine Worksheets – Step 1 of 3 window, check Combine all same name worksheets into one worksheet option, click Next.
doc combine sheets in to one sheet 1

3. In the Step 2 of 3 window, all sheets of opened workbook(s) are listed and checked in Worksheet list section, click Next to go on.
doc combine sheets in to one sheet 1

Tips:

If you only want to combine some of sheets, just check the sheet names you need, and leave others unchecked.

If you want to add more workbooks to combine, click Add to add files or folders into the Workbook list section.
doc combine sheets in to one sheet 1

4. In the Step 3 of 3, specify the settings as you need. Click Finish.
doc combine sheets in to one sheet 1

5. Select a folder and give a name for the new workbook in the Specify the file name and location for the combined workbook dialog, click Save.
doc combine sheets in to one sheet 1

6. A dialog pops out to ask you if save the settings as a scenario, click Yes or No for you need. A workbook pops out to list the original workbook and new workbook links, click the new workbook path link to open it for checking.
doc combine sheets in to one sheet 1

Combine by row
doc combine sheets in to one sheet 1

Combine by column
doc combine sheets in to one sheet 1


3. Combine same ranges across sheets into one sheet

If you want to combine same ranges across sheets into one sheet, for example, only combine the range A1:B5 of workbook A and workbook B into one sheet, the Combine feature of Kutools for Excel will be a good choice.

A B Combined Reuslt
doc combine sheets in to one sheet 1 doc combine sheets in to one sheet 1 doc combine sheets in to one sheet 1

doc sampleA.xlsx doc sampleB.xlsx

1. Enable the workbooks you will use, click Kutools Plus > Combine.
doc combine sheets in to one sheet 1

2. A dialog pops out to remind you of something about password, if the workbook(s) you use do not involve passwords, click OK to continue. In the popping out Combine Worksheets – Step 1 of 3 window, check Combine multiple worksheets from workbooks into one worksheet option, click Next.
doc combine sheets in to one sheet 1

3. In the Step 2 of 3 window, all sheets of opened workbook(s) are listed and checked in Worksheet list section, click the selection icon in the Worksheet list, then select the range that you want to use. Then click Same range button to set all sheets’ range to A1:B5. Click Next.
doc combine sheets in to one sheet 1
doc combine sheets in to one sheet 1

Tips:

1) If you only want to combine some of sheets, just check the sheet names you need, and leave others unchecked.

2) If you want to add more workbooks to combine, click Add to add files or folders into the Workbook list section.

4. In the Step 3 of 3, specify the settings as you need. Click Finish.

5. Select a folder and give a name for the new workbook in the Specify the file name and location for the combined workbook dialog, click Save.

6. A dialog pops out to ask you if save the settings as a scenario, click Yes or No for you need. A workbook pops out to list the original workbook and new workbook links, click the new workbook path link to open it for checking.


You may also be interested in

Combine / Import Multiple Csv Files Into Multiple Worksheets
Provides methods to import CSV files into Excel

Combine Cells And Keep The Cell Formatting In Excel
In this article, it can help you quickly ccombine cells together and keep formatting.

Combine Contents Of Multiple Cells Without Losing Data In Excel
Here introduces the quick ways on combining multiple cells without losing data..

Combine Date And Time Into One Cell In Excel
It gives two ways for combining date and time into one cell and displaying as datetime.



  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name...
kte tab 201905
  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.