AutoComplete With Data Validation in Excel & Google Sheets

Written by

Editorial Team

Reviewed by

Laura Tsitlidze

Last updated on January 9, 2023

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


AutoComplete intro


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.


AutoComplete source list


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


autocomplete headings


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


autocomplete ribbon


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


autocomplete data validation 2


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


autocomplete source


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


autocomplete data validation


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


autocomplete dropdwon


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


autocomplete suggestion


  1. 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 hide rows


Autocomplete still works after the list is hidden.


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


autocomplete gs list


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.


autocomplete gs suggest

AI Formula Generator

Try for Free

See all How-To Articles