SplitTable command: automatically split an Excel table into multiple worksheets

XLTools Automation tool allows you to automate tasks in Excel by creating automation commands in a table, without having to create VBA macros.

This tutorial will take you through the setup of the SplitTable automation command. Use it when you regularly need to split a table into multiple worksheets by criteria.

For non-recurring tasks, use the standard Split Table tool on XLTools ribbon.

Before you begin, add the Automation tool to Excel

Automation is one of the 20+ features within XLTools Add-in for Excel. Works in Excel 2019, 2016, 2013, 2010, desktop Office 365.

Download XLTools for Excel

– free trial gives you 14 days of full access to all features.

Step-by-step example: how to use the SplitTable command

Say, you have an Excel table with invoices and their payment status. Every week or so, the file gets updated. Each time you need to split this large table into multiple worksheets to get separate tables for invoices paid and outstanding.

When you automate this process with the SplitTable command, you can split the table in a button click. Follow the steps below to execute the command yourself.

Feel free to download the sample file used in this tutorial.

Download sample file (xlsx)

1. Insert the SplitTable command

Let’s take a look at the Excel spreadsheet you want to split. In our example, the spreadsheet contains the worksheet "Invoices".
Add a new worksheet Name it, for example, “AUTO”. This worksheet will hold automation commands.
Tip: it’s best to keep automation commands on a separate worksheet, not to interfere with data.
Select a starting cell where to place the command, e.g. A1.
Open XLTools tab In the Automation group, open the drop-down list Select SplitTable.
See the table with the automation command is now added on the worksheet.

2. Specify parameters and define how to split a table

Once you’ve inserted the SplitTable command, specify its parameters. This automation table defines how to split the Excel table into multiple worksheets.

XLTools.SplitTable
Range:	Invoices!A:D
MyTablesHaveHeaders:	TRUE
SplitKeyColumn:	Payment Status
SheetName_Prefix:	Invoices-
SheetName_Variable:	KeyValue
SheetName_Postfix:
SequenceCount:
KeepWorkbookOpen:	FALSE
OutputTo:	NewSheet

What each line in this command reads:

Range: take the range A:D on the sheet Invoices. This is the source table that we’ll split.
MyTablesHaveHeaders: yes, TRUE, the source table has headers.
SplitKeyColumn: we will split this table by values in column "Payment Status".
SheetName_Prefix: let’s type "Invoices-", and this will be added as a prefix to the names of the resulting sheets.
SheetName_Variable: choose KeyValue, so that the resulting sheets are named by the key column value.
SheetName_Postfix: let’s leave blank, because we don’t want to add a postfix to the names of the resulting sheets.
SequenceCount:
KeepWorkbookOpen:
OutputTo: place the result on a new worksheet "Result" in the same workbook as the automation command.

See other available options to use in parameters.

3. Execute the command and review the resulting worksheets

Select the table of the automation command In the Automation group, click Execute Commands.
Review the resulting sheets:
- See that the large source table was split into two worksheets by the value in the "Payment Status" column
- The resulting worksheets are automatically named "Invoices-outstanding" and "Invoices-paid" – after the key column value ("paid" or "outstanding") with the prefix "Invoices-"

Done! Now you can assign a custom button to this command.

4. Assign a custom button to the command

If you need to run the command over and over again, add a custom button on the ribbon. So that next time you can execute the command in a click.

In the Automation group, click Add Custom Buttons.
Name the button, e.g. "Split by paid/due" Specify the range of the SplitTable command Click Save.
Done! The custom button will appear on XLTools tab. Now every time you click this button, you will execute the command.

Note: each time you execute the command, the newly generated worksheets will replace the previously generated worksheets. So before running the command next time, you can change the parameters to name the resulting worksheets differently.

Parameters and available options

When you create automation commands in Excel, this file with commands must be located in the same destination as the source data:

Add automation commands in the same workbook as your source data. We recommend that you place the commands on a separate worksheet not to interfere with your data.
Or, add automation commands in a separate workbook. Save this automation file in the same folder as the source data.

Below are all parameters and available attributes for the SplitTable command.

Tip: select a cell and see a helpful tooltip. Some attributes also have a drop-down list of available options to choose from.

Range:

This parameter specifies which data range should be processed.

Required parameter, cannot be blank
If you refer to a source workbook, it can be open or closed
The source workbook must be located in the same destination as the automation file

