Split Table Wizard for Microsoft Excel

How to split a table into separate files by key column values

Split Excel table into multiple worksheets

Using Split Table Wizard, you can bring a large worksheet to multiple sheets based on the values in the selected key columns.

Free Download
7-day fully functional version

Select the table

  1. Open the Excel workbook with the table you need to split. Then open Split Table Wizard by clicking on its icon. Split Table Wizard icon
  2. You can select your table by highlighting it, by clicking the Select range icon or by typing its address in the Select your table field manually. Select your table by clicking the select range icon
    Tip. Before you start Split Table Wizard, select a cell in your table and the whole table will be selected automatically.
    Note. If you have standard Excel auto filter option enabled, the add-in will match only the visible filtered rows. If you need to work with the entire table, turn the filter off before you run the add-in. If you want to work with some part of the table only, apply auto filter to it and then only the visible rows of your table will be processed and combined.
  3. Click the Next button on the Split Table Wizard window to continue.

Select key column(s)

Key columns are the columns that contain values by which you want to group the split tables. You can select one or more key columns.

  1. Tick the checkboxes next to the columns with the key values. Tick the checkboxes next to the columns with the key values
    Tip. If you have a lot of columns in your table, take advantage of the Select All checkbox to instantly tick or untick all the boxes in the list of columns. Take advantage of the Select All and Unselect All buttons
  2. The 1st row content column next to Columns shows the 1st value of each column in your table. It can be useful if your worksheet doesn't have headers so you can see what kind of information the columns contain. See what kind of information is in the columns
  3. You can also indicate if there are header rows in your table. As a rule, the add-in indicates headers automatically. If it doesn't, you can check My table has headers. Indicate if there are header rows in your table
  4. Click on the Next button on the Split Table Wizard window to proceed. If you need to get back to step 1, press the Back button on the dialog box.

Select destination

On the Select destination step you can choose where to put the split tables and how to name them.

  1. Specify where you want to place the resulting tables.
    • Select the Current workbook radio button if you need to insert the table parts into spreadsheets in the same workbook where you have the main table. Select the Current workbook radio button
    • Choose the New workbook option to place the split tables to another workbook.
    • Select Multiple new workbooks to insert each new table in a separate workbook.
  2. Define where on your computer you want to save the files with the split tables.
    • When you select the New workbook option on Step 3 of the wizard you can choose to save this workbook as a file. To do this, go to the Save as file field and click on the icon with three dots. Then you will see the Save As window and select the necessary path.
    • If you pick Multiple new workbooks as destination, choose a folder on your PC for saving these new workbooks. Go to Save to folder and click on the icon with three dots next to the field with the path.

Choose additional options

  1. It is possible to name the new sheets by key values from the column(s) you select on step 2 or by number.
    • Select the Key values radio button to name new worksheets after the values in the selected column(s). Name new worksheets after the values in the selected column(s) See spreadsheets named after the key values: See worksheets named after the values in the selected column(s)
    • Select the Number radio button to use numbers as spreadsheet names. Number new spreadsheets
  2. You can add any text to the beginning or to the end of the new worksheet names. It can be really helpful if you need to split your table to numerous new tables and need to easily find them in a folder or within the existing workbook.
    • Tick the Before name checkbox and enter the necessary text in the field next to it. Tick the Before name checkbox This text will appear at the beginning of each new sheet name.
    • Check After name to add custom text at the end of all new worksheet names. Check the After name option You can use the Before name and After name options separately or in combination. Get your text at the beginning and/or at the end of each new sheet name
  3. If you want to have the header row in all the split tables, check the Copy header option. Check the Copy header option You can select any row in your main table as the header row if you click on the Select range icon next to the Copy header field.
  4. To transfer the current formatting to your new tables, select the Preserve formatting radio button. Check the Preserve formatting option
  5. Click on the Finish button and wait for a couple of seconds until your data are processed.

Featured customers

Ablebits.com featured customers
Contact us
  Publish your message on our support forum

Our working hours: 0am to 10am PST; 3am to 1pm EST; 8am to 6pm CET.

Ultimate Suite for Excel Professionals
60+ professional tools for Excel 2016-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Sheila Blanchard
Excel add-ins and Outlook tools - Ablebits.com