Split worksheet into multiple sheets based on column

Split worksheet into multiple sheets based on column

Learn how to split one Excel Worksheet into multiple sheets based on a column using a free Excel add-in named Splitter Tools.

When working with large datasets, it’s often necessary to break down information based on specific criteria, such as categories in a column. The Splitter for Excel add-in is a part of our add-ins. It simplifies the process by allowing you to automatically split a worksheet into multiple sheets or even separate files.

How to Split Excel Sheet into Multiple Worksheets

Steps to split a Worksheet into separate sheets:

  1. Open Excel.
  2. Install the Splitter for Excel add-in.
  3. Start the tool and select the range you want to split.
  4. Choose the column based on which the splitting will occur.
  5. Choose the output format.
  6. Click OK to split the Worksheet.

That’s all. This free tool provides a quick solution for managing large datasets, saving users from the manual task of copying and pasting data across multiple sheets or files.

Example: Separate Excel Worksheets based on column

After the successful installation, you’ll see the Splitter Tab on the Ribbon.

splitter add-in

In this example, we have a SalesOrders dataset. The goal is to split this dataset into separate sheets based on the Region column. First, open the add-in. In the add-in window, you must specify the range you want to split, A1:G38.

Select the range of data you want to split based on a column

Choose the Column to Group By: Next, select the column that will serve as the grouping criterion. In this case, we want to group the data by Region to create separate reports for each region. The column containing the region information is column B, selected in this step.

simply select the entire column

Choose the output type that you want the data structured.

You have the option to:

  1. Create new tabs within the current workbook based on a column (as in this example).
  2. If you prefer to export each group to separate Excel files.
  3. Additionally, you can choose to sort the new tabs alphabetically after splitting.
select the output type

To extract the data into multiple tabs based on a selected column, the add-in offers several formatting options:

  1. Report Naming (Optional): First, you can provide a custom name for the generated tabs or files. For example, if you split the worksheet based on the “Region” column, the resulting tabs could be named “East_Report,” “Central_Report,” and “West_Report.”
  2. Convert Outputs to Table Objects: Select this option, and the range is automatically converted into Excel tables. This conversion offers easier sorting, filtering, and formatting options within each new sheet.
  3. Check this box to keep source formulas in Outputs. By selecting this option, you also ensure that any formulas in the original dataset are preserved in the output sheets.
  4. Keep Source Formatting in Outputs: This option ensures the original formatting is carried over into the new tabs or files.
the result looks great

The result looks great!

How to Split Excel Worksheet into multiple files by column value

Steps to split a Worksheet into separate files:

  1. Install the Splitter for Excel add-in.
  2. Start the tool and select the range you want to split.
  3. Select the column you want to group.
  4. Choose the “Create new Files in a directory” option.
  5. Choose the output directory.
  6. Click OK to split and export the Worksheet.

Example

To split a worksheet into separate files based on a column, the process is almost the same as splitting into tabs, but with a few key differences:

How to Split Excel Worksheet into multiple files by column value
  1. Select “Create new Files in a directory”. This will export each group as an individual Excel file. The output will be saved to the directory in the example shown: “C:\Users\PD\Documents\Reports\Regions.”
  2. The formatting options remain the same as those used for splitting into tabs. You can convert the outputs to table objects, retain source formulas, and preserve the original formatting in the newly created files.
  3. After selecting the output directory and configuring the formatting options, simply click Split! to generate the files. The add-in will name each file based on the selected column (in this case, the Region) and create a separate Excel file for each region, making it easy to manage and organize your reports.
result

The example shows three separate files -Central, East, and West- created and saved in the output folder. Each file contains the data filtered by the corresponding region.

You can download the add-in and the sample dataset here.

Additional resources