Options	Meaning
Sheet1!A1:B2	Range A1:B2 on Sheet1 in the active workbook
Sheet1!A:C	Entire A, B, C columns on Sheet1 in the active workbook
Sheet1!1:3	Entire 1, 2, 3 rows on Sheet1 in the active workbook
A1:B2	Range A1:B2 on the current worksheet
Sheet1!	Entire Sheet1 in the active workbook
Sheet1!Table1	Table1 on Sheet1 in the active workbook, without table headers
Sheet1!Table1[Column1]	Column1 in Table1 on Sheet1 in the active workbook, without table headers
Sheet1!Table1[[Column1]:[Column3]]	Columns 1, 2, 3 in Table1 on Sheet1 in the active workbook, without table headers
Sheet1!Table1[#All]	Table1 on Sheet1 in the active workbook, with table headers
Sheet1!Table1[[#All],[Column1]]	Column1 in Table1 on Sheet1 in the active workbook, with table headers
Sheet1!Table1[[#All],[Column1]:[Column3]]	Columns 1, 2, 3 in Table1 on Sheet1 in the active workbook, with table headers
[Book.xlsx]Sheet1!A1:B2 '[Book One.xlsx]Sheet1'!A1:B2	Range A1:B2 on Sheet1 in the workbook Book. Book.xlsx must be located in the same destination as the automation file.
'C:\Documents\[Book.xlsx]Sheet1'!A1:B2	Range A1:B2 on Sheet1 in the workbook Book. Book.xlsx must be located in the specified file path location.

Use standard rules of range referencing in Excel, for example:

E.g. if names of worksheets or workbooks contain spaces, enclose the name in single quotes, e.g.'Orders April'!A1:H10, or '[All Orders]Orders April'!A1:H10.

Note: in order for automation commands to identify references, do not use spaces in the names of worksheets, workbooks, or tables.

MyTablesHaveHeaders:

This parameter specifies whether the source table has headers.

Required parameter, cannot be blank

Options	Meaning
TRUE	Yes, the source table has headers. The headers will be repeated on each of the resulting worksheets.
FALSE	No, the source table has no headers
(blank)	Same as FALSE

SplitKeyColumn:

This parameter specifies the key column - the source table will be split by values in this key column.

Required parameter, cannot be blank

Options	Meaning
Column2	Split source table by values in the key Column2

SheetName_Prefix:

This parameter defines how to name the resulting worksheets. With this parameter you can add a prefix to the name of each resulting worksheet.

Optional parameter, you can leave it blank or remove from the command altogether
The length of a text string in a cell cannot exceed 10 characters

Options	Meaning
Result-	Add a prefix "Result-" to the names of the resulting worksheets
(blank)	Do not add any prefix

SheetName_Variable:

This parameter defines how to name the resulting worksheets.

Required parameter, cannot be blank

Options	Meaning
KeyValue	Name the resulting sheets after the values in key column
Sequence	Name the resulting sheets as a numerical sequence (1, 2, 3, etc.)

Example: this table is split by values in the column "Payment Status". The resulting worksheets are named after KeyValue: "paid" and "outstanding":

Example: this table is split by values in the column "Payment Status". The resulting worksheets are named as a Sequence: 1, 2.

SheetName_Postfix:

This parameter defines how to name the resulting worksheets. With this parameter you can add a postfix to the name of each resulting worksheet.

Optional parameter, you can leave it blank or remove from the command altogether
The length of a text string in a cell cannot exceed 10 characters

Options	Meaning
-result	Add a postfix "-result" to the names of the resulting worksheets
Sequence	Do not add any postfix

SequenceCount:

This parameter specifies

Required parameter, cannot be blank
Use a value between 1 and 100 000

Options	Meaning
100	Split the table by 100 rows per worksheet.

KeepWorkbookOpen:

This parameter defines whether to keep the resulting workbook open after the operation. Useful when you have a sequence of automation commands.

Optional parameter, you can leave it blank or remove from the command altogether

Options	Meaning
TRUE	Keeps the workbook open.
FALSE	Does not keep the workbook open.
(blank)	Same as FALSE

OutputTo:

This parameter specifies where to place the resulting worksheets.

Required parameter, cannot be blank

Options	Meaning
NewSheet	Place the result on new worksheets in the active workbook
NewWorkbook	Place the result into a new workbook. A new default workbook Book1 will open after the command is executed and you can save it to your computer.
NewWorkbook[Result]	Place the result into a new workbook named Result. The workbook will be saved in the same location as the automation file.

Explore other automation commands

Any questions or suggestions?

How can we help?