The Combine Worksheets wizard joins multiple spreadsheets into a single large table and places records according to their headers. It doesn't matter whether the tables structured identically or differently. You can pick out the columns you want to merge if needed, so that unnecessary data are not copied to a new file.
Since the add-in processes table columns, all sheets you want to join must have headers.
If there are filtered, grouped, or hidden cells in your tables, they will be ignored.
To run the add-in, click the Combine Sheets icon on the Ablebits Data tab in the Merge group:
Here you can see all the files that are currently open in Excel. You can pick out the worksheets you would like to join by ticking off the checkboxes next to their names:
The add-in automatically pulls all data from the chosen worksheets. To change the highlighted area, select the sheet name in the list and click on the Select range icon under the Ranges column. It will open this sheet, and you will see a dialog box that allows you to pick the necessary cells:
The buttons at the bottom of the wizard's window will also help you deal with a big number of files:
Click Next to go to Step 2.
The columns you choose on this step will be joined together based on their headers and placed to a new location.
Here you can see a list of column headers from all the selected worksheets and the total number of times a given label occurs in the tables. E.g. if you choose 5 files and each has a column named "Fruit", you will see 5 in the Number of occurrences field for this column.
To exclude some records from the results, unselect the column in the list.
If you want to combine data from all columns, keep all the checkboxes ticked off.
Click Next to continue.
Or click Back to return to a previous step and delete, add, or pick out other worksheets.
This step lets you define the way to copy and paste records to the merged columns:
Press Next to proceed to Step 4.
Select the destination for the combined data from one of the following options:
Click Combine and get the data from the same columns merged into one Excel table.
I have a large amount of worksheets to combine. When I use the software it prematurely opens the combined results, which only contains about half of the sheets.
Hi Kevin,
Thank you for your comment.
The problem may be in Excel limitations.
The questions are: how many books you are trying to combine? And how many rows and columns does each book have? And where do you pick to place the results: to a new worksheet or to a new book?
Our products do not impose any additional limitations, so the number of rows and columns in the resulting table is defined by the version of Excel you have, please find them here.
If this is not the case, please contact us at support@ablebits.com and we will help you asap.
Hi there! This is going to save me so much work. Thank you.
Is there a way to preserve formatting of the destination sheet like borders and fixed columns?
Many thanks
ps referring to the "Combine sheets"
Hi Diana!
Thank you so much for your kind feedback, we appreciate it a lot.
Unfortunately, the add-in does not preserve formatting. The thing is that this feature would significantly slow down the combing process, so we’ve chosen speed instead of formatting. However, we are trying to find a way to implement it without losses in the add-in’s performance. So we are working on it 🙂
Thank you.
Hi,
I combined a bunch of sheets but the total number of rows is not as many as expected. I think there may be a command to ignore duplicates? How do I make sure that the duplicates are included as they are the same but are not duplicates.
Thanks
Hi Ben,
Thank you for contacting us.
Since our Combine Worksheets preserves duplicates while joining your sheets, we need to know some more details to understand the problem you faced better. Please check your Inbox and provide us with the requested information.
Thank you.
