Excel: Combine Validation with AutoComplete

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Problem: The Validation dropdown is horrible for keyboard people. You can't quickly jump to an item in the dropdown.

Strategy: Combine AutoComplete with Validation. Several viewers of the Learn Excel from MrExcel podcast sent in this idea when I complained about the lack of keyboard support for validation. Thus, I am guessing that the trick must be fairly widespread.

Say that you want to enter products in column D.

Insert several rows above the D1 heading and store the list above the heading. Set up the validation below the heading.

When someone who is a mouse person comes along, they will open the dropdown and use the mouse as usual.

  1. Validation works with the mouse.

    If someone who is a keyboard person comes along, they can start typing the entry. The AutoComplete will offer an item from the list above the heading.

  2. AutoComplete works for keyboard people.