How to Search Data Validation Drop-down Lists in Excel

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.

Skill level: Beginner

how-to-search-data-validation-lists-in-excel-cover-640x360

New Feature to Search Dropdown Lists

In 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 Tutorial

Watch on YouTube & Subscribe to our Channel

How to Search Data Validation Lists

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.

New Excel Feature - Search Dropdown Lists

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 Bugs

As awesome as this new feature is, there are a few things I'd like to be improved.

Enter Key Behavior

The 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 Matches

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

Availability

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

no-built-in-way-to-search-data-validation-drop-down-lists-in-excel
Watch on YouTube & Subscribe to our Channel

Click the links below to jump down to the feature update videos.

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

List Search Add-in Drop-down List Contains Data Validation List of Items

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.

list-search-results-narrowed-down-as-you-type-data-validation-list

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.

enter-key-or-input-value-button-to-enter-value-in-cell-list-search

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 & Features

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

list-search-add-in-options-sort-and-direction-settings
  • Select Next Cell – After pressing the Enter key or Input Value button, the cell below the active cell is selected.  This behavior can be changed in the direction drop-down menu.
    • Down – selects the cell below the active cell.
    • Right – selects the cell to the right of the active cell.
    • None – does not change the selection.
    • Close – closes the List Search window.
    • Paste – Copies the input value to the clipboard and pastes it to the active cell using the VBA SendKeys method.  The List Search Window closes.  This is the only option that retains the undo history in Excel.
  • Sort Order – The drop-down list can be sorted in ascending (A-Z), descending (Z-A), or original order by pressing the toggle buttons in the options menu.  This only sorts the list in the List Search window.  It does not sort the data validation list in the cell.
  • List Info – The Info button displays additional information about the drop-down list.  It currently displays the total number of items in the list.
  • Create List of Unique Values – A new button has been added that copies the contents of the drop-down list to the clipboard.  You can then paste the list to any range in the workbook.  This is a fast way to create a list of unique values when you use List Search on a cell that does NOT contain data validation.  You can also filter the list by typing a search, then copy the filtered list to the clipboard.

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 Validation

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

list-search-works-on-cells-that-do-not-contain-validation

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 Update

I 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
  1. Added a “Paste” option to the directions list.  This will copy the input value to the clipboard and paste it to the activecell.  The Paste options uses the SendKeys method in VBA to perform the paste.  This means the Undo history will NOT be cleared when using the Paste Option.
  2. Settings for the Options Menu and Input Direction Drop-down are now saved to the registry.  Your preferences will be saved and loaded when you open Excel and the add-in again in the future.
  3. Added enhancements for Excel Tables. When the activecell is in a Table and the cell does not contain validation, a unique list of values will be loaded and exclude the Table headers and total row.
  4. Added Copy List feature that copies the contents of the drop-down list to the clipboard. This feature is used to create a list of unique values from a column/table when the activecell does not contain validation. It also works when the list is filtered with a search term to only copy filtered results.

April 2017 Update

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

  1. It added the Auto Open feature to automatically open the form when a cell that contains data validation is selected. You can toggle this option on/off with a toggle button in the options menu.
  2.  The add-in now works with data validation created by formulas (OFFSET & INDEX) and comma separated lists.  It should work with all types of data validation lists.
  3. Updated Escape Key behavior to close the List Search window.  If there is text in the search box, then Escape clears the search box.  If the search box is empty, then Escape closes the form.

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.

List Search Add-in Free Download 640x360

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.

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.

  1. Send them a link to this page to download and install List Search on their computer.  They will be able to use List Search on any Excel file they have open on their computer.
  2. Import the List Search userform to the VB Project in your Excel file.  You can add the List Search form to any of your workbooks.  This must be a macro enabled workbook.  You will also need to create or import the code module that contains the macro to open the List Search userform.  Then add a button to the worksheet or ribbon that opens the form.  There is a video on the download site that walks through this entire process.  Once you opt-in to download the add-in you will receive a free account to access the Excel Campus Members Area and the download site.

Available on Hero Tools

