Split Table Wizard for Microsoft Excel

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

Split Excel table into multiple worksheets
Free Download
Free 15-day trial version, 11/6/2013

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. When working with the add-in, on the left you can see the step you are on. See the step you are on when working with the add-in
  3. You can select your table 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.

    Click the Next button on the Split Table Wizard window to continue. Click the Next button 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 and Unselect All buttons to instantly tick or untick all the checkboxes 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. Press the Back button to get back to step 1

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. Place the new spreadsheets to another workbook
    • Select Multiple new workbooks to insert each new table in a separate workbook. Have each new worksheet 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. Save as file option
    • 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 a folder on your PC for saving new workbooks
  3. If you want to have the header row in all the split tables, check the Copy header option. Copy the header row to all the split tables 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. 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 column 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
  5. 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
  6. Click on the Finish button and wait for a couple of seconds until your data are processed. Click on the Finish button

Featured customers

Ablebits.com featured customers
 
Contact us
 
  Publish this message on the 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.