We want to make a searchable drop-down list for two sets of data. We’ll use the Data Validation tool. These lists provide the options you see when you click on the drop-down list.

The drop-down lists will be used to select an appetizer and dessert for each person filling out the survey form.

Creating the Searchable Drop Down List in Excel

To create the drop-down list that will display appetizers, we perform the following steps:

  1. Select the first cell below the “Appetizer” heading (cell B2).
  2. Launch the Data Validation tool by clicking Data (tab) -> Data Tools (group) -> Data Validation.
  3. In the Data Validation dialog box, on the Settings tab, select “List” from the Allow field.
  4. In the Source field, browse out to highlight the list of appetizers (cells “List” sheet, cells A2 through A17. Notice that we have selected a few extra, empty cells for future appetizer entries.)
  5. Click OK to close the Data Validation dialog box.
Creating a searchable drop down list in Excel

Featured Course

Excel Essentials for the Real World

Learn time-saving hacks to work smarter in Excel. Our members also consistently report a newfound enthusiasm for using Excel after taking our course.
Learn More

Using the Searchable Drop Down List

When we select cell B2 and click the drop-down button, we see the list of appetizers from the “Lists” sheet.

💡Notice how the blank cells that were selected do not show up in the drop-down.  Before this new version, any extra cells would have been displayed, making the list appear less than professional.  This relieves us of the need to convert the lists to proper tables or utilize Named Ranges with complex formulas to suppress blanks while making lists easily expandable.

“I want something with cheese.”

Looking through longer lists (say, hundreds of entries), if I want to see all entries that have “cheese” in the name, I can click in the cell and type the first few letters of “cheese”.  Look what happens.

The drop-down list automatically reduces its scope to only display entries that contain the typed character string.

We can apply this drop-down list feature to many cells in the “Appetizer” column by selecting cell B2 and clicking Copy, then select additional cells (ex: B3 through B25) and click Home (tab) -> Clipboard (group) -> Paste -> Paste Special -> Validation.

Adding a data validation in Excel.

Excluding Empty Cells in Drop Down List

We saw that the new Data Validation Drop Down list feature would automatically exclude empty cells located at the end of the source selection range.

But what if the list has empty cells mixed in with the data?

Excluding empty cells in drop down list

Let’s find out how this is dealt with.

  1. Select a range of cells below the “Dessert” heading (cells C2 through C10).
  2. Launch the Data Validation tool by clicking Data (tab) -> Data Tools (group) -> Data Validation.
  3. In the Data Validation dialog box, on the Settings tab, select “List” from the Allow
  4. In the Source field, browse out to highlight the list of appetizers (cells “List” sheet, cells C2 through C12.
  5. Click OK to close the Data Validation dialog box.
How to create a searchable drop down list in Excel

Selecting a cell in the “Dessert” column and typing the first few letters in the words “Peanut Butter”, we see the following filtered drop-down list.

Searchable Drop Down List Excel

It doesn’t matter where those letters appear in the row entry, the matching item is displayed.

To display the entire dessert list, select a cell in the “Dessert” column and click the Data Validation drop-down.  This displays the following result.

Searchable drop down list in Excel

Notice that all the blank cells in between entries as well as extra cells after the list have been removed.

This is how you can take advantage of a searchable Drop Down List in Excel.

Featured Course

Power Excel Bundle

10x your productivity in Excel 💪. This bundle includes Master Excel Power Query course & Master Power Pivot and DAX course. It’s Excel’s Ultimate Power Tools in ONE convenient (cost savings) bundle. Learnings apply to Power BI as well.
Learn More
Power Excel Bundle course cover

Searchable Features in Context Menus

Another new feature is the ability to locate and activate almost any Excel feature from a search box in the right-click context menu.

right-clicking any cell on the worksheet reveals the cell context menu.  At the top of the menu is a Search feature.

Search feature in Excel context menus

When you click in the Search field, you can quickly access virtually any feature in Excel by typing a few letters of the feature name.

Search feature in Excel context menus
Search feature in Excel context menus

NOTE: This Search feature does NOT appear if you are right-clicking on a cell in an official Excel Table.

Leila Gharani

I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.

My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.