Split Table add-in: 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:

  • Quickly break down a table or a range to individual worksheets
  • Select a splitting method: by column values or by number of rows
  • Choose from several options to name the resulting worksheets
  • Maintain table headers and formatting in the resulting tables
  • Automatically unmerge cells and duplicate values

 

Add "Split Table" to Excel 2016, 2013, 2010, 2007

Works in: Microsoft Excel 2016 – 2007, desktop Office 365 (both 32 bit and 64 bit).

Download XLTools Add-in

Split Excel table ribbon button

 

Learn your way around:

 

How to split a table into multiple worksheets based on column values

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.

Split table into worksheets by column value

 

How to split a table into multiple worksheets based on number of rows

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.

Split table into worksheets by number of rows

 

How data are copied into worksheets

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.

 

How save the resulting worksheets as separate files

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.

 

Any questions or suggestions? Please leave your comment below.

 

Any questions or suggestions? Please leave your comment below.

Leave a Reply

Your email address will not be published. Required fields are marked *