See all How-To Articles

AutoComplete With Data Validation in Excel & Google Sheets

This tutorial demonstrates how to use 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 a large number of items, scrolling down the list looking for the correct entry could become cumbersome.   This is due to the fact that the drop down list does not automically auto complete based on the items that are contained within the list.   To enable the list to auto complete, we can use a neat shortcut trick that will solve this problem.

The first step is to type the list of entries that we wish to have in our data validation drop down list into Excel.  This list needs to end in the cell DIRECTLY above the heading of the drop down list.  There can not be an empty cell between this list, and the drop down list.

 

AutoComplete source list

 

Once we have created the list, we  can then create our data validation drop down list.

Click in the cell directly below the last item on the list (ie C10) and type in the heading that you require for your drop down list.

autocomplete headings

 

Then, in the cell below that, we can create the drop down validation.

In the Ribbon, select Data > Data Validation.

 

autocomplete ribbon

 

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

 

autocomplete data validation 2

 

Select a range of cells with items (C1:C9) and press Enter.

 

autocomplete source

 

Click OK to confirm and exit the Data Validation window.

autocomplete data validation

 

You can now select a name from the drop down list in C11.

 

autocomplete dropdwon

 

Now, due to the fact that the source list is directly above the drop down list, if you were to start typing one of the name in the source list, Excel will automatically start suggesting a name from the list.  You can then just type Enter or Tab to confirm the entry.

 

autocomplete suggestion

 

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 more appealing!), we can hide the rows that contain the source data for the list.

Select the rows that contain the items in the drop down list and then click the right-mouse button, and click Hide.

 

autocomplete hide rows

 

You can still then select from the drop down list, and autocomplete will still work!

 

autocomplete hidden

 

AutoComplete for drop down lists will help you to speed up filling in the correct data from the data validation list.   The method described above is one way of enabling auto complete to work.  We can also use VBA code to enable this to happen.  However, at the moment this feature is being beta tested by the Microsoft team so that this work around described above may not be necessary in future versions of Excel as it should become a built in feature!

AutoComplete With Data Validation in Google Sheets

In Google Sheets, you can create a data validation drop down list – but the source list does not have to be above the data validation drop down list.

autocomplete gs list

It can be next to the drop down  list, or even in a different sheet from the drop down list.  Regardless of where you choose to put the source of your drop down list, Google Sheets automatically will autocomplete for you when you select an item from the list.

autocomplete gs suggest