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

Combining multiple workbooks into one master workbook can be quite intimidating. Depending on the contents and the number of worksheets in each of the workbooks, the combining process can vary. To help with that, In this article, you will learn 6 methods to combine multiple workbooks into one workbook in Excel.

Download the Practice Workbook

You can download the Excel file from the following link and practice along with it.

6 Ways to Combine Multiple Workbooks To One Workbook in Excel

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

The first method is very simple and basic. If you have a few workbooks to combine and each of them has a few worksheets, then you can use this method.

All you need to do is,

❶ First copy the cell ranges that you want to move into the master workbook.

• You can press CTRL + C by selecting the cell range first to copy.
• Or simply go to the Home tab and click on the Copy command.

❷ After that, create a new worksheet in the master workbook.

• To do that, click on the plus icon that you will find at the bottom of your workbook.

❸ Then press CTRL + V to paste the data into the master workbook.

This is how you can move individual worksheets into a master workbook.

2. Copy the Worksheets to Combine Multiple Workbooks To One Workbook in Excel

If you have a few worksheets to combine, then this method is super handy.

All you need to do is,

❶ First 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.

❷ Then right-click on the selection area and a list will pop up. Choose Move or Copy from the list.

❸ Then 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.

❹ After that select the position of the copied or moved worksheets in the new workbook under the Before Sheet command text.

❺ By default, the selected worksheets are moved into the new workbook. If you want to copy them, then mark tick on the Create a copy check box.

❻ Finally hit the OK button.

3. Use the INDIRECT Function to Combine Multiple Workbooks To One Workbook in Excel

This method is a bit complex to implement. Therefore, two keynotes you have to keep in mind before implementing this one.

• It works only when you have the source workbook open.
• If you delete any data from the source file, Excel will report an error.

Anyways, here are the steps to follow:

❶ Insert the File Name, Sheet Name, and Cell addresses from where you want to retrieve data in the destination worksheet.

❷ Then insert the following formula in the destination cell address of the destination workbook.

Here,

• $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.

❸ After that press the ENTER button.

❹ Drag the Fill Handle icon to the cell ranges where you want to get the data from the source file.

4. Use CSV File Format to Combine Multiple Workbooks To One Workbook in Excel

You can combine lots of Excel files within a matter of a second using this method. But one condition is, you have to save files in CSV file format.

Anyways, here are the steps to follow:

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

❺ Then enter the following command.

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

❻ Then hit the ENTER button.

❼ Then open the Combined.csv file.

❽ Save is a normal Excel file (.xlsx).

5. Use PowerQuery to Combine Multiple Workbooks To One Workbook in Excel

You can use PowerQuery to combine a large number of Excel workbooks into one master workbook. You will get this feature available only in Microsoft Office 365.

To use this feature,

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

❷ Then go to the Data tab from the main ribbon.

❸ Choose the Get Data command.

❹ Then navigate to From File > From Folder.

❺ Then browse the folder where you stored all the Excel files.

❻ Select them all to combine into one master workbook.

6. Use VBA Code to Combine Multiple Workbooks To One Workbook in Excel

This method is super handy when you have to combine multiple workbooks together. All you need to do is run the VBA code and then select the workbooks that you want to combine.

Now follow the steps below to know how to use this VBA code.

❶ Press ALT + F11 to open the VBA editor.

❷ Then go to Insert > Module to create a new module.

❸ Copy the following VBA code and paste it to 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 this code using CTRL + S.

❺ Hit the Run Sub button or press the F5 key to run the above code.

After pressing the F5 key, you will have options to select files. Pick as many Excel files as you want to combine all together.

Things to Remember

• The third method works only when the source file is open.
• If you delete any data from the source file, you will get an error in the third method.

Conclusion

To sum up, we have discussed 6 ways to combine multiple workbooks into one master workbook in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website Exceldemy to explore more.

Further Readings

• How to Compare and Merge Excel Workbooks (3 Easy Steps)
• Merge Multiple Excel Files into One Sheet by VBA (3 Criteria)