Filter Excel Table Rows into an Array in WPS Spreadsheet
Question details
The user wants to collect all rows from an Excel table where the CUSTOMER_STATE column equals CA and store those rows in an array for later processing.
- Product
- Microsoft Excel Office Scripts / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- The user has a table such as Table1 with a CUSTOMER_STATE column and needs a repeatable way to filter rows matching a value like CA.
- Observed behavior
- The desired result is an array of matching table rows that can be looped through, copied, or used for aggregation.
If you need the Office Scripts code, use Microsoft 365 Excel because Office Scripts is not available in WPS Office. For a WPS alternative, open the workbook in WPS Spreadsheet and confirm the table is named Table1 and the column header is exactly CUSTOMER_STATE.
Return matching rows as a dynamic array with the WPS FILTER function
Best for most users who want a no-code, WPS-compatible way to return all CA rows as a dynamic array.
WPS Spreadsheet can filter an Excel table into an array-like spill range without Office Scripts. The FILTER function checks the CUSTOMER_STATE column and returns every row where the value equals CA.
Launch WPS Office, open WPS Spreadsheet, and load the Excel workbook that contains Table1.
Click a blank cell where the matching rows should appear.
Type =FILTER(Table1,Table1[CUSTOMER_STATE]="CA") and press Enter.
Use the spill reference such as E2# in other formulas, or select and copy the returned rows to another sheet.
Run the Office Scripts code in Microsoft 365 Excel
Use this when you must use Office Scripts as specified; WPS Office does not run Office Scripts.
Use WPS AutoFilter to copy matching rows manually
Use this when you prefer a click-driven approach and only need to copy the matching rows to another location.
Filter Excel table rows with WPS Spreadsheet instead of Office Scripts
Office Scripts is a Microsoft 365 Excel feature that WPS Office does not run. Switch to WPS Spreadsheet for a free, lightweight and familiar alternative that opens .xlsx files, preserves table and formula compatibility, and lets you filter matching rows with built-in tools.
Frequently Asked Questions
Can WPS Office run Office Scripts?
No. Office Scripts is a Microsoft 365 Excel feature. In WPS Office, use the FILTER function, AutoFilter, or a VBA-compatible macro to get the same row-filtering result.
How do I return all rows where CUSTOMER_STATE is CA in WPS Spreadsheet?
Enter =FILTER(Table1,Table1[CUSTOMER_STATE]="CA") in a blank cell. WPS returns a dynamic array containing all matching rows.
Does the FILTER function work with Excel tables in WPS?
Yes. The formula can reference the table name and the column header, for example Table1[CUSTOMER_STATE], and it updates when source data changes.
How do I copy matching rows to another sheet without a formula?
Click inside the table, choose Data > AutoFilter, filter CUSTOMER_STATE to CA, select the visible rows, press Ctrl+C, and paste them into another sheet.
Will my .xlsx workbook stay compatible if I use WPS instead of Microsoft Excel?
Yes. WPS Spreadsheet opens, edits, and saves .xlsx files while preserving table structure, formulas, and formatting for Microsoft Excel compatibility.
