Searchable Drop-Down Lists in Excel with ZERO Effort

FINALLY!!!

Excel now supports searchable drop-down lists!

Until this new feature hit the streets, searchable drop-down lists required writing complex VBA code coupled with advanced Excel features to work properly.

Searchable drop-down lists now work without any user effort whatsoever.  The feature is native to Excel.

In January 2022, this feature is available to select Beta Channel users of Office 365.  When officially released, the feature will be rolled out to Office 365 and standalone Excel users running the latest yearly version

Let’s look at an example of this wonderful, soon-to-be-released, new feature.

We start by creating two lists that will be used by the Data Validation tool.  These lists supply the entries shown when the user activates 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 First Drop-Down List

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
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.

Excel Essentials for the Real World - Complete Course

Learn Excel from Scratch or Fill in the Gaps.

Become Confident.

Packed with Challenges & Files (Excel 2016, 2019, Office 365).

GET ACCESS

Using the “Appetizer” Drop-Down List

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

BONUS FEATURE: 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.

Excluding Empty Cells

We saw that the new Data Validation Drop-Down 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?

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.

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.

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.

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

POWER EXCEL BUNDLE

Everything you need to master Excel’s Business Intelligence tools

GET ACCESS

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.

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.

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