Easily split Excel data into multiple sheets based on values or row numbers

Suppose you have a worksheet with data in columns A to G, where the salesman's name is listed in column A. You need to automatically split this data into multiple worksheets so that each salesman’s data appears in a separate sheet. Kutools for Excel’s Split Data utility can help you quickly split data into multiple worksheets based on the selected column, saving time and effort compared to doing it manually.

This utility is not limited to splitting data by names—it can also divide data based on any column values or by a fixed number of rows, making it a flexible solution for managing various types of large datasets in Excel.

Split data into multiple worksheets based on values in a specific column

Split data into multiple worksheets based on fixed number of rows

Why use Kutools' Split Data feature in Excel?

Manually splitting data into multiple worksheets in Excel can be tedious and error-prone—especially with large datasets. Kutools for Excel makes this task effortless with its powerful Split Data utility. Here’s why it’s worth using:

• ⚡ Save time and clicks – Instantly split data without writing formulas, VBA, or copying sheets manually.
• 📂 Split by column or row – Easily divide data based on column values or fixed row intervals, depending on your needs.
• 🧠 No tech skills needed – The user-friendly interface makes it simple for anyone to use—no coding or advanced Excel skills required.
• 🛠️ Customize sheet names – Use column values, sequence numbers, or your own prefix/suffix for naming the worksheets.
• 📁 Flexible output – Choose to save the new sheets in the current workbook or export them into a new one.
• ✅ Error-free process – Reduces human mistakes often made during manual copy-pasting or filtering.

Whether you're working with sales records, reports, or large data exports, Kutools simplifies your workflow and improves productivity.

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

Split data into multiple worksheets based on values in a specific column

Suppose you have a dataset like the one shown in the screenshot below, and you want to quickly split this data into multiple worksheets based on the values in column A. For instance, all rows with the name Kelly will be grouped into the same worksheet. You can accomplish this easily by following the steps below:

1. Select the range that you want to split into multiple worksheets based on a specific column.
2. Apply the utility by clicking Kutools Plus > Worksheet > Split Data.
3. In the Split Data into Multiple Worksheets dialog box:
   1. Choose Specific column and then select the column you want to split the data by from the drop-down list.
      Tip: If your selected range contains headers, check the My data has headers option. You can specify the number of header rows based on your data. For example, if your data contains two header rows, enter 2.
   2. Specify the new worksheet names:
      • You can use the Values of Column as worksheet names.
      • Optionally, add a Prefix or Suffix to the worksheet names.
   3. Choose where to save the new worksheets. You can split the data into:
      • A new workbook
      • The current workbook

      

4. Click OK to execute the split. Each unique value in the selected column will be split into a new worksheet, either in a new workbook or the current one as specified.

   

Split data into multiple worksheets based on fixed number of rows

If you want to split a dataset into multiple worksheets based on a specific number of rows—for example, every 5 rows—doing this manually can be time-consuming. With Kutools for Excel, the Split Data utility allows you to split data into multiple worksheets quickly and easily by defining a fixed number of rows per sheet.

1. Select the range that you want to split into multiple worksheets based on every 5 rows.
2. Apply the utility by clicking Kutools Plus > Worksheet > Split Data.
3. In the Split Data into Multiple Worksheets dialog box:
   1. Choose Fixed rows, then specify the number of rows (e.g., 5) in the input box.
      Tip: If your selected range contains headers, check the My data has headers option. You can specify the number of header rows based on your data. For example, if your data contains two header rows, enter 2.
   2. Specify the worksheet names:
      • You can use Row Numbers, Sequence Numbers, or the Excel Default Name as worksheet names.
      • Optionally, add a Prefix or Suffix to the worksheet names.
   3. Choose where to save the new worksheets. You can split the data into:
      • A new workbook
      • The current workbook

      

4. Click OK to split the data. Every 5 rows of your selected range will be split into a new worksheet in either a new workbook or the current one, depending on your settings.

   

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