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.
How to Combine Multiple Workbooks to One Workbook in Excel: 6 Ways
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 into one workbook 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.
Read More: How to Merge Excel Files into One Using CMD
2. Copy the Worksheets to Combine Multiple Workbooks into One Workbook in Excel
This method combines multiple Excel workbooks into one workbook. 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.
Read More: Combine Multiple Excel Files Into One Workbook with Separate Sheets
3. Use the INDIRECT Function to Combine Multiple Workbooks into 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.
=INDIRECT("'[" & $B$3 & "]" & $C$3 & "'!" & D3)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.
Read More: How to Consolidate Data from Multiple Workbooks in a Single Worksheet
4. Use CSV File Format to Combine Multiple Workbooks into One Workbook in Excel
You can combine lots of Excel files within a matter of a second using this method. But one condition is, that 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.
Copy *csv Combined.csvHere 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 with the help of VBA. 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 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 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 them all together.
Read More: How to Merge Multiple Excel Files Into One Sheet by VBA
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.
Download the Practice Workbook
You can download the Excel file from the following link and practice along with it.
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.
What is the best way to combine multiple Excel spreadsheets/pivot tables together? I have 13 spreadsheets and want to create a dashboard to view all the data in one place. Once combined, will I have to refresh the data each time, or can it be dynamically linked so the data is automatically refreshed?
Hello BLAKE HUGUENIN. Thank you for your query. I believe the best way to combine multiple spreadsheets and pivot tables is using Power Query. Please follow the below steps:
Initially, select Data >> Get Data >> From File >> From Excel Workbook Toolbar.
Now, Get the Import File dialog box and select the required file.
Then navigator tab will be visible and select the worksheet to copy the sheet in another workbook. Complete the process by clicking on Load and Close.
Now repeat this process to add sheets as much as you want. Using Power Query is better because if you change the value in any of the workbooks then the value will be changed in master workbook.
Now, if you want to create a dashboard that will be dynamic and change the values of the data if the primary data is changed then use formula =(Sheetname!Range) in the dashboard. For instance =(File4!C5:C13). Please find Add Multiple Worksheets in the master workbook