Skip to main content
 

Quickly create searchable or autocomplete drop down list in Excel

Author: Xiaoyang Last Modified: 2024-11-18

Kutools for Excel

Boosts Excel With 300+
Powerful Features

A drop down list can help to fill in a cell quickly without manually typing, but, if there are a long list of 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.

Kutools for Excel's Make Dropdown List Searchable, Auto-popup feature simplifies the creation of such dynamic and user-friendly drop-down lists in Excel. This tool enables you to quickly set up searchable lists, enhancing data entry efficiency and accuracy.


Benefits of the Make Dropdown List Searchable, Auto-popup feature:

  • Enhanced Efficiency:
  • Users can quickly find and select the desired item by typing a few characters, speeding up data entry and reducing time spent scrolling through long lists.
  • ✔️ Improved Accuracy:
  • Narrows down the list to matching entries, reducing the likelihood of errors during data entry and leading to more accurate data.
  • 🙌 User-Friendly Interface:
  • Makes Excel more accessible and easier to use, especially for those dealing with extensive datasets, improving the user experience.
  • 🎉 Automatically pops up the drop-down list box:
  • This searchable drop-down list feature automatically displays the list box as soon as the user click a cell, showing the relevant options immediately without the need to click a drop-down arrow. In contrast, the searchable drop-down list in Excel 365 requires users to click the drop-down arrow before seeing any options. Thus, our design offers a more straightforward and convenient user experience.
  • 🔍Supports search for characters at any position:
  • Allows for searching using any part of the item's text, offering flexibility in finding options. In contrast, Excel 365's searchable drop-down list is limited to searches that start with the first letter, making our functionality more comprehensive and user-friendly.
  • 💻 Compatibility:
  • Designed to work seamlessly with a wide range of Excel versions, ensuring users can take advantage of this functionality regardless of their Excel version.

Create searchable or autocomplete drop down list in Excel

To quickly create searchable or autocomplete drop down list, please do with the following steps:

Kutools for Excel: With over 300 features, Kutools covers everything from basic Excel tasks to advanced functions that are usually hard to achieve. Enjoy a 30-day free trial!

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.

shot-searchable-drop-2

2. Then, click Kutools > Drop-down List Make Dropdown List Searchable, Auto-popup item, see screenshot:

shot-searchable-drop-4

Note: To use this feature, make sure that you have checked the Enable Advanced Drop-down List option.
shot-searchable-drop-3

3. In the Make the Dropdown List Searchable dialog box, select the cells with the drop down list that you want to create searchable drop-down list. And then, click OK button, see screenshot:

shot-searchable-drop-5

4. Now, click a drop down list cell, a list box with all items is displayed, type the specific characters into the search field, and all corresponding items will be shown at once, and then, click the needed item to insert it into the cell, see screenshots:

shot-searchable-drop-6

Tips:

1. After selecting the item, the searchable drop-down list box will be hidden at once. To display it again, you just need to click the down arrow to expand the drop-down list box; You can also click the icon with four arrows to move the searchable drop-down list box to anywhere of the worksheet.
shot-searchable-drop-7

2. The searchable drop-down list box can be moved and resized.

  • To reposition the searchable drop-down list box, place the cursor in the blank area of the list box's bottom bar, and then drag it to your desired location;
  • To resize the searchable drop-down list box, put your mouse at the bottom right corner of the list box. When the cursor changes to a double arrow, simply drag to resize the list box.
  • Reposition the searchable drop-down list box Resize the searchable drop-down list box

3. You can also use the following shortcuts for doing some operations quickly and easily:

  • Up⬆ and Down⬇ key: use the Up⬆ and Down⬇ arrow in the keyboard directly to select the needed item;
  • ESC key: the current operation will not be saved and this Searchable Drop-down list box wil be hidden;
  • Enter key: the current operation will be saved and close the searchable drop-down list box;
  • Delete key: If no input in the search bar, delete selected cell content directly; if input exists, delete input characters;
  • Ctrl + Up/ Down/Left/Right key: press the Ctrl key along with the arrow keys to move the cursor up, down, left or right without closing the Searchable Drop-down list box.

4. In the popped out Searchable drop-down list box:

  • Click the shot-searchable-drop-10 button to go the Make the Drop-down List Searchable dialog box to make some modifications you need;
    shot-searchable-drop-9
  • Press down the shot-searchable-drop-11 icon to keep the Searchable drop-down list box always visible when selecting the drop-down list cells. Releasing this icon will hide the Searchable drop-down list box whenever you select the drop-down list cells.
  • Press down the Pin icon Release the Pin icon

5. Click the shot-searchable-drop-12 icon to hide the Searchable drop-down list box.


Notes:

1. To clear the searchable drop-down list from the selection, please click Kutools > Drop-down List > Advanced Drop-down List Manager, in the Advanced Drop-down List Manager dialog box, select the searchable drop-down list that you want to clear, and then click Delete button. Finally, click Save button, see screenshots:

shot-searchable-drop-14 arrow-right shot-searchable-drop-15

2. This feature only works if you have installed Kutools for Excel.


Productivity Tools Recommended
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.

Kutools for Excel

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

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase