Skip to content

Searchable Dropdown List in Excel

In this article, I will show you how to create a searchable dropdown list in Microsoft Excel.

Now the first thing I want to point out is that this method works only with the Office 365 version of Excel or Excel 2021 or newer. So if you are working in an older version of Excel, this method will not work.

The Setup

Before you can create your searchable dropdown list, you will need three things.

A list of all the possible dropdown options stored in your spreadsheet.

A space where you will calculate the filtered list of values. Make sure that there is nothing in the cells below this space because the length of the filtered list will be changing continuously.

The cell where you want the searchable dropdown list to be.

Step 1 – Filter the List

Before you create the dropdown menu, you must create a filtered list that you will reference later on.

To do this, select the topmost cell where you want your filtered list to be. Then enter the formula:

=FILTER(listRange, ISNUMBER(SEARCH(dropdownCell, listRange)))

This formula filters the original list based on the value that is typed in the cell that will contain the dropdown menu later on.

If you want to learn more about how this formula works, check out this article. This article uses Google Sheets for the example, but the method works the same way in Excel.

Once you have entered the formula, you are ready for step two.

Step 2 – Add Data Validation

The next step is to add data validation to the cell where you want the dropdown menu.

So select that cell, and then go to Data Data Validation.

In the dialog box, select List under Allow.

For the Source, select the topmost cell of the filtered list range. Also, be sure to add the pound symbol (#) after the cell reference.

The pound symbol returns all the values from the dynamic array that spills out of the referenced cell. In this case, that is the filtered list of values.

Step 3 – Remove the Error Alert

This last step is very important. Before you click Ok in the data validation dialog box, click on the Error Alert tab.

Then uncheck the box next to ‘show error alert after invalid data is entered.’

This will allow Excel to view the text typed into the data-validated cell and filter the original list without throwing an error. If you leave this box checked, Excel would give you an error without filtering the list options.

At this point, you can click on Ok.

You now have a searchable dropdown list that you can use within your Excel workbook.

Download the Example File

Click this link to download the example workbook.

This Post Has One Comment

  1. To be honest, I was a little confused by the article, but the downloadable spreadsheet helped clear my confusion!

    I wasn’t sure why the filtered list was referencing the dropdown list. I thought it would just create a loop. For example, once you choose one of the options from the drop down list, the filtered list would only show those options. Which is true. HOWEVER! You can type in the dropdown list. You don’t have to select just one of the list options. When you type in the dropdown list cell, it will pull all of the options that contain that search word. So the dropdown list itself is really helpful when you use it as a search bar rather than a dropdown selection.

    I would love to see a video on this one! After figuring this one out, I’m really excited to use this! I think it will really transform some of my spreadsheets! Hopefully my comment was helpful!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top