Bottom line: Learn a fast and easy way to search any data validation list or in-cell drop-down list with the new Excel feature or free tool. Show
Skill level: Beginner New Feature to Search Dropdown ListsIn January of 2022, Microsoft released an update that lets you search dropdown (data validation) lists in the desktop version of Excel. It's important to note that the feature is currently on the Insiders Beta channel for Microsoft 365. It is also flighted, which means not all users on the Beta Channel will have it immediately. Once the feature is fully flighted, it eventually moves to the Monthly and other channels. It's a feature I've been waiting on for a long time, and I'm happy it's here! I originally wrote this post when the feature wasn't available, and it contains info on an add-in I developed called List Search, that allows you to search drop down lists in cells. If you are NOT on the latest version of Excel for Microsoft 365, then the add-in will still be a great solution for you. It also contains some features that are not available yet in the native search feature in Excel. Video TutorialWatch on YouTube & Subscribe to our Channel With the new update, you can now type your search directly in the cell that contains the dropdown list. A list of results will appear that match your search term. You can select a result with your mouse or use the arrow keys and press Enter. The search currently performs a “contains” type search for whole words. This means the item you are searching for does NOT need to start with the search term. In the example below I am searching for apple. You can see that the results include phrases like Fuji Apple and Granny Smith Apple, which contain the search term but don't start with it. A Few BugsAs awesome as this new feature is, there are a few things I'd like to be improved. Enter Key BehaviorThe first is the enter key behavior. After you find an item and hit Enter to input it the cell, the same cell remains selected. You must press Enter again to go to the next cell. Now, this is NOT technically a bug. The same behavior exists if you use Alt+Down Arrow to open the list and find an item with the arrow keys. However, I think data entry would be faster if the Enter key followed the same behavior it does when you enter data or a formula in a cell. Partial Word MatchesAnother issue, and probably a bigger one, is that the search currently doesn't support partial word matches. For example, my list contains the word Arrowroot. If I search “root”, no results are returned. Partial word matching does exist in other areas of Excel like the search box in the filter dropdown menus. So hopefully it will make it's way to data validation lists as well. It will be very useful in a lot of scenarios, especially when your list contains part numbers, phone numbers, or account codes that all start with the same prefix and you want to search for text/number within the full value. AvailabilityThis new feature to search dropdown lists is currently available on the Beta Channel of Excel, which is part of the Office Insiders program. Office Insiders is free for all Microsoft 365 subscribers and you can learn more about it here. Note that the feature is currently being flighted out, so you might not have it yet even if you are on the Beta Channel. It will roll out to the other Microsoft 365 channels in the coming weeks/months. The search feature is also available on the web/online version of Excel. It was released for that version in 2021 and I covered it in my previous post and video on 21 New Excel Features Released in 2021. What If I'm Stuck on an Old Version of Excel?Older versions of Excel will not receive this new feature. However, I created a free add-in called List Search that allows you to search dropdown lists. I originally created this add-in in 2016, and it's since been downloaded over 30,000 times. The add-in contains several additional features for sorting, exporting lists, Enter key behavior, partial word matching, auto open, and more. So if you need that partial word match searching, you can use List Search on any version of Excel. Video Overview of List SearchWatch on YouTube & Subscribe to our Channel Click the links below to jump down to the feature update videos.
Search Validation Lists with List SearchThe List Search Add-in makes it fast and easy to search any validation list. It also works with lists of data that do not contain data validation cells. The List Search form contains a drop-down box that loads the selected cell's validation list. The drop-down box also functions as a search box. You can type a search in the box and the results will be narrowed down as you type. This is a Google-like search and the results will include any item that contains the search term. The item does not have to start with the search term. Once you have selected the item you are looking for, press Enter on the keyboard or press the Input Value button on the form to input the value in the selected cell. List Search works on any cell in any workbook. There is NO special setup required. Simply select a cell, press the List Search button, and start searching the list. List Search Options & FeaturesThe List Search Add-in contains some features that make it super fast to do data entry and work with your lists. Press the Menu button in the List Search window to see the options.
IMPORTANT Note: When inputting values to the active cell, the only way to retain the Undo History is by using the Paste option in the Select Next Cell drop-down list. List Search uses macros to input the selected value, and macros typically clear the undo history in Excel when they modify the workbook. The Paste option is a workaround that uses the SendKeys method to copy and paste the selected value. This mimics what the user would do to copy/paste, and does NOT clear the Undo history in Excel. Works on Lists Without Data ValidationList Search works on cells that do not contain data validation too. If you select a cell that does NOT contain data validation and open List Search, the drop-down will be loaded with a list of unique items from the column of the selected cell. This is similar to pressing Alt+Down Arrow in a cell to see a list of values in that column. However, the list does not need to be contiguous. Even if the column contains blanks, List Search will still load all the unique values in the current data region or list. November 2016 UpdateI published an updated version of the List Search Add-in with a few new features. Here is a video overview of the new features. Watch on YouTube & Subscribe to our Channel
April 2017 UpdateBased on your awesome feedback and requests, I'm excited to publish another update with new features. I share the new features in the following video. Here is a list of the new features in the April 2017 update.
In the video I also showed some cells with drop-down button icons next to them, even though the cell was not selected. Check out my article on how to make the validation list drop-down buttons always visible to learn more about this technique. Download the List Search Add-in (it's Free!)The List Search Add-in is free to download and use. The VBA code is also open source so you can modify it for your needs. This is also a great way to learn how macros and add-ins work if you are learning VBA. Note: You will create a free account for the Excel Campus Members site to access the download and any future updates. The download site also contains installation instructions and videos. How Can My Co-workers Use List Search?The List Search Add-in is installed on your computer, and only you will be able to see the XL Campus tab and use List Search. If you want your co-workers to be able to use List Search there are two ways to go about it.
The List Search add-in is also available on our Hero Tools Add-in. The Hero Tools Add-in is packed with over 100 features that will save you time with your everyday Excel tasks. It will help you automate processes with writing formulas, building pivot tables, filtering data, table of contents, navigating workbooks, date picker, and so much more. Learn more about The Hero Tools Add-in How Can We Make List Search Better?I hope the List Search Add-in saves you some time searching data validation lists. The ultimate goal is to make it faster to find the value we are looking for in long lists of data. Please leave a comment below with any questions or suggestions. Thank you! 🙂 Learn More about Dropdown ListsData validation lists are a great way to control the values that are input in a cell. These drop-down lists also allow us to choose options that can drive financial models, reports, or dashboards.
However, there is no built-in way to search the validation list in Excel. It can be difficult to scroll through these lists when the drop-down contains a lot of items. There are some really cool formula based solutions to this problem, but they require a lot of setup work for each validation list in your file. |