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 got acquainted with the Excel worksheet splitter, which gave us a big help 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 have the task of merging more (usually 100+ pieces) Excel worksheets or workbooks into one single table, possibly in a short time.
One of the managers’ favorite habits is trying to over-persuade their employees to better performance by impossible deadlines, but that’s how the cookie crumbles.
Combine Multiple Workbooks – Folder location
If we don’t automate these procedures, we may struggle forever with this task. However, this Excel know-how can also come in handy for you anytime!
That’s why today’s lesson will be about combining multiple workbooks so that the result will be one Excel file.
In this case, the Excel-supported automated data processing add-in will help the quick execution of our processes. As usual, the add-in can be reached from the ribbon.
Our first task is determining the location of those 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, then at the combination of the files, not only the folder but its sub-folders will be considered by the program in 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
First, let’s talk about what kind of files the combination can be applied to. Currently the .xls, .xlsx, .xlsm and .xlsb files are supported by the program. We would also like to introduce the .xml and .csv types in the next version.
After choosing the file types, we can decide whether we interlink all the files or only just some of them. We can choose from three checkboxes.
The first option is to choose the files manually. It is best to select this option when we want to combine only a few Excel files.
As the second option, we can choose that the program processes all the files in the folder. Again, there is not any limitation. The add-in reads in 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*, then write in 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 go to the next step! We have determined the file types and the output folder in the first two steps.
The question is, 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.
Merge Multiple Workbooks – Additional Settings
Finally, determine in what range 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, not the whole content of the worksheet will be placed into the interlinked file but only the determined range.
Suppose there isn’t such limitation as choosing the second checkbox. As an effect, the VBA code will not consider the worksheet ranges but automatically works with all the information.
Suppose we would like 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.), then mark this checkbox.
Combine Multiple Workbooks – DataXL Add-in
If you experience that the graphic user interface (GUI) is often changing, that is no accident. However, we have already determined the final goal: build a complex Excel data processing add-in.
We apply developments continually. That’s why there may be smaller changes to the GUI (graphical user interface). But we guarantee that these changes only affect the user interface. The just now introduced functions will only expand.
Finally, let’s say a few words about compatibility! We have tested the Combine Multiple Workbooks add-ins in Excel 2007 – Excel 2016. Download free Excel add-ins and tools!