How to use Split Table for Excel
Using Split Table, you can bring a large worksheet to multiple sheets based on the values in the selected key columns. The tool works with sheets of any size, so you can divide your summary tables the way you need with minimum effort. Below you can learn how to use this add-in.
Before you start
If you have a standard Excel filter enabled in your table, the add-in will process only visible filtered rows. If you need to work with the entire table, turn the filter off before starting the add-in.
How to split a table into separate files
To divide your table into multiple separate tables, you need to go through 4 simple steps.
Start Split Table Wizard
Open the workbook and click the Split Table icon on the Ablebits Tools tab, in the Transform group:
Step 1: Select your table
The range with your data will be entered automatically but you can edit it right in the Select your table box:
In the first step, you also choose how to split the table:
- By key columns will let you select columns with key values according to which the table should be split.
-
Every N rows will simply cut the table by every number of rows you enter.
Note. If you select this option, upon clicking the Next button, you'll be brought right to Step 3, as Step 2 is about choosing the key column for the option above.
Click Next to continue.
Step 2: Select key column(s)
Key columns are the ones that contain values by which you want to group the resulting tables. You can select one or more columns for consideration; just tick the checkboxes next to the columns with the key values.
Besides, you can use the additional options:
- If you have a header row which you don’t want to split, tick the My table has 1 header row checkbox. If you have more than one header, click 1 header row and enter the number of labels in your table.
- Also, you can split a table by the number of characters in the key column. For example, if the key column in your table has the abbreviation of names of the days of week (Mon, Tue, Wed, Thu, Fri, Sat, Sun) and you split the table without indicating the number of characters, you'll get seven tables, one for each value. If you enter "1" into the Number of characters field, only the first characters of the values will be compared, and you'll get six tables, as Sat and Sun will be joined together in one table.
Tip. If you have a lot of columns in your table, take advantage of the Columns checkbox at the top of the list to instantly select or clear all the checkboxes.
Tip. If you need to get back to Step 1, click the Back button.
Click Next to proceed.
Step 3: Select destination
This step lets you choose where to place the split tables:
- Select the Current workbook radio button and the worksheets with the results will be created in the workbook that contains your main table.
- Pick the New workbook option to place the resulting tables in a new Excel file. The add-in suggests you save the new workbook to the same folder as your current book. To choose a different location, click on the icon with three dots in the Save to folder field and you will see the Save As window where you will be able to specify the path.
- Choose Multiple new workbooks to insert each created table in a separate Excel workbook. Use the Save to folder field to browse for the location where you'd like to save the new workbooks. Click on the icon with three dots and open the Browse For Folder window to find the needed place for your new files.
Click Next.
Step 4: Choose additional options
The last step allows you to choose how to name your new Excel sheets or files and specify if you want to preserve the original formatting and headers:
How to name new sheets
There are two ways the tool can name new sheets:
- Select Key values to name new worksheets after the values in the column(s) you selected in Step 2.
- Pick the Number radio button to use numbers as worksheet names.
You can also create your own names:
- Choose Before name and enter the text to prefix the name of each new sheet with.
- Check After name to add custom text at the end of all new Excel worksheet names.
Tip. You can use the Before name and After name options separately or in combination.
How to copy headers and preserve formatting
- To get the header row in all the split tables, tick Copy header and specify the row with labels in your Excel table. Use the Select range icon to collapse the window and select the range manually.
- To transfer the current formatting to your new tables, tick the Use the first row to preserve formatting checkbox.
Click Finish and let the tool process your data and create new tables:
Scenarios
How to save scenarios
If you want to make Split Table Wizard keep information about the table and options that you selected when going through its steps, click Save scenario in Step 4:
Then enter the name of your scenario and click OK:
How to run scenarios
To run any of the scenarios you saved, start Split Table Wizard, choose the scenario you need at the moment, make sure that the necessary table is selected, and click the Start button:
Note. For a saved scenario to work for the current worksheet, the structure of your current table must be the same as that of the table in the scenario.
Responses
Bikash Nangalia says:
June 9, 2022 at 8:14 am
Hi,
Every time I try to split table using the option "Multiple new workbooks", it shows an error that says "The Table hasn't been split" and I see only one excel file created in the folder I selected.
Can you someone help with a solution to this error?
Thanks,
Bikash
Hi Bikash,
Thank you for reporting the problem. For us to be able to understand the problem better, we need to know more details. Could you please send us a sample workbook with your data and screenshots of each step with the selected options to support@ablebits.com? Also, please attach a screenshot of the error message you get. Thank you.
Anna says:
March 25, 2022 at 12:42 am
Hi there,
Is it possible to split based on a particular column value? Ie. if countries were listed with multiple entries, we want the table to be split so each country would have it's own table?
Hi Anna,
Thank you for your question.
Yes, it is possible, please simply choose the Split by key columns option on step 1 and select the necessary column on the next step of the Wizard. If you have any difficulties, please email us at support@ablebits.com for further assistance.
Tina Meadors says:
February 2, 2022 at 2:31 am
I'm trying to use Split Table to separate data by a key column into multiple sheets numbered 1,2,3, etc. It works great EXCEPT that when the multiple numbered sheets are created, the sheets have been reordered alphabetically by the key column data.
So if in my key column I have (for example) "apple" in row 1, "pear" in row 2 and "banana" in row 3, my resulting split will have apple on sheet 1, banana on sheet 2 and pear on sheet 3.
It is crucial that my sheets keep the same order as my original data. I want apple on sheet 1, pear on sheet 2 and banana on sheet 3. Is there a way to turn off the alpha re-sorting?
Hello Tina,
Thank you for contacting us. Please install the latest version of Ultimate Suite compatible with you key using the link below and try to reproduce the issue:
https://www.ablebits.com/files/get.php?addin=xl-suite
If the problem persists, please send us a sample of your workbook to support@ablebits.com. Please also include the screenshots of the options you choose on each step of the add-on. Thank you.
Tina Meadors says:
February 10, 2022 at 5:07 pm
The issue does still persist in the latest version, but I found another Excel add-in that handles the issue properly. Thank you for your time.
Hans says:
November 16, 2021 at 7:21 am
How to split multi sheets by same name and merge to one file ?
Hi Hans,
Thank you for your question.
If I understand your task correctly, I'd recommend first combining your multiple sheets into one using our Copy Sheets or Combine Sheets tools, and then splitting the sheet with the combined data by a key column(s) with the help of the Split Table tool.
If this is not what you need, then please describe your task in more detail and send us a small sample workbook with your source sheets to support@ablebits.com. I kindly ask you to shorten the tables to 10-20 rows/columns. If you have confidential information there, you can replace it with some irrelevant data, just keep the format. Please also don't forget to include the sheet with the expected result. It often gives us a better understanding of the task rather than any text description.
We'll look into your task and try to help you further. Thank you.
Roey says:
October 11, 2021 at 6:12 am
Hi,
1. it is possible to choose which column to export?
2. If I have a table with hidden cells that I do not want to export, is it possible?
thanks
Hi Roey,
1. You can choose the key columns to split in step 2.
2. If you have a standard Excel filter enabled in your table, the add-in will process only visible filtered rows.
The add-in is a part of Ultimate Suite for Excel, feel free to install it in a trial mode using the link below:
https://www.ablebits.com/files/get.php?addin=xl-suite&f=free-trial
Roey says:
October 12, 2021 at 6:38 am
thanks!
Olof Franceschi says:
September 16, 2021 at 9:12 am
I don't get the option to split every x row in the first step! Actually nothing pops up there just the table information! the split works but due to the nature of my data I get a new sheet for every line in the table!
My tables are around 25-40 thousand lines and I want to split them into 1000 line pieces!
Any clues?
Hello Olof,
Thank you for your comment. The functionality you are interested in is available in the latest version of Ultimate Suite. You can download it from this page.
Please also feel free to email us at support@ablebits.com with your order ID or license key. We will check whether you are eligible for a free upgrade.
Rick Pengilly says:
April 20, 2021 at 8:54 pm
Is there a way to split table into separate files by row?
Hello Rick,
Thank you for contacting us. Feel free to send us a small sample workbook with your source data and the result you expect to get to support@ablebits.com. I kindly ask you to shorten the table to 10-20 rows. We'll look into your task and see if our software can help.
N says:
March 26, 2020 at 8:18 pm
Unfortunately, The "Preserve Formatting" feature is not working correctly, the entire divided worksheet is created in the format of the first row. is there a patch for this?
Hello Nora,
Thank you for contacting us. You see, it is the by-design behavior of this option when splitting tables. Unfortunately, Excel's api for cells formatting is very slow so keeping formatting for the whole table would be an extremely time-consuming operation. So, we decided to use the formatting of the first row.
Thank you for your understanding.
Andrew ofthesong says:
September 30, 2019 at 2:50 pm
Hi, great tool.
The "split table" could have copy the original workbook settings (page layout, margin, footer)
Thank you for your feedback, Andrew.
There is the "Preserve formatting" checkbox on the final step of Split Table that helps you save the original table's settings.
If you have any questions, do not hesitate to address them to support@ablebits.com. Thank you.
sultan says:
February 14, 2020 at 6:31 am
Preserve formatting didnt work for me as it supposed to
it uses 1st raw after the header format and apply it to the rest of rows
Katerina Bespalaya (Ablebits Team) says:
February 14, 2020 at 12:13 pm
Hello,
Thank you for your comment. The add-in works in this way by design. When you split the table and select the "Preserve formatting" option, the add-in splits your table into separate worksheets and picks up the formatting of the first row with values from your original table, the one after the header row, for all new sheets. I am afraid, we can hardly change this behavior because, otherwise, splitting a table will turn into a very cumbersome and time-consuming task that would take hours. Sorry for any inconvenience.
Nick says:
April 23, 2019 at 9:28 pm
Hello. Once the split is complete and new workbooks are created, is there a way to utilize Ablebits to attach these new files to e-mails. Example: I have 3 rows, Ablebits creates 3 new files, 1 for each row. Each row is for a different person (Jack, Carl, Jane). If I include the e-mail address in the original file for each person, can Ablebits attach each of these new files to a new e-mail for each person (Jack's file is attached to a file with Jack's email, etc.)?
Hi Nick,
Thank you for your question. Unfortunately, the tool can't do this, but the good news is that we included this idea to our plan and maybe we will implement it in our future releases. I wish I could assist you more.
Thank you.
Jaclyn says:
February 11, 2019 at 7:48 pm
Hi,
I am wondering if there a way to refresh the data once the split has been completed.
i.e. if more data is added to the original table is there a way to refresh the data so the newly added information is added to the respective separate sheet?
Thank You in advance.
Hi Jaclyn,
Thank you for the question. I am afraid there is no way to refresh your data in the split tables automatically when the split has been completed. Each time you add information to the original table you should perform the split again to keep data in split tables up to date.
Thank you.
Denis says:
January 30, 2019 at 3:43 pm
I join the proposal about the preservation of formulas.
When you select the option to save the formatting and table header, the formatting of the header is copied to all rows. Is it possible to split the copy format settings to
1) copy headers formatting
and
2) copy cells formatting ?
Thank you for your question, Denis.
I've forwarded your request to our QA assistants and developers and they will check if it's possible to introduce what you described.
As soon as there's any news, I'll reply to you by email.
Thank you for your understanding.
Augie says:
December 18, 2018 at 5:42 pm
Is there a way to preserve formulas within the split-table wizard? When exporting as multiple workbooks, it seems only the value of a cell is copied, not the underlying formula.
Hi, Augie,
Thank you for the question. Unfortunately, there is no such an option to preserve formulas when you split a table. However, I will discuss this idea with our developers and maybe this feature will be implemented in future releases.
Thank you.
Gabriel says:
November 18, 2019 at 1:34 pm
Agreed! Need to preserve formulas. And additionally would like to duplicate other sheets to the new workbooks.
Thank you for your comment, Gabriel.
I'm sorry, but it is still not possible to preserve formulas when splitting your tables. If there's any news on this matter, we'll email you right away.
As for duplicating the sheets, it won't happen automatically while splitting. However, there are a couple of quick solutions described on our blog:
How to copy and move sheets in Excel
Hope you'll find this information helpful.
Post a comment
Seen by everyone, do not publish license keys and sensitive personal info!
If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at support@ablebits.com.
