Use FILTER in WPS Spreadsheets to Return Rows Where a Column Exceeds a Value
Question details
The user needs to return columns A:L from another worksheet when column C contains a numeric value greater than 2, using the FILTER function in WPS Spreadsheets.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A workbook has a source worksheet named Data Sheet with data in rows 3:200. The user wants a dynamic list on a destination sheet showing only rows where column C exceeds 2.
- Observed behavior
- The expected result is that only matching rows, such as the row for Carlton, appear and update automatically when source data changes.
Verify that the source worksheet is named Data Sheet and that column C contains numbers rather than text stored as numbers. If the sheet name or range differs, adjust the references in the formula.
Enter the FILTER formula with a worksheet reference and a >2 condition
Use this approach when the source data is in Data Sheet!A3:L200 and you want matching rows returned to another worksheet.
The FILTER function returns an array of rows. The include argument 'Data Sheet'!C3:C200>2 evaluates each row in column C; only rows where the result is TRUE are returned. The third argument "" returns an empty string if no rows match.
In the destination worksheet, copy the header row from 'Data Sheet'!A3:L3 into the first row where you want the filtered results to start.
Select the first result cell below the headers, type =FILTER('Data Sheet'!A3:L200,'Data Sheet'!C3:C200>2,""), and press Enter.
Confirm that only rows with column C greater than 2 are listed, such as the Carlton row. Change a source value above or below 2 to see the results recalculate automatically.
Filter rows with a condition in WPS Spreadsheets
Use WPS Spreadsheets to enter the FILTER function directly, reference a separate worksheet, and return only rows where column C exceeds 2—without installing Microsoft 365.
- 1. Launch WPS Spreadsheets: Open WPS Office and start WPS Spreadsheets, then open the workbook containing the source and destination worksheets.
- 2. Select the destination sheet: Click the destination worksheet tab where you want the filtered rows to appear.
- 3. Enter the FILTER formula: Select the first result cell, type =FILTER('Data Sheet'!A3:L200,'Data Sheet'!C3:C200>2,""), and press Enter.
- 4. Review the returned rows: Check that only rows meeting the column C > 2 condition are displayed, including the Carlton row.
Frequently Asked Questions
Can the FILTER function reference a worksheet name with spaces?
Yes. Wrap the sheet name in single quotes, as in =FILTER('Data Sheet'!A3:L200,'Data Sheet'!C3:C200>2,""). If your sheet has a different name, update both references.
What does the third argument "" mean in the FILTER formula?
It is the value returned when no rows meet the condition. Using an empty string keeps the result cell blank instead of showing an error.
Why does my FILTER formula return a #SPILL! error?
The destination range below the formula must be empty. Clear any content or merged cells where the filtered rows would spill.
Can I combine this condition with another criterion?
Yes. Multiply the include arrays for AND logic, for example =FILTER('Data Sheet'!A3:L200,('Data Sheet'!C3:C200>2)*('Data Sheet'!D3:D200="Active"),"").
Does the FILTER result update when the source worksheet changes?
Yes. The formula dynamically recalculates whenever values in Data Sheet rows 3:200 change.
