Create a Searchable Drop-Down List in Microsoft Excel
Question details
Create an Excel drop-down list that makes it easier to find an option by typing.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user wants to search a list of options instead of scrolling through a standard Data Validation drop-down.
- Observed behavior
- A matching option can be found by typing, either in a supported drop-down or in a separate search cell.
Check your Excel version and place your source options in a single column. The FILTER method requires a version that supports dynamic arrays and a clear area for the filtered results to spill.
Filter a List with a Search Cell in Excel
Use a separate search cell and a dynamic helper list to narrow down Data Validation options in supported Microsoft 365 versions.
This method searches from a separate cell, not inside the drop-down itself. The list updates as you type in the search cell.
Enter your options in cells A2:A100, leaving unused cells blank. Use D2 as the search cell and F2 as the cell that will contain the drop-down.
Enter =FILTER(A2:A100,(A2:A100<>"")*ISNUMBER(SEARCH(D2,A2:A100)),"No matches") in E2. Keep the cells below E2 clear so the results can spill.
Select F2, then choose Data > Data Validation. Under Allow, select List, enter =$E$2# in Source, and click OK.
Type part of an option in D2, then open the drop-down in F2 to select a matching result. Clear D2 to show all nonblank options.

Use Excel's Built-In Drop-Down Autocomplete
Try this option if your Microsoft 365 version supports autocomplete for Data Validation lists.
Manage Spreadsheet Lists with WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for Excel formats, making it easy to move existing workbooks. It does not change how drop-downs behave in Microsoft Excel.

Frequently Asked Questions
Can I search by typing directly in an Excel drop-down?
Some Microsoft 365 versions support autocomplete in Data Validation cells. Select the validated cell and start typing to check whether matching entries appear.
Does the FILTER method search anywhere in an option?
Yes. The SEARCH function in the formula matches text anywhere in each option and is not case-sensitive. Type the search text in D2 to update the helper list.
Why does my filtered list show a spill error?
The cells below E2 may contain data. Clear the area where the FILTER results need to appear, then check the formula in E2 again.
What can I use if my Excel version does not support FILTER?
Consider a ComboBox or VBA UserForm for a searchable interface. Store the source options in an Excel Table so the list is easier to maintain.




