How to Combine Multiple Workbooks to One Workbook in Excel (6 Ways)

Method 1 – Copy and Paste the Cells Ranges to Combine Multiple Workbooks into One Workbook in Excel

• Copy the cell ranges that you want to move into the master workbook. You can press Ctrl + C after selecting the cell range or go to the Home tab and click on the Copy command.

• Click on the plus icon at the bottom of your workbook.

• Press Ctrl + V to paste the data into the master workbook.

Method 2 – Copy the Worksheets to Combine Multiple Workbooks into One Workbook in Excel

• Select all the worksheets that you want to move or copy into another master worksheet. You can hold the Ctrl key and manually select all the worksheets using your mouse.
• Right-click on the selection area and a list will pop up.
• Choose Move or Copy from the list.

• The Move or Copy dialog box will appear. Select the workbook where you want to move or copy. This option is available in the To Book drop-down box.
• Select the position of the copied or moved worksheets in the new workbook under the Before Sheet command text.
• The selected worksheets are moved into the new workbook by default. If you want to copy them, check the Create a copy check box.
• Hit the OK button.

Method 3 – Use the INDIRECT Function to Combine Multiple Workbooks into One Workbook in Excel

• Insert the File Name, Sheet Name, and Cell addresses from where you want to retrieve data in the destination worksheet.
• Insert the following formula in the destination cell address of the destination workbook:

$B$3 contains the source Excel file name.

$C$3 holds the worksheet name.

D3 refers to the cell address of the source file from where you want to pull data.

• Press the Enter key.
• Drag the Fill Handle icon to the cell ranges where you want to get the data from the source file.

Method 4 – Use the CSV File Format to Combine Multiple Workbooks into One Workbook in Excel

• Save each of the Excel files in CSV file format.
• Store them all in the same folder.

• Open the Command Prompt.
• Navigate to the folder where you’ve stored all the CSV files.
• Enter the following command.

Here, Combined is an arbitrary file name. You can choose whatever you like.

• Hit Enter.

• Open the Combined.csv file.
• Save as a normal Excel file (.xlsx).

Method 5 – Use PowerQuery to Combine Multiple Workbooks in One Workbook in Excel

• Store all the Excel files in the same folder to combine them all together.

• Go to the Data tab from the main ribbon.
• Choose the Get Data command.
• Navigate to From File and to From Folder.

• Browse the folder where you stored all the Excel files.
• Select them all to combine into one master workbook.

Method 6 – Use VBA Code to Combine Multiple Workbooks into One Workbook in Excel

• Press Alt + F11 to open the VBA editor.
• Go to Insert and select Module to create a new module.

• Copy the following VBA code and paste it into the VBA editor.

Sub CombineMultipleFiles()
Dim x, i As Integer
Dim y As FileDialog
Dim m, n As Workbook
Dim z As Worksheet
Set m = Application.ActiveWorkbook
Set y = Application.FileDialog(msoFileDialogFilePicker)
y.AllowMultiSelect = True
x = y.Show
For i = 1 To y.SelectedItems.Count
Workbooks.Open y.SelectedItems(i)
Set n = ActiveWorkbook
For Each z In n.Worksheets
z.Copy after:=m.Sheets(m.Worksheets.Count)
Next z
n.Close
Next i
End Sub

• Save the code with Ctrl + S.
• Hit the Run Sub button or press the F5 key to run the code.

• You will get prompts to select files. Pick as many Excel files as you want to combine them all together.

Download the Practice Workbook

<< Go Back To Merge Excel File | Merge in Excel | Learn Excel