Quickly split data into multiple worksheets based on selected column in Excel
Kutools for Excel
Boosts Excel With 300+
Powerful Features
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:
 |  |  |
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 columns in Excel
Productivity Tools Recommended
Office Tab: Use handy tabs in Microsoft Office, just like Chrome, Firefox, and the new Edge browser. Easily switch between documents with tabs — no more cluttered windows. Know more...
Kutools for Outlook: Kutools for Outlook offers 100+ powerful features for Microsoft Outlook 2010–2024 (and later versions), as well as Microsoft 365, helping you simplify email management and boost productivity. Know more...
Kutools for Excel
Kutools for Excel offers 300+ advanced features to streamline your work in Excel 2010 – 2024 and Microsoft 365. The feature above is just one of many time-saving tools included.
