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.

Step 1: Select the table

Open Excel workbook with your table and click the Split Table icon in the Transform group under Ablebits Tools tab to run the wizard.

Split Table Wizard icon

The range with your data will be entered automatically, but you can edit it right in the Select your table field, use an icon you’ll see there, or select the table in your spreadsheet.

Select your table by clicking the select range icon
Note. If you have a standard Excel filter enabled, the add-in will process only the visible filtered rows. If you need to work with the entire table, turn the filter off before you start the add-in.

Click Next to continue.

Step 2: Select key column(s)

Key columns are the ones that contain values by which you want to group the resulting tables. You can select one or more columns for consideration; just tick the checkboxes next to the columns with the key values.

Check My table has headers to indicate if the first row in your table contains labels. If it doesn’t, look at the values in the 1st row content column to pick the right records.

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 checkbox at the top of the list to instantly tick or untick all the boxes.

Click Next to proceed. If you need to get back to step 1, press the Back button in the add-in window.

Step 3: Select destination

This step lets you choose where to place the split tables:

Select the Current workbook radio button
  • Select the Current workbook radio button if you would like to insert the sheets with the grouped records in the same workbook where you have the main table.
  • Pick the New workbook option to place the resulting tables to a new Excel file and define where you’d like to save it on your computer using the Save as file field. Click on the icon with three dots and you will see the Save As window where you can specify the path.
  • Choose Multiple new workbooks to insert each created table in a separate Excel spreadsheet. Use the Save to folder field to browse for the location where you’d like to save new workbooks.

Step 4: Choose additional options

The last step allows you to choose how to name the created Excel sheets or files and specify if you want to preserve original formatting and headers.

Name new worksheets after the values in the selected column(s)

There are two ways the tool can name new sheets:

  • Select the Key values radio button to name new worksheets after the values in the column(s) you select on step 2.
  • Pick the Number radio button to use numbers as spreadsheet names.

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 sheets, it will help you easily find them in a folder or within the existing workbook.

  • Tick the Before name checkbox and enter the text you want to appear at the beginning of each new sheet name.
  • Check After name to add custom text at the end of all new Excel worksheet names.

You can use the Before name and After name options separately or in combination.

  • To get the header row in all the split tables, select the Copy header option and specify the row with labels in your Excel table.
  • To transfer the current formatting to your new tables, select the Preserve formatting checkbox.

Click Finish and give the tool a couple of seconds to process your data and create new tables.

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