Excel Tutorial: Combine Multiple Workbooks/Worksheets into One

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

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

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

2. Then in the popping window, click Insert > Module to insert a new blank module.

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

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.

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.

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

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.

3. Click the expand button beside Content and check Expand option and Select All Columns checkbox.

4. Click OK. All the tables are listed one by one.

You can right click at a column header to do other operations at the selected column.

5. Then click File > Close & Load To….

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.

Now a new sheet is created to place all merged tables.

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.

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

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.

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.

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.

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.

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

Combine by row

Combine by column

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.

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

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.

4. In the last step, specify 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.

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.

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.

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.

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.

Tip: if you want to add workbooks to combine, click Add button to choose a path to add workbooks.

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.

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.

---

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

Jan	Feb	Mar

jan.xlsx  feb.xlsx  mar.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.

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.

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.

How the sheets have been moved to the master workbook.

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.

2. In the popped-out window, click Insert > Module to insert a new blank module.

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

 

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.

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

 

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.

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.

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

3. In the Step 2 of 3 window, click the arrow beside the Add button to display the drop-down menu, click Folder.

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.

5. Repeat the above step to add all folders and list all workbooks in the Workbook list, click Next.

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

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.

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.

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.

---

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

quarter-1.xlsx quarter-2.xlsx quarter-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.

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

 

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.

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.

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

3. In the Step 2 of 3 window, click the arrow beside the Add button to display the drop-down menu, click Folder.

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.

5. Repeat above step to add all folders and list all workbooks in the Workbook list.

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.

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

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

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.

---

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

Combined Table

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.

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

2. In the Query Editor dialog, click File > Close & Load To command. See screenshot:

3. Then in the popped-out Load To dialog, check Only Create Connection option. Click Load.

Now you can see the Workbook Queries pane display, and the table has been listed in the pane as a link.

Repeat above steps to add the second table to the Workbook Queries pane.

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

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.

6. The click at the key column you want to combine two tables based on, click OK.

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.

Now two tables have been merged into one based on the specified key column.

8. Click File > Close & Load To, in the Load To dialog, check Table option and specify the location you want to load. Click Load.
 

Now the two tables have been merged based on a key column.

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.

combine-two-sheets-based-on-a-key-column-functions.xlsx

Sheet1	Sheet2

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.

3. Then drag the autofill handle down to fill with all data.

4. Keep the formula cells highlighted, click Home tab, and go to format the cells as you need in the Number group.

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.

3. Then drag the autofill handle down to fill with all data.

4. Keep the formula cells highlighted, click Home tab, and go to format the cells as you need in the Number group.

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.

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

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.

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

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.

5. In the Step 4 of 5 window, here check the columns you want to combine to the main table, then click Next.

6. In the last step, specify the options as you need, click Finish.

This feature supports to combine two sheets in different workbooks.

---

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

Combined Sheet

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

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.

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.

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.

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

6. In the last step, specify the options as you need, click Finish.

Then the column(s) you want to add has been added to the end of the main table.

This feature supports to combine two sheets in different workbooks.

---

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

Combined Sheet

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

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

 

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.

Now all sheets in this workbook have been combined in a new sheet named Combined.

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.

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.

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

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

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.

---

Consolidate

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.

Result

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

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.

2) Click the selection icon beside Browse button to choose the range to be combined and click Add to the All references section.

Repeat this step to add all ranges needed to be combined into the All references section.

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.

4) If you want the combined contents to change as the sources data changes, check Create links to source data checkbox.

3. Click OK. The ranges have been combined and summed up based on headers.

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.

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.

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.

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.

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.

4. In the Step 3 of 3, specify the calculation, labels 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.

---

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

Click to download the sample

1. Enable the workbook you will use, click Customize Quick Access Toolbar > More Commands.

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.

3. Click Add to add the PivotTable and PivotChart Wizard to the Customize Quick Access Toolbar, click OK.

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.
 

5. In the Step 2a of 3, check I will create the page fields option, click Next.

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.

7. Choose the location you want to create the PivotTable in Step 3 of 3, click Finish.

Now a pivot table has been created, specify the settings in PivotTable Fields pane as you need.

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

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

Click 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)) })

Then the data in sheet A, B and C has been copied.

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")

Then the data in sheet B has been copied below.

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.

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!

---

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.

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.

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.

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.

---

Extension

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

General result	Remove duplicates

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

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.

Then the duplicate rows have been removed.

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.

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.

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

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.

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.

Then the two tables are combined into the main table without duplicates.

---

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.

2020.xlsx 2021.xlsx

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

1. Enable the workbooks you will combine, click Kutools Plus > Combine.

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.

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.

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.

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.

Combine by row

Combine by column

---

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

A.xlsx B.xlsx

1. Enable the workbooks you will use, click Kutools Plus > Combine.

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.

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.

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.