Quickly create searchable or autocomplete drop down list in Excel
Kutools for Excel
A drop down list can help to fill in a cell quickly without manually typing, but, if there are a long list items, you need to scroll up and down in the list to search the proper item. In this case, using a searchable or autocomplete drop down list will be a good choice for you, so that when you type the corresponding characters, only the matching items are displayed in the drop down list as below demo shown.
With the Kutools for Excel’s Searchable Drop-down List feature, you can create a list of searchable drop down list quickly and easily in Excel.
To quickly create searchable or autocomplete drop down list, please do with the following steps:
1. First, you should create the drop down list in your workbook as below screenshot shown.
Tips: To create the drop down list, you can apply the Data Validation feature in Excel or Create simple drop-down list of Kutools for Excel.
2. And then, click Kutools > Drop-down List > Searchable Drop-down List > Settings to configure the settings for the Searchable Drop-down List feature, see screenshot:
3. In the Searchable Drop-down List dialog box, please do the following operations:
(1.) In Apply to section, you can select the scope for applying this Searchable Drop-down List feature.
- A: Specified Ranges: only work at the selected ranges, this can be applied for one selection or more selections.
- B: Specified Scope: to apply this Searchable Drop-down List feature in current worksheet, current workbook or all workbooks.
(2.) In the Options section, you can check the operations as you need.
- A: Match only the beginning of the word: if you check this option, only the items that begin with the typed character are displayed, and the first displayed item will be autocompleted into the list box; if uncheck this option, all items that contain the typed character will be displayed.
- B: Case sensitive: if you check this option, only the items matched the case of the typed character are displayed, if uncheck this option, all items that contain the typed character will be displayed without case sensitive.
(3.) In the Mode section, choose the mode for adding drop-down list items to cells.
- Append: If you choose this radio button, multiple searched items (including duplicates) will be allowed to be added to a cell. After adding the first searched item to a cell, if you perform a new search in that cell again, the new searched item will be added to the end of the existing one.
- Modify: If you choose this radio button, the later added item will overwrite the existing one. Only one item is allowed to be displayed in the cell at a time. See the below demo:
(4.) Click OK.
4. Now, click Kutools > Drop-down List > Searchable Drop-down List > Enable Searchable Drop-down List to activate this feature, see screenshot:
5. And now, click a drop down list cell, a list box with all selections is displayed, now you just need to type a specific character into the textbox, and all corresponding items will be shown at once, and then, you can click the needed item to insert it into the cell, see screenshots:
Tips: To select the needed item, you can also use the up and down arrow in the keyboard directly.
Tips: In the popped out list box:
: Click this button will open the Searchable Drop-down List Settings dialog, you can change the settings as you need.
: Click this button will close the searchable list box.
1. To disable this feature, you just need to click Kutools > Drop-down List > Searchable Drop-down List to disable this feature.
2. This feature supports to keep the activation status and applying scope you have specified in the last closed workbook.
3. This feature only works if you have installed Kutools for Excel.
The following tools can greatly save your time and money, which one is right for you?
Office Tab: Using handy tabs in your Office, as the way of Chrome, Firefox and New Internet Explorer.
Kutools for Excel: More than 300 Advanced Functions for Excel 2021, 2019, 2016, 2013, 2010, 2007 and Office 365.
The functionality described above is just one of 300 powerful functions of Kutools for Excel.
Designed for Excel(Office) 2021, 2019, 2016, 2013, 2010, 2007 and Office 365. Free download and use for 60 days.