Excel - Merge Workbooks

Microsoft Excel's powerful Power Query component enables users to merge workbooks seamlessly. For instance, a data engineer may need to integrate datasets from multiple units, leveraging Power Query to load, transform, and combine the various workbooks into one workbook.

However, column names must be identical across all workbooks to consolidate the data. Experts may also utilize VBA code to merge workbooks. Alternatively, you can manually copy the data, excluding the column header, and paste it into the next empty rows of another dataset for smaller datasets.

Merge Workbooks in Microsoft Excel

Step 1 − Below are the two Excel workbooks in the Merge workbook folder in the D column with identical columns.

Note − Ensure that two workbooks must be placed in the same folder.

Step 2 − Open a new workbook, select the Data tab, expand the "Get Data" button, expand the From File option, and then select "From Folder" from the drop-down menu.

Step 3 − In the Browse window, specify the "Merge workbook" folder name and click the "Open" button.

Step 4 − After that, another dialog box, "D Merge workbook," will open. Click on the Combine button and choose the "Combine and Load" option from the drop-down menu.

Note − If you require data cleaning first, choose the "Combine & Transform Data" option to eliminate errors.

Step 5 − Another dialog box, "Combine Files," will appear. You can choose the sample file, "First File," and select Sheet1. The preview of Sheet 1 is displayed in Sheet 1. Click the OK button.

Therefore, using the Power Query tool, both workbooks merge into one. The first column, Source Name, specifies the source name indicating the file name of each record.

Note − If the column names are not identical, the multiple workbooks will not be merged.