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.
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.
This will open the data validation window. This window is where you can define how your drop-down list will behave.
Here is how you need to set up the items in this window: 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.
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.
Repeat this for any other drop-down list columns you’ve created. When you’re done, you’ll see that the columns are hidden.
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.
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 rangeText: Make sure entered text contains or doesn’t contain certain text, or is a valid email or URLDate: Keep the entered date within or outside of a specific range, or simply confirm it’s a valid dateCustom formula is: Use custom Google Sheets formulas to validate entered dataCheckbox: 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.
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!
