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.
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 |
 |  |  |
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.
All 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
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.
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.
 |
 |
 |
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 |
 |  |
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.
 |  |  |
 |  |  |
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 |
 |  |  |
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 Combine Cells And Keep The Cell Formatting In Excel Combine Contents Of Multiple Cells Without Losing Data In Excel Combine Date And Time Into One Cell In Excel |
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
- 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...
- 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!
