How To Use Split Table Wizard For Excel - Ablebits

How to use Split Table for Excel

Using Split Table, you can bring a large worksheet to multiple sheets based on the values in the selected key columns. The tool works with sheets of any size, so you can divide your summary tables the way you need with minimum effort. Below you can learn how to use this add-in.

• Before you start
• How to split a table into separate files
  • Start Split Table Wizard
  • Step 1: Select your table
  • Step 2: Select key column(s)
  • Step 3: Select destination
  • Step 4: Choose additional options
• Scenarios
• Related links

Before you start

If you have a standard Excel filter enabled in your table, the add-in will process only visible filtered rows. If you need to work with the entire table, turn the filter off before starting the add-in.

How to split a table into separate files

To divide your table into multiple separate tables, you need to go through 4 simple steps.

Start Split Table Wizard

Open the workbook and click the Split Table icon on the Ablebits Tools tab, in the Transform group:

Step 1: Select your table

The range with your data will be entered automatically but you can edit it right in the Select your table box:

In the first step, you also choose how to split the table:

• By key columns will let you select columns with key values according to which the table should be split.
• Every N rows will simply cut the table by every number of rows you enter.
  Note. If you select this option, upon clicking the Next button, you'll be brought right to Step 3, as Step 2 is about choosing the key column for the option above.

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.

Besides, you can use the additional options:

1. If you have a header row which you don’t want to split, tick the My table has 1 header row checkbox. If you have more than one header, click 1 header row and enter the number of labels in your table.
2. Also, you can split a table by the number of characters in the key column. For example, if the key column in your table has the abbreviation of names of the days of week (Mon, Tue, Wed, Thu, Fri, Sat, Sun) and you split the table without indicating the number of characters, you'll get seven tables, one for each value. If you enter "1" into the Number of characters field, only the first characters of the values will be compared, and you'll get six tables, as Sat and Sun will be joined together in one table.

Tip. If you have a lot of columns in your table, take advantage of the Columns checkbox at the top of the list to instantly select or clear all the checkboxes. Tip. If you need to get back to Step 1, click the Back button.

Click Next to proceed.

Step 3: Select destination

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

• Select the Current workbook radio button and the worksheets with the results will be created in the workbook that contains your main table.
• Pick the New workbook option to place the resulting tables in a new Excel file. The add-in suggests you save the new workbook to the same folder as your current book. To choose a different location, click on the icon with three dots in the Save to folder field and you will see the Save As window where you will be able to specify the path.
• Choose Multiple new workbooks to insert each created table in a separate Excel workbook. Use the Save to folder field to browse for the location where you'd like to save the new workbooks. Click on the icon with three dots and open the Browse For Folder window to find the needed place for your new files.

Click Next.

Step 4: Choose additional options

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

How to name new sheets

There are two ways the tool can name new sheets:

• Select Key values to name new worksheets after the values in the column(s) you selected in Step 2.
• Pick the Number radio button to use numbers as worksheet names.

You can also create your own names:

• Choose Before name and enter the text to prefix the name of each new sheet with.
• Check After name to add custom text at the end of all new Excel worksheet names.

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

How to copy headers and preserve formatting

• To get the header row in all the split tables, tick Copy header and specify the row with labels in your Excel table. Use the Select range icon to collapse the window and select the range manually.
• To transfer the current formatting to your new tables, tick the Use the first row to preserve formatting checkbox.

Click Finish and let the tool process your data and create new tables:

Scenarios

How to save scenarios

If you want to make Split Table Wizard keep information about the table and options that you selected when going through its steps, click Save scenario in Step 4:

Then enter the name of your scenario and click OK:

How to run scenarios

To run any of the scenarios you saved, start Split Table Wizard, choose the scenario you need at the moment, make sure that the necessary table is selected, and click the Start button:

Note. For a saved scenario to work for the current worksheet, the structure of your current table must be the same as that of the table in the scenario.

Related links

• How to unpivot your Excel table
• How to use Ablebits Create Cards
• How to transpose a range in Excel
• How to use Split Workbook Wizard for Excel

Ultimate Suite for Excel

This tool is part of Ablebits Ultimate Suite that includes 70+ professional tools and 300+ solutions for daily tasks.

More about Ultimate Suite Free download Download Ultimate Suite for Excel setup file

• For desktop Excel included in Microsoft 365and Excel 2024 – 2016
• 14-day free trial, no credit card required

Please contact us here

I have read and accept the Terms of use and Privacy Policy Our working hours: Pacific Time (PT) 11:00 PM (previous day) – 2:00 PM Central European Time (CET) 08:00 - 23:00 Eastern Time (ET) 2:00 AM – 5:00 PM Central Time (CT) 1:00 AM – 4:00 PM Pacific Time (PT) 11:00 PM (previous day) – 2:00 PM Australian Eastern Daylight Time (AEDT) 6:00 PM – 9:00 AM (next day) If you want to attach files (e.g. screenshots or log files), it will be possible right after you send this form and we automatically create a support ticket for you.