How to Filter Rows Above a Valuation Threshold in Excel
Question details
The user wants to extract records where company valuations exceed a certain threshold without leaving blank rows, and needs to hide zero values in the results.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Filtering a dataset to show only qualifying investor records based on a numerical threshold.
- Observed behavior
- Using a standard IF formula leaves unwanted blank rows for records that do not meet the threshold, and zero values may appear when source cells are blank.
Ensure you are using a version of Excel (Microsoft 365 or Office 2021) or WPS Office that supports dynamic array functions like FILTER.
Use the FILTER Function to Extract Qualifying Rows
The FILTER function dynamically returns only the rows that meet your condition, automatically eliminating blank rows.
Traditional IF formulas evaluate each row individually, returning empty spaces for rows that fail the condition. The FILTER function processes the entire array and stacks the successful results sequentially.
Locate your source data range (e.g., 'Data Sheet'!A2:D100) and the column containing the valuation figures (e.g., Column B).
Copy the column headers from your source data and paste them into your target sheet so the extracted data has matching labels.
Select the first cell directly under the copied headers (e.g., cell A2) and enter the formula: =FILTER('Data Sheet'!A2:D100,'Data Sheet'!B2:B100>=5,"")
Press Enter. The results will dynamically spill into the adjacent cells, displaying only the records with a valuation of 5 or greater.

Hide Zero Values Using Custom Number Formats
If your filtered data or other formulas return unwanted zero values for blank source cells, you can easily hide them using a custom format.
Use WPS Spreadsheet to Filter and Format Data Easily
WPS Spreadsheet provides robust support for modern array functions, including FILTER, allowing you to extract specific data records effortlessly. It is fully compatible with Microsoft Excel formulas.
- 1. Open your dataset: Launch WPS Spreadsheet and open the file containing your valuation data.
- 2. Input the formula: Select the target cell and input your =FILTER() formula just as you would in Microsoft Excel.
- 3. View filtered results: Press Enter to instantly generate the filtered dataset without any empty rows.

Frequently Asked Questions
Why does the FILTER function return a #CALC! error?
The #CALC! error typically occurs when the FILTER function finds no records that meet the specified criteria. To prevent this, ensure you provide an 'if_empty' argument at the end of your formula, like =FILTER(A2:D100, B2:B100>=5, "No results").
Can I filter rows based on multiple valuation conditions at once?
Yes. You can use an asterisk (*) for AND logic or a plus sign (+) for OR logic between criteria arrays. For example, to find valuations over 5 in the Tech sector: =FILTER(A2:D100, (B2:B100>=5)*(C2:C100="Tech"), "").
How do I remove zero values without changing the cell format code?
Instead of applying a custom number format like ;;;@, you can adjust your application settings. In Excel or WPS Office, go to File > Options > Advanced, scroll down to 'Display options for this worksheet', and uncheck 'Show a zero in cells that have zero value'.




