Combine Multiple Workbooks

How to combine multiple workbooks into one worksheet? Our Excel data processing tools series continues! It’s a very important task if you consolidate raw data.

A short time ago, we became acquainted with the Excel worksheet splitter, which was very helpful in cutting up and grouping data tables.

It is possible that we don’t have to cup up the data table, but we need to do just the opposite.

As a business analyst and advanced Excel user, I often merge more (usually 100+ pieces) Excel worksheets or workbooks into one table, possibly in a short time.

One of the managers’ favorite habits is trying to over-persuade their employees to perform better by impossible deadlines, but that’s how the cookie crumbles.

Combine Multiple Workbooks – Folder location

combine-multiple-workbooks-two

If we don’t automate these procedures, we may struggle with this task forever. However, this Excel know-how can also come in handy anytime!

That’s why today’s lesson will combine multiple workbooks into one Excel file.

In this case, the Excel-supported automated data processing add-in will help us quickly execute our processes. As usual, the add-in can be reached from the ribbon.

Our first task is determining the location of the Excel files we want to combine. Then, we can browse the files’ folders by clicking the Browse command button.

Next to the button, we have placed a checkbox.

If you have it checked, when the files are combined, the program will consider the folder and its sub-folders when analyzing the workbooks to be combined.

For example, frequent practice groups the marketing data by year, and the monthly data will be placed into sub-folders.

Join Multiple Workbooks – File Types

combine-multiple-workbooks-three

First, let’s discuss what kind of files the combination can be applied to. The program currently supports the .xls, .xlsx, .xlsm, and .xlsb files. We would also like to introduce the .xml and .csv types in the next version.

After choosing the file types, we can decide whether to interlink all the files or just some. We can choose from three checkboxes.

The first option is to choose the files manually. This option is best when we want to combine only a few Excel files.

The second option is to have the program process all the files in the folder. Again, there is no limitation. The add-in reads all workbooks and combines the data found therein.

The third option enables unique settings. For example, if we have to choose from thousands of files containing the word “sales,” we will write this word. So, only the sales data will be in one table.

Of course, this is only an example; we can search by virtually any word or word fragment.

Combine Multiple Workbooks – Options

It seems we are making great progress. Let’s move on to the next step! In the first two steps, we determined the file types and the output folder.

Do we have to process all the worksheets from the file? Because one workbook usually contains several worksheets, the question is valid!

We can choose the most appropriate one from the ones in the picture. We can include all the worksheets in the interlinking but also give a specific name. In this case, we should choose based on the type of the current task.

combine-multiple-workbooks-four

Merge Multiple Workbooks – Additional Settings

Finally, determine the range in which the data on each worksheet is located. If we know their place in the fixed range field, we can set the range, for example, A1 : D10.

In this case, the interlinked file will contain only the determined range, not the whole worksheet’s content.

Suppose choosing the second checkbox isn’t a limitation. In that case, the VBA code will not consider the worksheet ranges but will automatically work with all the information.

Suppose we want to keep the data in its original structure, then leave the “Paste as Values” checkbox blank. If you would also like to keep the format (colors, etc.), mark this checkbox.

combine-multiple-workbooks-five

Combine Multiple Workbooks – DataXL Add-in

It is no accident if you notice that the graphic user interface (GUI) is often changing. However, we have already determined the final goal: build a complex Excel data processing add-in.

We apply developments continually, so there may be smaller changes to the GUI (graphical user interface). But we guarantee that these changes only affect the user interface. The functions just introduced will only expand.

Finally, let’s say a few words about compatibility! We tested the Combine Multiple Workbooks add-ins in Excel 2007 – 2016. Download free Excel add-ins and tools!