How to Filter Excel Rows When Another Column Contains Numbers
Question details
The user needs to extract or filter specific values from one column when the corresponding cells in an adjacent column contain numeric conditions, such as being greater than or equal to a specific number.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Filtering a dataset dynamically to display rows where a corresponding criteria column meets a specified numeric threshold, rather than manually hiding rows.
- Observed behavior
- Returns a filtered array of values from the target column where the condition in the criteria column (e.g., values matching or exceeding a cell reference) is successfully met.
Ensure that your dataset does not contain merged cells within the ranges being filtered, and that your spreadsheet software supports dynamic array functions like FILTER.
Use the FILTER Function with a Dynamic Cell Reference
Use a specific cell to control the numeric threshold dynamically, allowing you to change the filter criteria without editing the formula.
Instead of hardcoding the numeric condition into your formula, linking it to a separate cell allows for seamless threshold adjustments. This is particularly useful for interactive dashboards and reporting.
Choose a blank cell outside your dataset, such as A1, and type your desired numeric threshold (e.g., 1).
Select the empty cell where you want the filtered data to appear and type: =FILTER(A2:A48, E2:E48>=A1, "NODATA")
Press Enter to generate the filtered array. The formula will extract all values from A2:A48 where the corresponding value in E2:E48 is greater than or equal to the number in A1.

Use the FILTER Function with Hardcoded Numbers
Apply a static numeric condition directly inside the formula for quick, one-off filtering tasks.
Easily Filter Data with Dynamic Arrays in WPS Spreadsheet
WPS Spreadsheet fully supports advanced dynamic array formulas like the FILTER function, allowing you to instantly extract rows based on numeric conditions without complex VBA scripts or macros.
- 1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing your un-filtered data.
- 2. Insert the FILTER formula: Click on a blank cell where you want the extracted data to spill over, and type =FILTER().
- 3. Define your criteria: Select your target array, add your numeric condition array (e.g., E2:E48>=1), and press Enter to instantly populate the filtered results.

Frequently Asked Questions
Why does my FILTER formula return a #CALC! error?
The #CALC! error occurs when the FILTER function finds no records matching your criteria and no 'if_empty' value is provided. Adding a fallback string like "NODATA" at the end of your formula (e.g., =FILTER(A2:A48, E2:E48>=1, "NODATA")) resolves this issue.
Can I filter rows using multiple numeric conditions?
Yes. You can multiply criteria arrays together to apply an AND logic. For example, using =FILTER(A2:A48, (E2:E48>=1)*(E2:E48<=5), "NODATA") will return rows where the value in column E is between 1 and 5.
Is the FILTER function available in older versions of spreadsheet software?
The FILTER function is a dynamic array formula that might not be supported in very old spreadsheet versions. If you encounter a #NAME? error, ensure your software is updated to a modern version like WPS Office or Microsoft 365. Alternatively, you can use the Advanced Filter tool from the Data tab.




