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.

 

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.

 

 

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.

 

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

In the Ribbon, select Data > Data Validation.

 

 

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

 

 

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

 

 

Click OK to confirm and exit the Data Validation window.

 

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

 

 

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.

 

 

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.

 

 

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

 

 

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.

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.