How to use Split Table Wizard for Excel

Using Split Table Wizard, 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 efforts. Below you can learn how to use this add-in.

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 in the Transform group on the Ablebits Tools tab:
Run Split Table in Excel.

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 field. Just use the Select range icon you'll see there or select the table manually in your spreadsheet:
Choose the Excel table to split.

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:
Pick the key columns to split your Excel table.

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.

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 tick or untick all the boxes.
Tip. If you need to get back to step 1, press the Back button in the add-in window.

Click Next to proceed.

Step 3: Select destination

This step lets you choose where to place the split tables:
Select destination for your Excel split tables.

  • Select the Current workbook radio button if you would like to insert the sheets with the grouped records to 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 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 the created Excel sheets or files and specify if you want to preserve original formatting and headers:
Select additional options.

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 on step 2.
  • Pick the Number radio button to use numbers as spreadsheet names.
  • Choose Before name 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.
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, choose to Copy header and specify the row with labels in your Excel table. Use Select range in this field to collapse the window and highlight the range manually.
  • 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:
Split your Excel tables easily.


Is there a way to preserve formulas within the split-table wizard? When exporting as multiple workbooks, it seems only the value of a cell is copied, not the underlying formula.


Hi, Augie,
Thank you for the question. Unfortunately, there is no such an option to preserve formulas when you split a table. However, I will discuss this idea with our developers and maybe this feature will be implemented in future releases.

Thank you.


Agreed! Need to preserve formulas. And additionally would like to duplicate other sheets to the new workbooks.


Thank you for your comment, Gabriel.

I'm sorry, but it is still not possible to preserve formulas when splitting your tables. If there's any news on this matter, we'll email you right away.

As for duplicating the sheets, it won't happen automatically while splitting. However, there are a couple of quick solutions described on our blog:
How to copy and move sheets in Excel

Hope you'll find this information helpful.

I join the proposal about the preservation of formulas.
When you select the option to save the formatting and table header, the formatting of the header is copied to all rows. Is it possible to split the copy format settings to
1) copy headers formatting
2) copy cells formatting ?


Thank you for your question, Denis.

I've forwarded your request to our QA assistants and developers and they will check if it's possible to introduce what you described.
As soon as there's any news, I'll reply to you by email.

Thank you for your understanding.



I am wondering if there a way to refresh the data once the split has been completed.
i.e. if more data is added to the original table is there a way to refresh the data so the newly added information is added to the respective separate sheet?

Thank You in advance.


Hi Jaclyn,
Thank you for the question. I am afraid there is no way to refresh your data in the split tables automatically when the split has been completed. Each time you add information to the original table you should perform the split again to keep data in split tables up to date.

Thank you.


Hello. Once the split is complete and new workbooks are created, is there a way to utilize Ablebits to attach these new files to e-mails. Example: I have 3 rows, Ablebits creates 3 new files, 1 for each row. Each row is for a different person (Jack, Carl, Jane). If I include the e-mail address in the original file for each person, can Ablebits attach each of these new files to a new e-mail for each person (Jack's file is attached to a file with Jack's email, etc.)?


Hi Nick,
Thank you for your question. Unfortunately, the tool can't do this, but the good news is that we included this idea to our plan and maybe we will implement it in our future releases. I wish I could assist you more.
Thank you.

Andrew ofthesong says:
September 30, 2019 at 2:50 pm

Hi, great tool.
The "split table" could have copy the original workbook settings (page layout, margin, footer)


Thank you for your feedback, Andrew.

There is the "Preserve formatting" checkbox on the final step of Split Table that helps you save the original table's settings.

If you have any questions, do not hesitate to address them to Thank you.


Preserve formatting didnt work for me as it supposed to
it uses 1st raw after the header format and apply it to the rest of rows

Katerina Bespalaya says:
February 14, 2020 at 12:13 pm


Thank you for your comment. The add-in works in this way by design. When you split the table and select the "Preserve formatting" option, the add-in splits your table into separate worksheets and picks up the formatting of the first row with values from your original table, the one after the header row, for all new sheets. I am afraid, we can hardly change this behavior because, otherwise, splitting a table will turn into a very cumbersome and time-consuming task that would take hours. Sorry for any inconvenience.

Unfortunately, The "Preserve Formatting" feature is not working correctly, the entire divided worksheet is created in the format of the first row. is there a patch for this?


Hello Nora,

Thank you for contacting us. You see, it is the by-design behavior of this option when splitting tables. Unfortunately, Excel's api for cells formatting is very slow so keeping formatting for the whole table would be an extremely time-consuming operation. So, we decided to use the formatting of the first row.

Thank you for your understanding.


Ask a question (posted publicly)

If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at
60+ professional tools for Excel
60+ professional tools for Excel
2019–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