WPS Spreadsheet: Filter an Excel Table Automatically from a Drop-Down Cell
Question details
The user needs to filter an Excel Table automatically based on a value selected from a drop-down cell.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A worksheet contains a drop-down selector and an Excel Table with a column such as Department. The user wants matching rows to appear automatically after selecting a value.
- Observed behavior
- The table does not automatically display only the rows matching the selected drop-down value unless a formula or automation is added.
Confirm the table has a header for the field you want to filter, such as Department, and note the address of the drop-down cell. Ensure the selected value matches the table values exactly, including spaces and capitalization where applicable.
Return matching table rows with the FILTER formula
Use this option when you want a separate dynamic result that updates whenever the drop-down selection changes.
This method creates a filtered result outside the original table. It does not change the table’s built-in AutoFilter state.
In the example, the drop-down is in A1 and the table is named Table1 with a Department column.
Select cell A1, open Data > Data Validation, choose List, and set the source to the cells containing your allowed values, such as =$D$1:$D$3.
Select an empty cell where the results should begin and enter =FILTER(Table1,Table1[Department]=A1,"No results"). Press Enter to spill all matching rows into the worksheet.
Click the drop-down arrow in A1 and select a different department. WPS Spreadsheet updates the spilled result automatically. Leave A1 blank if you want the formula to return no matching rows or add a blank condition if needed.
Apply the table’s built-in filter automatically with a worksheet macro
Use this option when the original table itself must hide and show rows as the drop-down value changes.
Use WPS Spreadsheet to Build Dynamic Drop-Down Table Filters
WPS Spreadsheet can create a validated drop-down cell and return matching table rows with the FILTER function. It also supports familiar table, formula, and macro workflows for Excel-compatible workbooks.
- 1. Create the selector: In WPS Spreadsheet, select the target cell and choose Data > Data Validation > List to create the drop-down.
- 2. Reference the table: Click an empty output cell and enter a FILTER formula that compares the table’s target column with the drop-down cell, such as =FILTER(Table1,Table1[Department]=A1,"No results").
- 3. Verify the results: Select different values from the drop-down and confirm that the spilled result updates to show the corresponding table rows.
Frequently Asked Questions
Can a drop-down cell filter the original Excel Table instead of creating a separate result?
The FILTER function returns a separate dynamic result and does not change the table’s built-in filter. To filter the original table automatically, use a Worksheet_Change macro that applies AutoFilter when the drop-down cell changes.
Where should I enter the FILTER formula?
Enter it in an empty area outside the source table where there is enough space for the result to spill. For a table named Table1 and a selector in A1, use =FILTER(Table1,Table1[Department]=A1,"No results").
What should I do if FILTER returns no results?
Add a fallback message as the third argument, such as "No results", and verify that the selected value exactly matches the table values. Extra spaces or inconsistent spelling can prevent a match.
How do I create the drop-down used by the filter?
Select the selector cell, open Data > Data Validation, choose List, and select the range containing the allowed values, such as =$D$1:$D$3.
Will this work with an XLSX file created in Microsoft Excel?
Yes. WPS Spreadsheet can open and save .xlsx workbooks. Dynamic-array and macro behavior can depend on the WPS version, workbook format, and macro security settings.
