How to: Split data table into multiple worksheets (and save time)
Many people have asked me the past years if ASAP Utilities has an option to split a table into several sheets. Either per so many rows, or by value. Until recently that wasn't possible, but now it is!. In cooperation with several users we have created a new utility.
In the new version of ASAP Utilities you will be able to quickly export your data in parts to new worksheets.
You can for example export the information of each car brand to a new worksheet. Whenever the tool encounters a new brand in column A, a new sheet is created and the information of that brand will be copied there:
With this tool you can quickly split your data into multiple parts on different sheets. You can do this either by value, for example to create a new sheet for each company, or you can specify the number of rows to create per sheet.
We have also received the request for this tool from people that used workbooks with over 500 000 rows (Excel 2007) and then needed to open the information in Excel 2003 or earlier. By default when you save the file in Excel 2007 in Excel 2003 format, you will then get a warning such as the following and you will lose data:
In order to save the file with 500 000 rows you had to split the information into several sheets with a maximum of 65536 rows because that is the maximum amount of rows that a sheet in Excel 2003 can contain.
Now, with this tool you can do that much faster.
Download and use
You can read more information and download the pre-release of the new version from:
http://www.asap-utilities.com/faq-questions-answers-detail.php?m=220
You can start this new (experimental and only in English) tool via:
ASAP Utilities Options » New tools in development (experimental, English only)
and then click on the button "Split data table into multiple worksheets".
Export the sheets as separate workbooks.
If you need to save each worksheet as an individual file, then you can use the following tool to do this:
ASAP Utilities » Export » Export worksheets as separate files...
Combined with the new tool mentioned above this is a powerful and time-saving combination.
Filed under: ASAP Utilities on February 11th, 2010 by Bastien | 5 Comments »
Hi Bastien,
May the Couple Live 1000 Years !
This was something I was waiting for years ! Thank you So Much. I will be using this feature in a very near future and surely will get back to you with my feedback.
Sincere Regards
Kanwaljit Singh Dhunna
This is an amazing tool and will help me save so much time!
One problem though, the format of new worksheets is wrong, for some reason this has increased the row height 10x or more meaning I have to go through and correct it manually. Is this a glitch that will be corrected?
cheers
Hello Andrew,
Thank you for your feedback and compliment.
Unfortunately I cannot reproduce the problem with the row height.
What happens when you add an empty worksheet into your workbook (Excel menu > Insert > Worksheet)?
Does that also have the larger row height? Can you perhaps send me an example workbook that I can use to reproduce the problem with?
Kind regards,
Bastien Mensink
I cannot access the split feature in trail download, 4.6.0. Please advise.
Hello Dixi,
Thank you for your feedback.
In the newer version we have changed the layout or this utility a bit, but it is still there.
To start this utility, choose in the Excel menu:
ASAP Utilities » ASAP Utilities Options » New tools in development (English only)
Then in the dropdown select “Split data table into multiple worksheets” and press the OK button.
More information:
http://www.asap-utilities.com/asap-utilities-excel-tools-tip.php?tip=262&utilities=224&lang=en_us
In response to Andrews comment. We have had contact by email and that problem is solved in version 4.6.0
http://www.asap-utilities.com/faq-questions-answers-detail.php?m=228
Kind regards,
Bastien Mensink