Data Import & Export

How to Split an Excel Table Into Separate Tables by Category

Chanuka Geekiyanage Oct 10, 2026 868 views

Question details

The user needs to separate a single data table containing mixed categories into multiple distinct tables or individual worksheets.

How to Split an Excel Table Into Separate Tables by Category

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Organizing a large, consolidated dataset into smaller, manageable sections based on specific category values for distinct reporting and analysis.
Observed behavior: Data is currently grouped in one large table, requiring a method to extract, copy, and subtotal rows by individual category levels.

Before you start

Ensure your dataset has clear, unique column headers and no entirely blank rows or columns, as this will help the filtering tool accurately identify your data range.

Solution 1Recommended

Use Data Filters to Separate and Extract Categories

Applying filters is the most straightforward manual method to separate your data into individual tables when dealing with a manageable number of categories.

By applying an AutoFilter to your dataset, you can isolate one category at a time. Once isolated, the visible rows can be copied to a new location without affecting the hidden data.

Select the Data Range

Highlight your entire data table, ensuring that the column headers (e.g., Dates, Names, Levels, Amounts) are included in your selection.

Apply the Filter

Navigate to the 'Data' tab on the top ribbon and click on 'Filter'. Drop-down arrows will appear next to each of your column headers.

Isolate a Specific Category

Click the drop-down arrow on your target category column (e.g., the 'Level' column). Uncheck 'Select All', check the box for a single category level, and click 'OK'.

Copy and Paste Visible Rows

Select the visible filtered rows, press Ctrl+C to copy them, open a new worksheet (or select a blank area on the current sheet), and press Ctrl+V to paste.

Calculate Totals

To calculate totals for your new isolated table, click the cell below your amount column and enter a formula such as =SUBTOTAL(109, D2:D1000). Adjust the range (D2:D1000) to perfectly match your specific data.

Subtotal Function Advantage: Using the SUBTOTAL function with the code '109' is highly recommended because it calculates the sum of visible rows only, ignoring any rows that might be hidden by subsequent filters.

Efficient Data Management with WPS Spreadsheet

Easily Split and Filter Tables in WPS Office

WPS Spreadsheet provides powerful data management tools, including advanced AutoFilters and Pivot Tables, allowing you to quickly split, isolate, and analyze categorized data seamlessly.

1. Open Your Spreadsheet: Launch WPS Office and open the file containing your consolidated data table.
2. Enable AutoFilter: Go to the 'Data' tab and click 'AutoFilter' to add sorting arrows to your header row.
3. Filter by Category: Click the arrow on the category column, select a single level, and copy the isolated data.
4. Paste and Analyze: Create a new worksheet using the '+' icon at the bottom, paste your data, and use the 'Formulas' tab to quickly add subtotal calculations.

Advanced AutoFilter tools for quick data separation and extractionFully compatible with Microsoft Excel (.xlsx, .xls, .csv) formatsBuilt-in robust calculation formulas like SUBTOTAL and SUMIFSLightweight, fast-loading, and free to use for daily spreadsheet tasks

microsoft office alternative - wps office

Frequently Asked Questions

Is there an automatic way to split an Excel table into multiple sheets by category?

Yes, you can create a PivotTable from your data, place your category field in the 'Filters' area, and then go to PivotTable Analyze > Options > Show Report Filter Pages. This automatically generates a new worksheet for each category, though it outputs PivotTables rather than standard ranges.

Can I use a formula to split data into different tables?

If you are using a recent version of Excel or WPS Spreadsheet that supports dynamic arrays, you can use the FILTER function. For example, entering =FILTER(A2:D100, C2:C100="Level 1") in a new location will automatically extract all rows matching that category without manual copying.

Why is my SUBTOTAL formula not calculating properly after pasting?

If you copy and paste filtered data as values rather than standard formatting, you no longer need the SUBTOTAL function. A standard =SUM(D2:D50) formula might be more appropriate. Ensure you are using function code 109 only when you need to exclude hidden rows in a filtered list.

Excel & Data Problems