How To Find Hidden Data Validation List In Excel

Bottom line:Learn a fast and like shooting fish in a barrel style to search whatsoever data validation list or in-cell drop-down listing with the new Excel feature orcostless tool.

Skill level: Beginner

New Characteristic 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.

Information technology's a feature I've been waiting on for a long fourth dimension, and I'm happy it'due south hither!

I originally wrote this post when the feature wasn't bachelor, and it contains info on an add-in I adult chosen Listing Search, that allows you to search driblet down lists in cells.

If yous are NOT on the latest version of Excel for Microsoft 365, then the add-in will still be a great solution for yous. 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.

Y'all can select a effect with your mouse or use the arrow keys and printing 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 tin can encounter that the results include phrases like Fuji Apple tree and Granny Smith Apple tree, which contain the search term but don't showtime with it.

A Few Bugs

As awesome as this new characteristic is, there are a few things I'd similar to exist improved.

Enter Key Behavior

The kickoff is the enter key behavior. After yous discover an item and hitting Enter to input it the cell, the same prison cell remains selected. You must press Enter again to go to the next cell.

Now, this is NOT technically a issues. The same behavior exists if y'all use Alt+Downwards Pointer to open the list and find an item with the arrow keys.

However, I think information entry would exist faster if the Enter cardinal followed the same behavior it does when you lot 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 back up partial word matches.

For example, my list contains the discussion Arrowroot. If I search "root", no results are returned.

Partial give-and-take matching does exist in other areas of Excel similar the search box in the filter dropdown menus. And then hopefully it will make it'south manner to data validation lists as well.

It will be very useful in a lot of scenarios, especially when your list contains office numbers, telephone numbers, or account codes that all start with the same prefix and y'all want to search for text/number within the full value.

Availability

This new characteristic to search dropdown lists is currently available on the Beta Channel of Excel, which is function of the Office Insiders programme. Office Insiders is free for all Microsoft 365 subscribers and yous can learn more nearly information technology here.

Annotation that the characteristic is currently existence flighted out, and then you might not have information technology yet even if you are on the Beta Channel.

It will ringlet out to the other Microsoft 365 channels in the coming weeks/months.

The search feature is likewise available on the web/online version of Excel. It was released for that version in 2021 and I covered information technology in my previous post and video on 21 New Excel Features Released in 2021.

What If I'thou Stuck on an Erstwhile Version of Excel?

Older versions of Excel will not receive this new feature. Yet, 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'south since been downloaded over 30,000 times.

The add-in contains several additional features for sorting, exporting lists, Enter central behavior, fractional word matching, car open up, and more than. And then if you need that partial discussion match searching, you can use List Search on any version of Excel.

Video Overview of Listing Search

Watch on YouTube & Subscribe to our Channel

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

• November 2016 Update
• April 2017 Update

Search Validation Lists with List Search

The List Search Add-in makes it fast and easy to search any validation list.  It likewise works with lists of data that do non contain data validation cells.

The List Search class contains a driblet-down box that loads the selected cell'southward validation list.  The drib-down box also functions as a search box.  You tin blazon a search in the box and the results will be narrowed downward as you blazon.  This is a Google-like search and the results will include any item that contains the search term.  The item does non accept 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 jail cell.

List Search works on any cell in any workbook.  There is NO special setup required.  Simply select a jail cell, press the List Search button, and start searching the listing.

List Search Options & Features

The List Search Add together-in contains some features that make it super fast to do data entry and work with your lists.  Press the Menu button in the Listing Search window to come across the options.

• Select Next Cell –Afterwards pressing the Enter central or Input Value button, the cell below the agile prison cell is selected.  This behavior can be changed in the direction drib-downward menu.
  • Downward – selects the jail cell below the agile cell.
  • Right – selects the cell to the right of the active cell.
  • None – does not modify the choice.
  • Close – closes the Listing 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 disengage history in Excel.
• Sort Guild –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 but sorts the list in the List Search window.  It does non sort the data validation list in the cell.
• Listing Info – The Info button displays additional information nearly the driblet-down listing.  Information technology currently displays the total number of items in the list.
• Create List of Unique Values – A new push has been added that copies the contents of the drop-down list to the clipboard.  Y'all tin can and then paste the list to any range in the workbook.  This is a fast way to create a listing of unique values when you use Listing Search on a jail 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.

