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

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 theSplit Tablebutton 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. CheckMy table has headersif 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. SelectValues in this columnas 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:

    • SelectColumn valueto 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, selectNumber seriesto 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.

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 theSplit Tablebutton 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. CheckMy table has headersif 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. SelectNumber of rowsas the splitting method Set the fixed number of rows to split the table by.
  5. Choose how to name the resulting worksheets:

    • SelectNumber seriesto 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.

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 to 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 XLToolsWorkbook 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?
robert huffHi, I may have overlooked the resources, but I would find it helpful if sample Excel files are also provided to illustrate each of XL Tools Pro features. Thank you. Bob
April 6, 2020 at 21:58
Maria BalobanovaHi Bob, There are no sample files with examples, but you can find step-by-step guides to each feature - they are all listed on thehome page.
April 7, 2020 at 11:46
How can we help?