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.

Download Practice Workbook

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


5 Ways to Merge Data from Multiple Workbooks in Excel

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.

Copy the Cell Ranges to Merge Data in Excel from Multiple Workbooks

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

Copy the Cell Ranges to Merge Data in Excel from Multiple Workbooks

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

Copy the Cell Ranges to Merge Data in Excel from Multiple Workbooks

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

Manually Copy Worksheets to Merge Data from Multiple Workbooks in Excel

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

Manually Copy Worksheets to Merge Data from Multiple Workbooks in Excel

  • 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, placed the workbook names, sheet names, row, and column numbers of the corresponding salespersons.

Steps:

  • Write the following formula in Cell G5
=INDIRECT("'"&$C5&$D5&"'!"&$F$5&$E$5)
  • Then just press the Enter button.

Use Excel INDIRECT Function to Merge Data from Multiple Workbooks in Excel

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

Use Excel INDIRECT Function to Merge Data from Multiple Workbooks in Excel

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

Steps:

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

Merge Data from Multiple Workbooks Using Excel VBA Macros

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

Merge Data from Multiple Workbooks Using Excel VBA Macros

  • 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 petty more steps but may be helpful for some cases.

Steps:

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

Use Power Query to Merge Data from Multiple Workbooks

  • Select that folder and press Open.

Use Power Query to Merge Data from Multiple Workbooks

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

Use Power Query to Merge Data from Multiple Workbooks

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

Use Power Query to Merge Data from Multiple Workbooks

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

Use Power Query to Merge Data from Multiple Workbooks

  • Soon after, you will get the Queries & Connections section at 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.


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.

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo