How to use Split Workbook Wizard for Excel

Split Workbook Wizard lets you quickly copy all or selected worksheets to new Excel workbooks. You can select the worksheets which you want to place in the resulting workbooks, move every specified number of sheets to a separate file, or group worksheets in new workbooks by substrings.

Before you start

If the worksheet you're going to place in a new workbook has references to other worksheets, make sure that the new workbook contains the worksheets with the source data.

Macros are not copied to new workbooks.

How to split a workbook in Excel

Here is how you can use the three-step Split Workbook Wizard to copy worksheets to new workbooks.

Start Split Workbook Wizard

Open the workbook which you want to split. On the Ablebits Tools tab, in the Transform group, click Split Workbook:

Start Split Workbook Wizard.

The Split Workbook Wizard allows you to:

  • Manually select the worksheets you want to turn into separate workbooks.
  • Group worksheets in new workbooks by substrings in their names.
  • Copy every specified number of sheets to a new workbook.

Place each selected sheet in a separate workbook

Step 1: Specify how to split your workbook

If you want to place only specific sheets in separate workbooks, select the first option—Place each selected sheet in a separate workbook:
Place each selected sheet in a separate workbook.

Click Next to proceed to the next step.

Step 2: Choose sheets to be placed in separate files

In the list of worksheets, select the ones that will be placed in new workbooks.
Choose sheets to place in separate files.

Tip. Click the New workbooks checkbox to select or deselect all worksheets in the list.

If you have an extensive list of worksheets, take advantage of the Filter field. Enter the characters that should appear in a sheet name, and the worksheets whose names do not contain those characters will be filtered out.

Filter worksheets.

Click Next to continue.

Step 3: Choose how to save the new workbooks

To select the location for the newly created workbooks, click Browse, and then select the destination folder for the new files in the Browse For Folder dialog window.

You will see the path in the Save the new workbooks to field.
Choose where to save the resulting workbooks.

Note. The new workbook will have the same name as the worksheet copied to it.

In case a workbook with the same name already exists in the destination folder or is currently open in Excel, the add-in can add a timestamp to the name of the new workbook. To have this done automatically, keep the corresponding checkbox below the Save the new workbooks to field selected.

With this option checked, your new workbook will be named like this:
Workbook's name with a timestamp

If you clear this checkbox and a workbook with the same name is currently open, you’ll be prompted to either Auto-rename the new workbook with a timestamp or Skip, in which case the new workbook will not be created.
Workbook with the same name is now open.

If this checkbox is cleared and a workbook with the same name already exists in the destination folder, you will be provided with the following choices:

  • Auto-rename—a timestamp will be added to the name of the new workbook.
  • Overwrite—the existing workbook will be deleted and a new one with the same name will be created.
  • Skip—the new workbook will not be created.

Workbook with the same name already exists.

Tip. To apply your choice to all other new workbooks with the same names, select the Do the same with the other workbooks checkbox.

Click Split to have the new workbooks created.
Sheets are copied to new workbooks.

Group worksheets by substrings

Step 1: Specify how to split your workbook

If you want to group worksheets in new workbooks according to certain text/characters included in their names, select the second option—Group worksheets by substrings.
Group worksheets by substrings in their names.

Click Next.

Step 2: Specify substrings to group your worksheets by

  • In the Enter substrings field, type the substring(s) by which the worksheets will be grouped in new workbooks.
    Enter substrings to group worksheets by.

    Note. To have several groups of worksheets placed in different workbooks, type each substring in a new line.
    Note. The substrings you enter will be used as the names for the new workbooks.
  • In the Result preview field, you can view the resulting workbook(s) and the worksheet(s) they will contain.
    Clear the checkboxes next to the names of the worksheets which you don’t want to be copied to new workbooks.

    Tip. Click New workbooks to select or deselect all.

Click Next to continue.

Step 3: Choose how to save the new workbooks

Choose the location for the newly created workbooks.

Click Browse and, in the Browse For Folder dialog window that will pop up, select the destination folder for the new files. The path will appear in the Save the new workbooks to field.

Choose where to save the resulting workbooks.

Note. The new workbooks will be named by the substrings you enter in Step 2.

In case a workbook with the same name already exists in the destination folder or is currently open in Excel, the add-in can add a timestamp to the name of the new workbook. To have timestamps added automatically, select the corresponding checkbox below the Save the new workbooks to field.

If this checkbox is cleared, you'll be offered to either Auto-rename the new workbook using a timestamp or Skip, in which case the new workbook will not be created. For full details, please see Step 3 of the previous section.

Click Split to have the new workbooks created.
Sheets are grouped in new workbooks.

Split the workbook by every N sheets

Step 1: Specify how to split your workbook

If you aim to place every specified number of sheets in a new workbook, select the third option—Split the workbook by every N sheets.
Split by a specified number of sheets.

Click Next.

Step 2: Decide how many worksheets to bring to new workbooks

  • In the Split by every N sheets field, type the number of worksheets to place in every new workbook (or use the up and down arrows to set the number).
    Type how many sheets to place in every new file.
  • In the list of new workbooks, you can view the resulting workbooks with the worksheets they will contain. Clear the checkboxes next to the names of the worksheets which you don’t want to be placed in new workbooks.

    Tip. Click New workbooks to select or deselect all.

Click Next.

Step 3: Choose how to save new workbooks

Choose the location for the new workbooks.

Click Browse and in the Browse For Folder dialog window that opens, select the destination folder for the new files. You will see the path in the Save the new workbooks to field.

Choose where to save the resulting workbooks.

Note. The new workbooks will be named as Book1.xlsx, Book2.xlsx, etc.

In case a workbook with the same name already exists in the destination folder or is currently open in Excel, the add-in can add a timestamp to the name of the new workbook.

If you want timestamps to be added to the names of the workbooks automatically when the workbook with the same name already exists in the destination folder or is open, select the checkbox below the Save the new workbooks to field.

If this checkbox is cleared, you'll be offered to either Auto-rename the new workbook using a timestamp or Skip, in which case the new workbook will not be created. For full details, please see Step 3 f the Place each selected sheet in a separate workbook section.

Click Split.