`
Join In
Useful Tools
Split worksheet
Tools       2020/6/9 9:51:13          Author: FFCell
Views: 0

If we want to split data from multiple worksheets into multiple worksheets/workbooks based on key fields or fixed rows, it is the right choice to use this feature: Split worksheet.


For example, we want to split data based on Fruit from all worksheets of below two workbooks into multiple worksheets.


We choose the feature Split worksheet in Split group, and then click Start button (upper right corner).


Step 1: Select excel files

We drag those two excel files and drop them to the blank area, and then click Next button.


Step 2: Select worksheets

All sheets means all sheets in the selected excel files will partcipate in split.

If we only want the first sheet in selected excel file to participate, we can choose the next option and set it as :

Sheet 1 to 1 from left to right


If we want the specific sheets in the selected excel files to participate in split, for example the sheet Februray, pls choose this option:Specific sheets (MultiSelect)

And we will get all sheets' names from the first excel file by default, and then tick the specific sheet (sheet Februrary) you want.


If the first excel file does not include sheet February, we can click Get All , and then we will get all sheets' names from all selected excel files. But it will take a long time.

It is better for us to click the Input button and then input the sheet name manually.

If we don't want the hidden sheets or blank sheets to participate in the split, pls tick Skip hidden sheets and Skip blank sheets. In Step 2, we decide to choose All sheets, and then click Next button.


Step 3: Split options


In this step, we have 3 parts to set.

In the part of Split range, we need to set the table header and table footer. We can find that table header occupies 2 rows and table footer occupies 1 row in the selected worksheets.Thus we should set this part as Whole sheet: table header 2 rows, table footer 1 rows.



In the part of Split method, we should know that we want to split data based on Fruit. So we will choose Based on key column A (Fruit locates in column A).

If we want to split data based on Fruit and Rank, we need to click More key field, and then choose Based on key column A and C (Fruit locates in column A, Rank locates in column C)


If we want to split data every 5 rows, we can choose Based on fixed rows: 5.


Preserve formatting will copy the formats to the result worksheet.

Preserve formulas will copy the formulas to the result worksheet.

Preserve comments will copy the comments to the result worksheet.


In the part of Save, we need to set the New sheet name. We can use key field or subtable + auto-increment number to name the new sheet. The words subtable can be revised manually by yourself.

If we want to split data into multiple worksheets, we should choose Save split ranges in One workbook (One workbook means that you will get one workbook which contains multiple worksheets).

If we want to split data into multiple workbooks, we should choose Save split ranges in Individual workbooks (each split range occupies one worksheet in one individual workbook).


Step 4: Ready to split


Click Start button, some seconds later, split completed.


We finally get a workbook which contains multiple worksheets. Those worksheets were named as key field. Now let's open and take a look.