How to Create a Drop-Down Filter for Rows Containing Text in Excel
Question details
The user wants to set up a drop-down menu that filters rows in a dataset based on whether a column contains a specifically selected text substring.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Analyzing and organizing large datasets where the user needs to quickly isolate records containing a specific keyword from a predefined list.
- Observed behavior
- The user requires a setup to dynamically or manually filter rows based on a partial text match driven by a drop-down selection.
Ensure your dataset has clear column headers and no completely blank rows so that the filter is applied to the entire range correctly.
Use Built-in AutoFilter with Text Filters
The most straightforward method to filter rows containing specific text without using any code.
Excel and WPS Spreadsheet both offer a built-in AutoFilter feature. This instantly adds drop-down menus to your column headers, allowing you to use the 'Contains' text filter to find partial matches easily.
Click any single cell inside your dataset, or highlight the entire data range including the headers.
Navigate to the 'Data' tab on the top ribbon and click the 'Filter' button. Drop-down arrows will appear on your column headers.
Click the drop-down arrow on the specific column you want to filter. Hover over 'Text Filters' in the menu.
Select 'Contains...' from the sub-menu. In the dialog box that appears, type your desired text (e.g., 'apple') and click 'OK' to filter the rows.
Create a Dynamic Drop-Down Filter using VBA
For an advanced, interactive dashboard experience, you can link a Data Validation drop-down to a VBA macro that automatically filters data containing the selected text.
Use WPS Spreadsheet to Filter Data Effortlessly
WPS Spreadsheet provides an intuitive and user-friendly interface to apply AutoFilters and Data Validation, making it incredibly easy to filter rows by partial text without needing complex coding.
- 1. Open your file in WPS: Launch WPS Spreadsheet and open your dataset.
- 2. Enable AutoFilter: Highlight your dataset, go to the 'Data' tab, and click the 'AutoFilter' icon.
- 3. Choose Text Filter: Click the drop-down arrow on your column header, select 'Text Filters', then choose 'Contains'.
- 4. Enter Search Term: Type your keyword in the prompt box and click 'OK' to instantly view the filtered results.
Frequently Asked Questions
Can I filter for multiple text values at once?
Yes. When using the Custom AutoFilter dialog, you can set the first condition to 'Contains', select the 'Or' radio button, and set a second condition to 'Contains' to filter for two different text values simultaneously.
Why isn't my drop-down filter showing all the rows?
This usually happens if there are entirely blank rows in the middle of your dataset, which breaks the filter range. Remove the blank rows, or manually highlight your entire data range before applying the AutoFilter.
Does the 'Contains' text filter differentiate between uppercase and lowercase?
No, the standard 'Contains' AutoFilter feature in both Excel and WPS Spreadsheet is case-insensitive. Searching for 'apple' will also return rows with 'Apple' or 'APPLE'.
How do I remove the filter to see all my data again?
To clear the filter, click the funnel icon on the filtered column header and select 'Clear Filter'. Alternatively, go to the 'Data' tab and click the 'Clear' button next to the Filter option.
