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 a free tool.

Skill level: Beginner

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

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.

no-built-in-way-to-search-data-validation-drop-down-lists-in-excel

So I developed a very simple add-in that helps solve this problem…

Video Overview of List Search

Click here to download the List Search Add-in

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

Search Validation Lists with List Search

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.

Here is a list of the features.

  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

Click here to download the List Search Add-in

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.

  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.

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! 🙂

169 comments

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

  • Hi,
    I have two problems.
    One. After close Excel and reopen, XL Campus is not on toolbar. I must select in add-on, uncheck, confirm, and recheck again to see it.
    Second. When I use List search tool it is open not after the cell, but on bottom of screen, behind windows taskbar. It is hard to move on it higher.

  • This is fantastic, thanks ! Great work… Very helpful. I have a quick question, how will udpates in MSexcel or Widnows OS, affect this plugin ? Please share your thoughts. Can I safely use this for, say 2 years, or as long as I am on the same OS etc? What are some of the things that will make this plugin not usable anymore ?

    Thanks for your help. One observation is that the plugin needs to be installed everytime I close and open excel, other than that this is working great.

    Thanks
    Bharad

  • Works on Lists Without Data Validation
    Dear Jon,

    Thanks a lot!
    It was working fine until I inserted new items in the upper section of the column. It keeps showing all the unique items(which it was showing previously) except for those newly inserted items. Why is it not loading new items ? My Column list consists of two portion – List above and out of Data Table and List in Data Table connected without blank row. Your List was working fine in this set up. Could you please let me know what i am doing incorrectly?

    Thanks!

    Regards,
    Arun

  • Dear Jon,
    Thank you for this Add-in!

    Sometimes when I select a cell and then click on the List Search button, I get the following error message. What am I doing wrong here?

    Error Creating List
    please select a cell that is not blank or contains a validation list.

    Kind regards,
    Dave

  • Hi,

    I just installed the List Search add-on to my Excel. It works great, however, every time I close Excel the List Search add-on disappears and I have to re-install the add-on. According to your tutorial, it is supposed to stay installed, correct?

  • When I put my cursor on a field in a column and use List Search on the column of data and type in a word, it finds the word in the drop down list, which I highlight and press enter. It doesn’t do any search at all. What it does is replace the field that the cursor was on with the word I typed into the search box. What is going on? I thought it would find the word I highlighted and return all the rows with that name.

  • Hi Jon,

    Thanks for this, saved lots of time already!

    Is there any way to make the List search form’ search through more than 250 items?

    Thanks.

    Regards,

  • This is a great add-in! Thank you!
    I am in the process of making a small income and expense tracking workbook and had the need to do searchable lookup for account information, payee, bank accounts, etc.. This worked wonderfully but i found there were times when I needed to allow an entry not in the dropdown, like Check Payee. I modified the vba code to check the validation information and based on the validation error type I can display yes / no msgbox prompts to continue to write to cell if error type is information or warning and display error message if the cell must be filled from the list. It uses the validation cell information for the error messages or a default message if the cell information is blank in the validation properties. I have also modified the code to display the cell value in the search box if the cell is already populated.
    Once again, THANK YOU!!

    • Hi Preetam,
      I’m sorry to not reply sooner. After you download the add-in you will get access to the download site. There is a video on that site that walks through how to import the add-in into your own files. This is ONLY if you want to distribute/share an Excel file with other users and add the functionality to that file.

      The installation is easier if you are just going to use the add-in on files on your computer. You install it one time, then you can use it on any open Excel file on your computer.

      I hope that helps.

  • Hi Jhon and thanks a lot for this add-in!

    I imported the List Search form in the VBA of a workbook, and the worksheet I have the validation list is usually protected.

    Is there a way to prevent the form from loading (when I click on a cell) when the worksheet is protected and enable the loading (when I click on a cell) when the sheet is unprotected?

    Hope the above makes sense

    Thanks

    Lefkios

  • John, Thank you for the List Search. I use it almost every day.

    1. Have you ever considered having a function added that re-pastes the previous paste to the next cell?

    I am currently using your List Search Program to go down a column of expenses for our year-end financials, and there are many rows that require the same choice. I have to close out of List Search and then manually copy and paste the previous search results into the next 1 to up to 20 rows, re-launch List Search, and then repeat. I would love to continue to keep hitting the enter button, and a repaste auto happens.

    2. Is there a way to assign a key to auto-launch List Search.

    Let me know your thoughts. Thank you again!

    • I was very pleased to find your site.I wanted to thanks for your time for this wonderful read!! I definitely enjoying every little bit of it and I have you bookmarked to check out new stuff you blog post.

  • Hi Jon!
    I have been searching for something like this for a long time. Great job. I think this should have been developed by MicroSoft! I will find several uses for it.

    One thing I noticed is that if you try to drop the search drop down in a cell with a validation range relating to a whole column like range A:A, you get an error. No big issue if you know what you are doing but the VBA code may confuse some users.

  • Thank you so much for this helpful add-in. I’ve been looking exactly for something like this.

    I only have 2 suggestion if you are still updating this.

    1. Allow to click on another cell and close the add-in window.
    (At the moment, If I choose “Open automatically when clicking on a cell with Validation”, when I click on such cell, I will have to close Manually first before clicking on another cell)

    2. There seems to be a bug where after I have gone through a few cells inputting things, when I tried to close the Add-in windows, I have to click “X” a lot of times for it to finally close and be able to click another cell.

    Thank you again.

  • Hello,

    I am getting an error when attempting to use the List Search add in.

    I receive the message :

    Compile error in hidden module : f_ListSearch.
    This error commonly occurs when code is incompatible with the
    version, platform, or architecture of this application. Click “Help” for
    information on how to correct this error.

    I have reinstalled, but still no luck.

    I have Office 365.

    Thanks

    Bob

  • Hi, nice idea very usefull,

    I’ve found that validation like:

    =SI(H11=””,INDIRECT(“Table[Items]”),J11)

    makes evaluate return rNothing

    and i get the message:

    —————————
    Error Evaluating Validation Formula
    —————————
    There is an error with the validation formula. Please fix the error in the Data Validation window.
    —————————
    Aceptar
    —————————

    Is there a workaround for this?

  • Hi Jon,

    This is a very useful tool except I can’t find out how to toggle it off/close it when it isn’t needed? If I select the Close open it still opens every time I click on a cell with data validation. I have a large table with multiple validation lists and if I click on any of the cells with data validation, I have to close list search before I can do anything else. Thanks for all you do!

    • Hi Terri,

      I realize this is a while after your post, but to toggle the tool off when you do not need it, expand the options bar and click on the second to last button. It is a box with an arrow pointing out the top right. This will turn off auto-open. Hope this helps!

  • Error Type Mismatch solution.

    For example, original NewPO list was on tab ‘DataMaps’ cell W8 “YES”. This is just a flag and counts new purchase order requests. So I don’t need a “NO” option in the dropdown. YES in the dropdown gave me Type Mismatch errors.

    When I changed my list to ‘DataMaps’ cells W8:W9, List Search worked. Even though my cell W9 was empty.

    I’m brand new to this app (as of today) and I realize this is a VERY BASIC example, however I hope this helps someone!

  • I love the add-in… very useful for long plant lists among other lists… And I have shared with others in the community…

    The only problem I’m having is the pop-up box for the list search works its way into the bottom of the screen and I can’t look at the options in the search list… so I have to move it after every entry on the bottom of the spread sheet… just to view the results….

    Is there a way I can fix it stationary to the top ribbon? or fix it stationary so it doesn’t drift off the page?

    Any advice is greatly appreciated…

    • Hi Nathan,

      Thanks for the nice feedback. The add-in attempts to start the List Search Form under the selected cell, but it doesn’t always work. There are a lot of variables here including the screen resolution, multiple monitors, and resize/maximize state of the Excel window.

      So, I created a version of the add-in that will startup in the center of the screen. You can find this file on the downloads page for List Search when you login to the members area.

      The file is named, “The List Search Add-in for Excel – Startup Center Screen.zip”.

      Just follow the instructions on how to update an add-in on this page within the members area.

      https://members.excelcampus.com/products/the-list-search-add-in/categories/142180/posts/460875

      I hope that helps. Thanks again and have a nice day! 🙂

  • Hi Jon,

    Great add-in. One thing that I have found is that if you have set freeze panes in your sheet and the column you want to use the search on is to the left of the frozen column then when the list-search form displays it is over to the right of the frozen column and looks like it bears no relation to the one you want to search. Is there any way around this?

    Thanks

    Bax

    • Hey Bax,

      I just saw your comment. I don’t know of an easy way around that because there are a lot of variables in trying to determine the startup position of the List Search Form. However, I just added a version of the add-in to the downloads page that will display the form in the center of the screen. See my comment above to Nathan for instructions on how to get it.

      This will at least put the form in the same position on the screen every time it opens.

      I hope that helps.

  • Hi,
    Sorry a bit newbie here.

    What does it mean “You will also need to create or import the code module that contains the macro to open the List Search userform.”

    Which file of the 4 modules is he referring to?
    Regards

    • Hi MA,
      I’m sorry to not reply sooner. After you download the add-in you will get access to the download site. There is a video on that site that walks through how to import the add-in into your own files. This is ONLY if you want to distribute/share an Excel file with other users and add the functionality to that file.

      The installation is easier if you are just going to use the add-in on files on your computer. You install it one time, then you can use it on any open Excel file on your computer.

      I hope that helps.

  • I installed the search list add-in yesterday.
    I liked it. It worked fine.

    Today I reopen Excel, the add in is not showing, if I go to Excel Options, the add in is there.

    How can I make it reappear again on my ribbon.

    Thank you

  • Is it working with Google sheet and if not,, is there are any other solutions to have a searchable drop-down list of Google sheets ?????

  • Hi Jon,

    Fabulous tool you have there! Fills a huge gap in Excel functionality. Great work and would be glad to contribute.

    Thx!

    Renaud

  • this amaizing add in,, make easy to work with data validatin list,, need little bit fix,, when i chose select right in cell active,, this made error, and must be reinstal the add ins… best for EC

  • This is a perfect program . It works very well on my computer . But I need to cancell it for small list (for example less than 15 row list ) but it wake up for all list . please sned me a solution .

  • This works great- thank you! I would like to know how to skip to the next row without selecting anything if I don’t find results I want in the list. EG when you select from the search results and click the arrow key it goes to the next cell but what if I don’t want to search in that cell? Is there a way to make is skip down again?

  • That is one amazing ad-in. Is there a way to use this on Mac? I’ve managed to install the add-in and it comes in a new XL Campus tab but when I try using it, excel does not allow me to use mouse and constantly makes error sound even when pressing on empty cells.

    Thanks

    Dainius

  • Thank you so much sir that is exultant drop down list I am looking for log log time this kind drop down list finlly I find in thanks you

  • I recently got Formatting Shortcuts and List Search. I use a number of Excel 2016 properties. They show up in Excel, but now they are blank using (win 10) windows explorer. Any thoughts?

  • Thanks for LIST SEARCH FORM.I use it to fill up my RECIPE COSTING FORM in EXCEL.I have to fill up two column. One is ITEMS and another is QTY.I could fill up ITEM column by LIST SEARCH FORM. But it doesn’t allow me to type in another column, when LIST SEARCH FORM is active. I have to every time ESC it, then fill up the QTY column, after that active it. Would you make it easy please !

  • wow, wow and wow!!!!
    I cant believe….it works perfect…such an amazing feature….
    Thanks a lot Mr.Jon….
    Microsoft should have included this feature…
    cant explain in words..again Mr.Jon thank you very much….
    “I am loving it”

  • This add inn deserves to be part of the next version of excel.

    Thanks for developing and maintaining this blog

    Bhatt K V
    Kenya

Search
Generic filters
Exact matches only
Filter by Custom Post Type

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly