How to Separate Excel Order Lists by Category and Remove Zero Values
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.

- 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.
Ensure your source data is formatted as a continuous table with clear column headers for categories and order amounts to make referencing easier.
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.
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).
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.
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 Advanced Filter for Static Separation
If your version of the spreadsheet does not support the dynamic FILTER function, you can use the Advanced Filter feature to extract the data manually.
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. Open your data in WPS Spreadsheet: Launch WPS Office and open your order data workbook.
- 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. 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.

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<>"")).




