Easily break down a table or a range into multiple worksheets

When you want to share only relevant data from your master worksheet, or harmlessly break down a large table to fit your email size, then you might need to split your data. E.g. split a sales report into sub-reports by product category. Or, split a long listing into smaller sub-listings by a fixed number of rows. Instead of painstaking manual sorting, copying and formatting, you can save time with XLTools add-in.

The Split Table add-in automatically splits data in one worksheet into multiple new worksheets:

Split Table 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

You can split an entire table or a range based on values in one key column. This way, data related to each unique value in the key column are placed on a separate worksheet.

1. Click the Split Table button on XLTools ribbon A dialogue box will appear.
2. Select a table or a range you plan to split, including table header.

   

   Tip: click any cell in a table, and the entire table will be automatically selected.
3. Check My table has headers if it does.

   • If your table has headers, the header will be repeated in the output tables.

     

     Note: for best results, make sure that there are no blank cells in the table header.
   • If your table does not have headers, the output tables will not have headers either.
4. Select Values in this column as the splitting method Find and select the key column from the drop-down list:

   • If your table has headers, find the column by its header label.
   • If your table does not have headers, find the column by its generic alphabetic label (A, B, C, etc.)
5. Define how to name the resulting worksheets:

   • Select Column value to assign key values as tab names.

     

     Note: if some cells in your key column are blank, please fill blanks or use another naming method.
   • Or, select Number series to assign sequential numbers (1, 2, 3…) as tab names.
   • If necessary, type a prefix or a suffix. They will be repeated in all tab names.

     

     Tip: we recommend adding a descriptive prefix or a suffix – it will be easier to identify and navigate sheets later on.
6. Click OK Done. Processing large tables may take a few seconds.

Review results: new worksheets are ordered right after the source worksheet. Each tab contains a table with data related only to one unique key value. The source worksheet remains intact.

You can split a table or a range based on a required number of rows per sheet, e.g. split data after every 5 rows. This way, every next 5 rows are placed on a separate worksheet.

1. Click the Split Table button on XLTools ribbon A dialogue box will appear.
2. Select a table or a range you plan to split, including table header.

   

   Tip: click any cell in a table, and the entire table will be automatically selected.
3. Check My table has headers if it does.

   • If your table has headers, the header will be repeated in the output tables.
   • If your table does not have headers, the output tables will not have headers either.
4. Select Number of rows as the splitting method Set the fixed number of rows to split the table by.
5. Choose how to name the resulting worksheets:

   • Select Number series to assign sequential numbers (1, 2, 3…) as tab names.
   • If necessary, type a prefix or a suffix. They will be repeated in all tab names.

     

     Tip: we recommend adding a descriptive prefix or a suffix – it will be easier to identify and navigate sheets later on.
6. Click OK Done. Processing large tables may take a few seconds.

Review results: new worksheets are ordered right after the source worksheet. Each tab contains a table with a fixed number of rows. The source worksheet remains intact.

Splitting a table or a range into multiple worksheets means that data are essentially extracted and copied from a source worksheet into new worksheets in the workbook.

• Formulas and cell references:

  To prevent data corruption, instead of cell references, functions or formulas from the source worksheet, XLTools Split Table inserts their values into the resulting worksheets.

• Formatting:

  The Split Table add-in keeps cell and table formatting as they are in the source worksheet. This includes cell formatting (number, date, text, etc.), column width, row height, fill color, etc. However, if your source table has table style applied, the resulting tables are inserted as ranges.

• Merged cells:

  If you source table has merged cells, they are automatically unmerged in the resulting tables and respective values are duplicated.

After splitting your table or a range into multiple worksheets, you can easily save these sheets as separate files with XLTools Workbook Organizer. It helps save worksheets as separate files, copy sheets into a new workbook, and otherwise manage multiple worksheets at at a time.