Google Search Drop-down List

If you want to let someone select from a list of items in a spreadsheet, it's very helpful to learn how to create a drop-down list in Google Sheets.

Creating a drop-down list in Excel is fairly easy. But what if you use Google Sheets instead? To be honest, the process is even easier.

In the case of Google Sheets, you can pull in your drop-down list from a range of cells, or you can type in the full list separated by commas. Let's take a look at the process and how you can customize your drop-down list once you've created it.

How to Create a Drop-down List in Google Sheets

First, before you can create a drop-down list, you need to create the entries that will fill your drop-downs. In this example, we'll create drop-down lists for Age and Gender in a spreadsheet.

Type the contents of the drop-down lists in two columns.

filling dropdown lists

The problem with having these lists in the sheet is that everyone can see them. So a good first step is to hide these by hiding the columns. But, before you do this, you want to set up your drop-down lists.

Select the cell (in this case, Age) where you want to create your drop-down list. Then, select the Data menu and choose Data validation.

data validation in google sheets

This will open the data validation window. This window is where you can define how your drop-down list will behave.

data validation window in Google Sheets

Here is how you need to set up the items in this window:

Select the Cell range field and select the range of cells under the Age column that you want to use this drop-down list.
Set the Criteria drop-down to List from a range, and select the field and select the entire list containing all of the data you want to be included in the drop-down list (in this case the range of ages).
Enable the checkbox for Show dropdown list in cell.
For On invalid data, select Show warning to warn the user if they enter something, not in the list, or Reject input to reject the entry entirely.
Select Save when you're done.

Now, you'll notice in the spreadsheet that all of the cells you set up with data validation contain a drop-down arrow in the empty cell. Select one of these and you'll see the full list displayed in the drop-down list.

Repeat the process above, this time selecting the cells in column C (Gender) column that you want drop-down lists for, and selecting the three cells in column E to fill that drop-down list.

gender data validation

Now you're almost done!

How to Hide Data For Your Drop-Down Lists

Your drop-down lists are working. However, now you have the data for those drop-down lists in full view of anyone who views the sheet. One way to make your sheets look more professional by hiding those columns.

Hide each column by selecting the drop-down arrow on the right side of the column letter. Choose Hide Column from the list.

screenshot of hiding a column in google sheets

Repeat this for any other drop-down list columns you've created. When you're done, you'll see that the columns are hidden.

hide column in sheets

If you need to access those lists to update them at any point, just tap either the left or right arrow on either side of the line where the hidden columns should be.

Other Data Validation Criteria

When you use data validation to create a drop-down list in Google Sheets, you aren't limited to using a column of data to fill in the list. There is another option available.

data validation criteria

If you select the Criteria drop-down list, you'll see all of the following options for validating data. The List of items option lets you manually type out the items you want in the list, separated by commas.

What do all of the other items in the criteria list mean? Remember, this is "data validation", which means whatever you fill in here will "validate" the data that the user types in and will either accept or reject that entry based on your criteria.

You can limit the user's data entry into cells using the following criteria:

Number: Ensure the value is within or outside of a specific number range
Text: Make sure entered text contains or doesn't contain certain text, or is a valid email or URL
Date: Keep the entered date within or outside of a specific range, or simply confirm it's a valid date
Custom formula is: Use custom Google Sheets formulas to validate entered data
Checkbox: Use a checkbox entry in the cell and assign checked and unchecked values

How to Remove Google Sheets Validation

If you decide that you don't want drop-down lists in these cells anymore, you can remove them quickly using the same Data validation window. To do this, select the cells where you want to remove the drop-down lists. Select the Data menu, and select Data validation.

Finally, all you have to do to remove the drop-down lists from these cells is select Remove validation.

remove validation

The drop-down arrows will then disappear from all of the cells you selected.

Using drop-down lists in Google Sheets lets you better control how other people enter data into your spreadsheets. You can keep their entries within the guidelines of what you expect, rather than letting them type in entries that maybe don't make any sense or is incorrect.

Play around with drop-down lists in Google Sheets and see if it improves the user experience for other people who use your sheets. It could even save you time when you enter data into your own sheets!