AutoComplete With Data Validation in Excel & Google Sheets

This tutorial demonstrates how to autocomplete with a data validation drop-down list in Excel and Google Sheets.

 

 

Data Validation and AutoComplete

Creating a drop-down list in Excel using data validation is useful in restricting the data input allowed. However, if the drop-down list has many items, scrolling down the list looking for the correct entry could be cumbersome. Unfortunately, drop-down lists don’t autocomplete based on the source list when you start typing. (Note that Office Online currently has built-in autocomplete functionality for drop-down lists, but desktop versions do not.)

The following steps create a neat trick as a workaround to approximate drop-down autocomplete in desktop versions of Excel.

1. The first step is to type the list of entries you want in the drop-down list into Excel. This list needs to end in the cell directly above the heading of the drop-down list.
   Remember: There can’t be an empty cell between this list and the rest of the data.

 

 

2. Once you have created the list of items, you can create a drop-down list in a cell below them with data validation.
   Click in the cell directly below the last item on the list (i.e., C10) and type a heading for the drop-down list.

 

 

3. Then, in the cell below that, create the data validation rule.
   In the Ribbon, go to Data > Data Validation.

 

 

4. In the Data Validation window, choose List in the Allow drop down, and click on the arrow next to the Source box.

 

 

5. Select the range containing list items (C1:C9) and press ENTER.

 

 

6. Click OK to confirm and exit the Data Validation window.

 

 

Now, there’s a drop-down list in C11 with the names above as items.

 

 

7. Since the source list is directly above the drop-down list, when you start typing one of the names, Excel automatically suggests a name from the list. Just press ENTER or TAB to confirm the entry.

 

 

8. To protect the values in the drop-down list (so that the user doesn’t delete or amend values by mistake and to make the worksheet look cleaner!), hide the rows that contain the source data for the list.
   Select the rows that contain the items in the drop-down list. Right-click the selection and click Hide.

 

 

Autocomplete still works after the list is hidden.

 

 

The method described above lets you “dupe” Excel into allowing autocomplete for drop-down lists, but you could also use VBA code to get a combo box to function with autocomplete.

AutoComplete With Data Validation in Google Sheets

In Google Sheets, you can create a drop-down list with the source list anywhere. It can be next to the drop-down list, or even in a different sheet.

 

 

Regardless of where you choose to put the source list, Google Sheets autocompletes when you start typing in a cell with the drop-down rule. It’s built in, just like Office Online’s drop-down autocomplete.