Split Table Wizard for Microsoft Excel
How to split a table into separate files by key column values
Using Split Table Wizard, you can bring a large worksheet to multiple sheets based on the values in the selected key columns.
- Open the Excel workbook with the table you need to split. Then open Split Table Wizard by clicking on its icon.
- 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.
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.
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.
- 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.
- 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.
- 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.
- 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.
On the Select destination step you can choose where to put the split tables and how to name them.
- 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.
- 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.
- 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.
- 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). See spreadsheets named after the key values:
- Select 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 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. 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. You can use the Before name and After name options separately or in combination.
- If you want to have the header row in all the split tables, 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.
- To transfer the current formatting to your new tables, select the Preserve formatting radio button.
- Click on the Finish button and wait for a couple of seconds until your data are processed.