Of import Note: When inputting values to the active prison cell, the only way to retain the Undo History is by using the Paste selection in the Select Next Cell drop-down list.  Listing Search uses macros to input the selected value, and macros typically clear the undo history in Excel when they modify the workbook.  The Paste pick 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 articulate the Undo history in Excel.

Works on Lists Without Data Validation

Listing Search works on cells that do not comprise data validation too.  If you select a cell that does NOT contain information validation and open Listing Search, the drib-down will exist loaded with a list of unique items from the column of the selected jail cell.

This is like to pressing Alt+Downwardly Pointer in a cell to see a listing of values in that column.  Withal, the list does not need to be contiguous.  Even if the column contains blanks, List Search volition still load all the unique values in the current information region or listing.

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

Picket on YouTube & Subscribe to our Channel

1. Added a "Paste" pick to the directions listing.  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 Carte and Input Direction Driblet-down are now saved to the registry.  Your preferences will exist saved and loaded when you open Excel and the add-in once more in the futurity.
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 volition exist loaded and exclude the Table headers and total row.
4. Added Copy List characteristic that copies the contents of the driblet-down listing to the clipboard. This feature is used to create a listing of unique values from a column/table when the activecell does not incorporate validation. Information technology besides works when the listing is filtered with a search term to only re-create filtered results.

April 2017 Update

Based on your awesome feedback and requests, I'yard 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 theMotorcar Open feature to automatically open the form when a cell that contains information validation is selected. Yous tin can toggle this pick on/off with a toggle button in the options menu.
2.  The add-in at present works with data validation created by formulas (Commencement & INDEX) and comma separated lists.  Information technology should work with all types of information validation lists.
3. Updated Escape Key behavior to shut 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 grade.

In the video I too showed some cells with drop-down push icons next to them, even though the cell was not selected.  Bank check out my commodity on how to make the validation list drop-downwardly buttons always visible to learn more than about this technique.

Download the Listing Search Add-in (information technology'southward Free!)

The List Search Add-in is complimentary to download and use.  The VBA code is besides open source so you can alter it for your needs.  This is also a groovy fashion to acquire how macros and add together-ins piece of work if you lot are learning VBA.

Note: You volition create a free account for the Excel Campus Members site to access the download and any futurity updates.

The download site as well contains installation instructions and videos.

How Can My Co-workers Use List Search?

The List Search Add together-in is installed on your calculator, and simply you will exist able to run across the XL Campus tab and utilize List Search.  If you want your co-workers to be able to use List Search at that place are two ways to go about information technology.

1. Send them a link to this page to download and install List Search on their computer.  They volition be able to employ List Search on whatever Excel file they have open on their figurer.
2. Import the List Search userform to the VB Project in your Excel file.  Y'all can add the List Search grade to any of your workbooks.  This must be a macro enabled workbook.  You will too need to create or import the lawmaking module that contains the macro to open the List Search userform.  And so add a button to the worksheet or ribbon that opens the form.  There is a video on the download site that walks through this unabridged process.  Once you opt-in to download the add together-in you volition receive a costless account to admission the Excel Campus Members Area and the download site.

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 yous fourth dimension with your everyday Excel tasks. It will help you automate processes with writing formulas, building pivot tables, filtering information, tabular array of contents, navigating workbooks, appointment picker, and and then much more than.

Acquire more about The Hero Tools Add together-in

How Can We Make List Search Meliorate?

I promise the List Search Add-in saves you some time searching data validation lists.  The ultimate goal is to get in faster to discover the value we are looking for in long lists of data.  Delight leave a comment below with any questions or suggestions.  Thanks! 🙂

Learn More than about Dropdown Lists

Information validation lists are a nifty way to control the values that are input in a cell.  These driblet-downwardly lists also allow the states to choose options that can drive fiscal models, reports, or dashboards.

• You can observe my complete tutorial for setting upwardly data validation lists here.
• Then yous can learn how to make them dynamic here.
• And you tin find out how to make them dependent on one another here.

However, at that place is no built-in manner to search the validation listing in Excel.  It tin exist difficult to scroll through these lists when the drib-down contains a lot of items.  There are some actually cool formula based solutions to this problem, but they crave a lot of setup work for each validation listing in your file.

Source: https://www.excelcampus.com/vba/search-data-validation-drop-down-lists/

Posted by: ashfordtered1955.blogspot.com

Share this post