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

Are you worried about how to merge data from multiple workbooks and finding a proper guideline for that? No worries! You have come to the right place. This article will provide you with 5 quick methods to merge data from multiple workbooks in Excel with sharp steps and clear illustrations.

How to Merge Data from Multiple Workbooks in Excel: 5 Ways

To explore the 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.

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

First of all, we’ll learn the simplest method to merge data from multiple workbooks. It is helpful for a small dataset but for a large dataset, this method is not so effective.

Steps:

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

• Later, open your main worksheet where you want to copy.
• After that, just paste to the preferred position by pressing Ctrl+V.

• In the same way, copy the data range from another source workbook.

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

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

Now we’ll learn another manual way to combine data from multiple workbooks. Here, we’ll be able to copy or move whole sheets from one workbook to another for combining.

Steps:

• Open your source workbook.
• Then right-click your mouse on the sheet title that you want to copy or move.
• Later, select Move or Copy from the Context menu.

• Select the destination workbook from the To book box.
• Then select the pasting position.
• Mark on Creat a copy if you want to copy.
• Finally, just press OK.

• Follow the same procedure for another source worksheet.

As a result, we’ll see that the sheets from the other workbooks are copied successfully.

3. Use Excel INDIRECT Function to Merge Data from Multiple Workbooks

Now we’ll take the help of an Excel function to merge data from two workbooks which is the INDIRECT function. We’ll merge two salespersons’ sales from two different workbooks. That’s why I modified the dataset and placed the workbook names, sheet names, row, and column numbers of the corresponding salespersons.

Steps:

• Write the following formula in cell G5–

• Then just press the Enter button.

• Finally, drag down the Fill Handle icon to copy the formula for the salesperson.

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

4. Merge Data from Multiple Workbooks Using Excel VBA Macros

There is an exclusive way to merge data from multiple workbooks using VBA macros. Let’s see how to apply VBA code to merge cells in Excel.

Steps:

• Open your main workbook.
• Press Alt+F11 to open the VBA window.
• Then Click: Insert > Module.

• After that, type the following codes-

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

• Finally, click the Run icon to run the codes.

It will open a file browsing window to select a source file.

• Select the source file and press OK.

Then Excel will merge all the worksheets from that source workbook. For another source workbook, run the codes again and follow the same next steps.

5. Use Power Query to Merge Data from Multiple Workbooks

In our very last method, we’ll use the Power Query feature of Excel. It takes more steps but may be helpful in some cases.

Steps:

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

• Select that folder and press Open.

• Then select Combine & Load to from the Combine box.

• At this moment, click the Parameter box and press OK.

• Select the data type and preferred sheet.
• Finally, just press OK.

• Soon after, 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.

Now have a look that the files are merged.

Download Practice Workbook

You can download the free Excel template from here and practice on your own.

Conclusion

I hope the procedures described above will be good enough to merge data in Excel from multiple workbooks. Feel free to ask any question in the comment section and please give me feedback.

<< Go Back to Cells | Merge | Learn Excel