Home>Blogs>Excel Tips and Tricks>Searchable Excel Drop down list for Multiple Cells
Searchable Dropdown
Excel Tips and Tricks

Searchable Excel Drop down list for Multiple Cells

In this article you will learn how to create a Searchable Excel Drop down list in excel. Just assume you have a lengthy list in excel data validation and if you must select any item then it will take some time because you must scroll it to find your desired item in the excel data validation list.

Here I have created a solution of this and that is Searchable Drop-Down List. Just type the any keyword on an excel cell and click on drop-down arrow, you will see the drop-down list related your keyword.

Searchable Excel Drop down list

Searchable Excel Drop down list for Multiple Cells
Searchable Excel Drop down list for Multiple Cells

Below are the steps to create drop down lists and make then searchable for multiple cells

Let’s say we have a country list on a worksheet (“Sheet1“) in column A.

Country list
Country list

Put the below given formula on column B,C and D

  • Put the headers – “Search” on cell “B1“, “Frequency” on cell “C1” and “Final List” on cell “D1
  • Put the formula “=IFERROR(SEARCH(INDIRECT(CELL(“address”)),A2),0)“on cell “B2“.
  • Put the formula “=IF(B2=0,””,COUNTIF($B$2:B2,”>0″))”on cell “C2“.
  • Put the formula “=IFERROR(INDEX(A:A,MATCH(ROW(B1),C:C,0)),””)“on cell “D2“.
  • Fill the formulas till the end of list.
Support columns
Support columns
  • Go to Formulas Tab and click on Define Name
Define Name option
Define Name option
  • New Name window will be opened.
  • Give the Name as Country_Name.
  • Put the formula “=OFFSET(Sheet1!$D$2,,,COUNTIF(Sheet1!$D:$D,”*?”)-1)” in “Refer to” box.
New Name Window
New Name Window
  • We will create our searchable drop-down on “Sheet2“.
  • Go to Visual Basic Editor (Press Alt+F11)
  • Double click on “Sheet2
  • Paste the below given code.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.Calculate
End Sub

Below is the snap set of Visual Basic Editor

Visual Basic Editor
Visual Basic Editor
  • Now go to the “Sheet2” and select the range of cells where you want to create searchable drop-down.
Selection on sheet2 where we will create searchable drop-down
Selection on sheet2 where we will create searchable drop-down
  • Open the data validation dialog box (use shortcut key –  Alt+D+L)
  • Select the list in allow drop-down.
  • Click in “Source” box and press F3 to open the Paste Name window.
Data Validation Window
Data Validation Window
  • In the Paste Name window select the Country_list Name and click on OK
Paste Name window
Paste Name window
  • Name will be reflected in the Source box.

Note: You can type the name in the source box directly like “=Country_list

 

Data Validation Window
Settings tab of Data Validation Window
  • Now go to the Error Alert tab of data validation window.
  • Uncheck the “Show error alert after invalid data is entered” check box.
  • Click on OK button.

 

Error Alert tab of data validation window
Error Alert tab of data validation window
  • Searchable drop-down has been created. if you click on drop-down arrow, it will show the complete list of countries.

 

Countries list in drop-down
Countries list in drop-down
  • If you will type some keyword and click on drop-down arrow, then it will show the related list.
Searchable Drop-down
Searchable Drop-down

 

Click here to download this excel file

 

Watch the video tutorial:

Visit our YouTube channel to learn step-by-step video tutorials

Youtube.com/@PKAnExcelExpert

 

PK
Meet PK, the founder of PK-AnExcelExpert.com! With over 15 years of experience in Data Visualization, Excel Automation, and dashboard creation. PK is a Microsoft Certified Professional who has a passion for all things in Excel. PK loves to explore new and innovative ways to use Excel and is always eager to share his knowledge with others. With an eye for detail and a commitment to excellence, PK has become a go-to expert in the world of Excel. Whether you're looking to create stunning visualizations or streamline your workflow with automation, PK has the skills and expertise to help you succeed. Join the many satisfied clients who have benefited from PK's services and see how he can take your Excel skills to the next level!
https://www.pk-anexcelexpert.com

Leave a Reply