Data Import & Export

How to Separate Excel Order Lists by Category and Remove Zero Values

Rana GarciaRana Garcia Nov 11, 2026 869 views

Question details

The user needs to split a main order data list into separate categorized lists (e.g., Pickup, Eicher, Small Eicher) while simultaneously excluding any rows where the order value is 0.00.

How to Separate Excel Order Lists by Category and Exclude Zero Values
Product
Excel
Device & OS
not provided
Scenario
Organizing bulk order data into distinct categorized lists for easier processing and accurate reporting.
Observed behavior
The goal state is to output categorized data dynamically or manually without including any rows that contain an amount of 0.00.
Before you start

Ensure your source data is formatted as a continuous table with clear column headers for categories and order amounts to make referencing easier.

Solution 1Recommended

Use the FILTER Function to Separate Categories and Exclude Zeros

The most efficient way to dynamically split data by category and remove zero values is using the modern FILTER function with multiple criteria.

The FILTER function allows you to extract data matching multiple conditions simultaneously by multiplying the criteria arrays together. This means if the source data updates, your separated lists will update automatically.

1
Identify your data ranges

Take note of the range of your entire data table (e.g., A2:D100), the category column range (e.g., B2:B100), and the value/amount column range (e.g., D2:D100).

2
Enter the FILTER formula for the first category

Click on the top-left cell of where you want the new list to appear. Enter the formula: =FILTER(A2:D100, (B2:B100="Pickup")*(D2:D100<>0), "No records found"). Press Enter.

3
Repeat for other categories

Move to a new section or worksheet for your next list. Modify the category name in the formula to "Eicher" or "Small Eicher" and apply it again to pull the respective non-zero data.

Use the FILTER Function to Separate Categories and Exclude Zeros
Formula Tip: Using the asterisk (*) between conditions acts as an 'AND' logic operator, ensuring both conditions (category match AND non-zero value) must be met for a row to be included.
Free Microsoft Office alternative

Easily Filter and Separate Data in WPS Spreadsheet

WPS Office provides powerful built-in functions like FILTER, as well as intuitive sorting and filtering tools, making it easy to manage large datasets and categorize orders instantly.

  1. 1. Open your data in WPS Spreadsheet: Launch WPS Office and open your order data workbook.
  2. 2. Apply dynamic array formulas: Select an empty cell in a new worksheet and input the FILTER formula with multiple criteria to automatically pull your categorized, non-zero data.
  3. 3. Use AutoFilter as a quick alternative: Go to the Data tab, click Filter, uncheck '0.00' in the amount column, and select only your desired category to view the separated list immediately without formulas.
Fully compatible with Microsoft Excel formulas, including advanced array functions.Powerful data filtering and sorting capabilities to handle large order lists.Lightweight and runs smoothly on Windows, Mac, Linux, iOS, and Android.Free alternative with a familiar tabbed interface for seamless migration.
microsoft office alternative - wps office

Frequently Asked Questions

Can I split data into different worksheets automatically?

Yes, you can place the =FILTER() formula directly into cell A1 of a new worksheet. As long as you reference the original data sheet properly (e.g., Sheet1!A2:D100), it will dynamically pull the matching data.

Why is my FILTER function returning a #CALC! or #VALUE! error?

This usually happens if the criteria ranges are not exactly the same size as the data range, or if no records match the criteria. Ensure your arrays match in size and provide a third argument like "No records" in the formula to handle empty results gracefully.

How do I filter out blank rows along with zero values?

You can add a third condition to your FILTER formula. Simply multiply another condition checking for blanks. For example: =FILTER(data, (category="Pickup") * (amount<>0) * (amount<>"")).