The List Search add-in is also available on our Hero Tools Add-in.

Hero-Tools-Tab-in-Excel-Ribbon

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 Lists

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

216 comments

Your email address will not be published. Required fields are marked *

  • Hi guys, love the add-in, however it does not appear in the ribbon when I reopen Excel O365. I need to disable the add-in, add it again and it then appears.
    Do you know if I need to do something to make it appear every time?

    Thanks

  • Hi, Jon!
    How to use the List Search Add-in for depending (cascade) drop-down list (with INDIRECT() )?

    By clicking I get: “Error Evaluating Validation Formula. There is an error with the validation formula. Please fix the error in the Data Validation window.” and stop “List Search Add-in”.

    Have you any answer?

    • Control Panel > region and language > change to English (United Stated). Then your problem will be fixed.

  • Loving this tool – thanks very much.

    I am having trouble with a list about 90,000 entries long. I know a value is in the list but the list search function is not finding it.

    Is there a maximum length list it will search?

    Any thoughts appreciated.

  • Thank for a great tool.
    By the way, please advise me how to edit the appearing position of combo box. Sometime, I choose the low cell, combo box appear out of screen.

  • Hi Jon,
    This addin saved my life. It is the only addon available in the world to do such job. I have one suggestion though,
    Is it possible to display combo box in cell only (like google sheets). Just select the cell having data validation list and start typing, As soon as we select the cell it becomes combo box and shows the list of items filtered as per typed characters. No need to click and open combo box in different window, (I known addon has a feature to open automatically if it detects the data validation in list, but in that case it opens for every small and long list, whereas we only need it when there is a long list). if somebody needs to change some settings it could be done by going to list search addon settings.

    There is one such video https://www.youtube.com/watch?v=eB7aedJ9RVI

    Such feature should be there by default in MS Excel, but excel development team is damn lazy. Thanks to people like you who saved tons of people facing issues.

    • Great Idea,
      Jon you should consider this seriously. This can be a forward and unique step towards better excel. No such solution available anywhere, you can do this. Comeon.

  • This is the right site for everyone who really wants to find out about this topic.
    You understand a whole lot its almost hard to
    argue with you (not that I actually would want to…HaHa).
    You certainly put a new spin on a subject that’s been written about for ages.
    Wonderful stuff, just great!

  • This add in made life so much easier and made reporting much easier for my employees. I had multiple lists of about 1000 entries and this sorts it perfect.

    The issue I am facing though is that in Office 365 the add on, after being installed vanishes the next time I open the sheet. So everytime my sales staff opens the sheet the next week for their resports they have to install the add in all over again. Is anyone else facing the same issue. Is there any way to resolve this?

  • This is the perfect addin for this purpose. i have tried several but this one is the father of all. Is it possible to append a keyboard shortcut like the way we do with save (CTRL S) or new (CTRL N)
    It will make it even more fancy. i love your geneous

  • Is it compatible with Office 365? it give me an error, the function btnListSerch_onAction is not available or macro is not enabled

    Thanks for this grate tool

  • it would be nice if there woud be an option on pressing enter that insted of entering the info in the cell it should find it for you

  • Can list search be used on a spreadsheet housed in MS Teams? My team would not be opening the spreadsheet in the app. They have to use the MS Teams interface.

  • Did install the search list incorrectly – I would like the ExcelCampus menu to always show up/be there when I open up Excel, even if, there are no list. Even if it is a new workbook.

  • Jon,

    I wanted to download the List Search Add-in, it keeps looping and asking me to register taking me to the page where the video plays and where you have to put your email and contact info, however, never actually gives a link to download. I am in Excel Elevate program. Please, assist.

  • I previously added your add-in to my Excel, but now the Excel Campus tab is gone. It stills shows the add-in as being activated under the Add-ins under options.

  • Really terrific!! So helpful to our nonprofit staff trying to search through client names each month to satisfy funders. It made me look like a hero, so thank you. I would also love to use this on timesheets to make it easier for staff but the List Search list does not retain the same formatting as the data validation list. It presents the times (9:00, 9:15,…) as decimals. Is there a way to change what I’ve done to make that work? Thanks again very much!!

  • Hi Jon,
    Iconic solution for the shortfall of Excel’s built in drop down options, thanks a lot for such a wonderful tool.

    I would like to share some of my observations, i have read the comments section but couldn’t find someone who has reported these (May be it happens only in my workbook).

    1. Once i have updated couple of cells using the list search i press Esc key to unload the form, i have to repeat pressing the Esc key continuously (the no of times i used the tool to update value those many times i have to press the Esc key).

    2. Move_Form actually doesn’t fit with the activecell when the worksheet is having freeze panes or some rows and columns were hidden already.

  • In list search Can an array column of other sheet be the source of data instead of the data from column of input?

  • Hi Jon,

    First of all, I love using your list search, and it works like a charm in excel! However, when uploading my file to Microsoft OneDrive, The add-in does not work and does not get uploaded. Is there a method to get list search to work in OneDrive?

  • We use a lot of data validation lists as well as filters in our organisation. The list search is appearing on every cell that is filtered and every cell with data validation. Is there a way to disable the list search for certain conditions – such as when filters are being used.

    • Same, I uninstalled the add-in. My Excel freezes a lot and the startup is very slow. Looking forward to a solution, I really like this add-in, it helps me a lot.

  • A great tool, but can we set a determined range in list of two columns and bound only one column.
    i.e:- I want to search an items list from a named range which contains two columns 1st for Item ID & 2nd for Item Name , and when we press enter then save only item ID to cell.

  • How about ten digits instead of leters; start with zero: 0#########, where ‘#’ is any integer from 0 to 9, is this possible?

    Thank you.

  • If you share the worksheet with others, do they have to have the add-in on their computer to be able to use it?

  • Hi, I have been working on a large spreadsheet which has numbers, I am wanting to know how I can pull out the information from my spreadsheet breaking down the statistics, i.e which numbers are consecutive pairs, and statistics on number frequencies, common pairs and triplets. I am not sure which formula or function I need to use. Please can you direct me, to the right way? I have learned how to use excel spreadsheets on my own, as I am not working and I am 60 yrs old.

  • Love your add-in and look forward to getting the new search feature in due course.

    In the meantime, is there an option make your List Search add-in stay where it is dragged to? For example, when opening the add-in the control is located over my table, I drag it to the side, search for a value and when I click to add it to the table the control promptly moves back over the table.

    I tried the above on the ‘Drop_Down_List_Examples.xlsx’ file you offered for download.

  • John you are always my go-to when I can’t solve a problem on my own. I was wondering if you could add a feature that would add ask if you wanted to add an item to the data validation list if it is not found during search, then sort A_Z…..
    Thanks again you are an awesome mentor

  • Hello!

    “In January of 2022, Microsoft released an update that lets you search dropdown (data validation) lists in the desktop version of Excel.”

    Does anyone know which release he is referring to?
    I don’t find this update was it postponed or was it really released?

    Thanks!

    • Hi JP,
      I added a note above that this feature is currently being flighted out on the Insiders Beta channel for Microsoft 365. Once it’s been fully flighted and approved, it will then roll out to the other channels. I don’t have any information on the timing for that.

      I hope that helps. Thanks! 🙂

  • Hi Jon,
    I am trying to find solution to drop down list validation updates: in my file I switch language that affects the change of the language of the original drop down list. The problem is that the selected items in drop down list remain unchanged, i.e. in original language. Do you have any clue how to automatically update the value of the drop down list based on the language selected?
    Thank a lot!

  • The List Search Add-in is stopped work in MBP.
    Run-time error ‘1004’:
    Method ‘Index’ of object ‘WorksheetFunction’ failed

  • Hi! If I have an add-in installed, but another user has not, would that user be able to use the updated dropdown if I send him the file with the dropdown? Thanks!

  • are you able to turn off the search function on small picklist workbooks where it is not really needed. Works fine on a large workbook.

Generic filters
Exact matches only

Excel Shortcuts List

keyboard shortcuts list banner

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

Join Our Free Newsletter