How to Merge Data from Multiple Workbooks in Excel (5 Methods)

We’ll use the following dataset that represents some salespersons’ sales in different regions. We’ll use two more workbooks, which also represent sales for different months.

Method 1 – Copy the Cell Ranges to Merge Data from Multiple Excel Workbooks

Steps:

• Open the first source workbook.
• Copy the data range by pressing Ctrl + C.

• Open your main worksheet where you want to copy.
• Paste to the preferred position by pressing Ctrl + V.

• Repeat to copy the data range from another source workbook.

• Paste it on the main sheet.
• Follow the same procedures if you have more source worksheets.

Method 2 – Manually Copy Worksheets to Merge Data from Multiple Workbooks in Excel

Steps:

• Open your source workbook.
• Right-click on the sheet title that you want to copy or move.
• Select Move or Copy from the Context menu.

• Select the destination workbook from the To book box.
• Select the pasting position.
• Mark on Create a copy if you want to copy.
• Press OK.

• Follow the same procedure for the other source worksheets.

Method 3 – Use the Excel INDIRECT Function to Merge Data from Multiple Workbooks

We’ll merge two salespersons’ sales from two different workbooks. We modified the dataset and placed the workbook names with brackets, sheet names, row, and column numbers of the corresponding salespersons.

Steps:

• Use the following formula in cell G5:

• Press the Enter button.

• Drag down the Fill Handle icon to copy the formula for the salesperson.

You will get the output like the image below.

Note: You will have to keep the source workbooks open while using the INDIRECT function otherwise it will return #REF! error.

Method 4 – Merge Data from Multiple Workbooks Using Excel VBA Macros

Steps:

• Open your main workbook.
• Press Alt + F11 to open the VBA window.
• Click on Insert and select Module.

• Insert the following code:

Sub Merge_Data()
    Dim No_of_Files, i As Integer
    Dim Temp_F_Dialog As FileDialog
    Dim Main_Workbook, Source_Workbook As Workbook
    Dim Temp_WorkSheet As Worksheet
    Set Main_Workbook = Application.ActiveWorkbook
    Set Temp_F_Dialog = Application.FileDialog(msoFileDialogFilePicker)
    Temp_F_Dialog.AllowMultiSelect = True
    No_of_Files = Temp_F_Dialog.Show
    For i = 1 To Temp_F_Dialog.SelectedItems.Count
        Workbooks.Open Temp_F_Dialog.SelectedItems(i)
        Set Source_Workbook = ActiveWorkbook
        For Each Temp_WorkSheet In Source_Workbook.Worksheets
            Temp_WorkSheet.Copy after:=Main_Workbook.Sheets(Main_Workbook.Worksheets.Count)
        Next Temp_WorkSheet
        Source_Workbook.Close
    Next i
End Sub

• Click the Run icon to run the code.
• It will open a file browsing window to select a source file.

• Select the source file and press OK.

Excel will merge all the worksheets from that source workbook.

• For another source workbook, run the code again and follow the same steps.

Method 5 – Use Power Query to Merge Data from Multiple Workbooks

Steps:

• Keep your source workbooks in the same folder.
• Click as follows in the main worksheet: Data > Get Data > From File > From Folder.

• Select the folder and press Open.

• Select Combine & Load to from the Combine box.

• Click the Parameter box and press OK.

• Select the data type and preferred sheet.
• Press OK.

• You will get the Queries & Connections section on the right side of your Excel file.
• Scroll down and click on your folder name from that section.

The files are merged.

Download the Practice Workbooks

<< Go Back to Cells | Merge | Learn Excel