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
Free 15-day trial version, 14.73 MB

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 forum

Please remember that we work from 8am to 4pm GMT, this is 4:00am to 12:00pm EST,
1:00am to 9:00am PST, and our public holidays may differ from yours.

Collection for Outlook
Automatically CC or BCC recipients
Never forget to copy your colleagues on some or all outgoing messages
Reply with email templates
Create your collection of email templates to avoid typing the same text again and again
Check emails before sending
Configure your rules to prevent "Oh, no!" after sending an email
Never forget to hit Reply All
Always remember to select Reply All on a multi-recipient message
Prevent unintended replies to all
Get an alert when you're about to reply to all recipients of an email
Never miss really important emails
See an immediate notification when an urgent message arrives at your in-box
Analyze email headers
Get a really quick access to email headers to fix some common mail problems
Optimize your email timing
See sender's time zone to send timely emails when you correspond with people overseas
Add-ins Collection for Outlook
Boost your in-box productivity!
See more