Note: The other languages of the website are Google-translated. Back to English

Quickly split data into multiple worksheets based on selected column in Excel

Supposing you have a worksheet that has data in columns A to G, the salesman’s name is in column A and you need to automatically split this data into multiple worksheets based on the name in column A. Each salesman will be split into a new worksheet. Kutools for Excel’s Split Date utility can quickly split data into multiple worksheets based on selected column in Excel.

Split data into multiple worksheets based on selected column

Split data into multiple worksheets based on rows number


Click Kutools Plus > Worksheet > Split Data (or Kutools Plus > Split Data). See screenshots:

kutools-arrow-right

Split data into multiple worksheets based on selected column

Supposing you have a range data as shown in the below screenshot, and you want to quickly split this data into multiple worksheets based on the column A in a workbook by splitting each name in a new worksheet. For example, all Kelly will be split into the same worksheet. You can get it done as follows:

1. Please select the range which you want to split into multiple worksheet based on a column.

2. And then applying the utility by clicking Kutools Plus > Worksheet > Split Data. In the Split Date into Multiple Worksheets dialog box, please choose Specific column and then specify a column which you want to split data based on from the drop-down list.

Tip: If your selected range contains header, please checked My data has headers option, you can specify the number of header rows based on your data. For example, if your data contains two headers, please type 2.

3. Please specify the new worksheet names. You can use the Values of Column as the new worksheet names. You can also type in a Prefix or Suffix for the worksheet names.

4. Click OK to split data, now you can see each name in the column will be split into a new worksheet within a new workbook. See screenshot:


Split data into multiple worksheets based on rows number

Supposing you have a range data as shown in the below screenshot, and you want to quickly split this data into multiple worksheets based on rows number, for example, split every 5 rows of data into a new worksheet. You can get it down as follows:

1. Please select the range that you want to split into multiple worksheets based on every 5 rows.

2. And then applying the utility by clicking Kutools Plus > Worksheet > Split Data. In the Split Date into Multiple Worksheets dialog box, please choose Fixed rows and then specify the rows number in the box.

Tip: If your selected range contains header, please checked My data has headers option, you can specify the number of header rows based on your data. For example, if your data contains two headers, please type 2.

3. Please specify the new worksheets name. You can use the Row Numbers as the new worksheets name. You can also type in a Prefix or Suffix for the worksheets name.

4. Click OK to split data, every 5 rows of this range will be split into a new worksheet within a new workbook. See screenshot:


Demo: Quickly split data into multiple worksheets based on selected column in Excel

Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. Download and free trial Now!

Productivity Tools Recommended
The following tools can greatly save your time and money, which one is right for you?
Office Tab: Using handy tabs in your Office, as the way of Chrome, Firefox and New Internet Explorer.
Kutools for Excel: More than 300 Advanced Functions for Excel 2021, 2019, 2016, 2013, 2010, 2007 and Office 365.

Kutools for Excel

The functionality described above is just one of 300 powerful functions of Kutools for Excel.

Designed for Excel(Office) 2021, 2019, 2016, 2013, 2010, 2007 and Office 365. Free download and use for 60 days.

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

Comments (18)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
I want to split data into multiple worksheets based on columns(2) Please give me some help!
This comment was minimized by the moderator on the site
I need the same ! I am looking to split the data based on 2 columns. want to see worksheets that show "column1" - "Column2"
This comment was minimized by the moderator on the site
I cannot locate the Enterprise > Worksheet Tools > Split Data in Excel 2010. Is this a standard tool on the tool bar or do i need to install this from add-ins or some other feature.
This comment was minimized by the moderator on the site
When using split data, the last row of each new worksheet has the first record that's supposed to be on the next worksheet. Also, the formatting of the original worksheet is lost in the new worksheets.
This comment was minimized by the moderator on the site
In my previous comment, the data I was splitting was in a table. Once I converted it to a range, I didn't have the problem with the record. The formatting issue still remains.
This comment was minimized by the moderator on the site
[quote]In my previous comment, the data I was splitting was in a table. Once I converted it to a range, I didn't have the problem with the record. The formatting issue still remains.By Carrie[/quote] We are going to enhance this utility to accomplish the Table data and try to support keep formatting in upcoming version. :-) We are working on it.
This comment was minimized by the moderator on the site
This function is not working, any suggestions
This comment was minimized by the moderator on the site
This comment was minimized by the moderator on the site
I'm having a similar issue with this feature not working. seems to copy of of sync. believe its to do with there being only single entries for some of the data. cant work out why its not using the selected headers and replacing them with the first entry using the macro they have seems to work: http://www.extendoffice.com/documents/excel/1174-excel-split-data-into-multiple-worksheets-based-on-column.html
This comment was minimized by the moderator on the site
Has there been a resolution to this? I am unable to split data into multiple worksheets based on the content of a column.
This comment was minimized by the moderator on the site
There seems to be an issue with the Kutools "Copy Page Setup" print function when used in Office 365 as it isn't able to copy the "Fit All Columns on One Page" setting to the other worksheets. Instead it uses the "Fit Sheet on One Page". Tried finding the printer default settings for the worksheets created by Kutools, but wasn't successful - it doesn't seem to use the settings from the default template? How can this be achieved?
This comment was minimized by the moderator on the site
Is there something similar for Google Sheets?
This comment was minimized by the moderator on the site
I can split data, but it doesn't keep the formatting. It's almost not worth it to have it split the data without the formatting.
This comment was minimized by the moderator on the site
Currently, we can split based on text string in a certain column, based on specific rows in a certain column. I think it is fantastic to add the ability to split based on the time group in a certain column. For example, I have a date column, values are 01/01/2020, 01/03/2020....03/05/2025.., I want to split the whole table by year into different sheets.
This comment was minimized by the moderator on the site
Hi there,
Brilliant function to split data to new worksheets.
Sometimes inconsistent naming of the sheets when selecting "Values of Column" as new worksheet name. Reverts to "Sheet 1" etc.
What is the rule that determines this?Are there any workarounds?
Many thanksNaomi
This comment was minimized by the moderator on the site
This split function is really helpful but I need total also in subsequent split sheets. What should I do? Please help
This comment was minimized by the moderator on the site
It works well, How can I fetch all data from different sheeets to one sheet?

any one to help
This comment was minimized by the moderator on the site
Hello, PETER
To merge data from multiple sheets into one single sheet, the following article may help you.
https://www.extendoffice.com/documents/excel/1184-excel-merge-multiple-worksheets-into-one.html
or you can use our Kutools for Excel 's Combine multiple worksheets from workbooks into one worksheet to solve this task, please check this article:
https://www.extendoffice.com/product/kutools-for-excel/excel-combine-worksheets-into-one.html
Please check it, if you have any other problem, please comment here again.
There are no comments posted here yet

Follow Us

Copyright © 2009 - www.extendoffice.com. | All rights reserved. Powered by ExtendOffice. | Sitemap
Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.
Protected by Sectigo SSL