Reduce scrolling in data validation


Given a large number of entries in the source of data validation, it may become cumbersome to scroll down the list to select a desired value.  It would be ideal to have the user type the first alphabet and be taken to all words which start with that alphabet.

The drop down list in cell C2 is the normal drop down list created through data validation.  This list does not have your desired functionality.  However, the drop down list in cell E2 has the desired functionality.  In cell E2, type the first letter of the any alphabet and then click the drop down button in the cell.  You will be taken to the first alphabet int he list.

You may refer to my solution in this workbook.

Leave a Comment

Your email address will not be published.


  • Hi i want to have modification in this. 1. If the list is on another sheet (other than sheet on which it is used for data entry), then how it works. 2. The list appearing on typing first letter, i want to extend this facility for typing further character also. i mean if i type ‘H’ first then list starting with ‘H’ letter will be available for making choice, but if i type ‘e’ after ‘H’, then the list should be further reduced to only those items having starting characters ‘He’ only. How this can be achieved. Also mind well that this list is on another sheet and data validation is used on another sheet.

    • Hi,

      In my example, if range A2:A194 and G2:N194 is on another worksheet, then just assign a name to range N2:N194 (say dummy). In cell E2, create a Validation list with the name dummy.

      I do not have a solution to the second problem.

  • Ok, but the way in which it is formulated, if some simple 1 formula is possible, pl. suggest the simplest method to derive final things of N colmn. I will be highly obliged.