Combine Validation with AutoComplete

February 05, 2024 - by Bill Jelen

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.

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.

This article is an excerpt from Power Excel With MrExcel

Title photo by Sergi Kabrera on Unsplash