Hide used items in drop down list Google Sheets

By Ryan Dube

Last Updated on January 25, 2021

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

Nội dung chính Show

How to Create a Dropdown List in Google Sheets
How to Create a Drop-down List in Google Sheets
How to Hide Data For Your Drop-Down Lists
Other Data Validation Criteria
How to Remove Google Sheets Validation
Post navigation
Featured Articles
How to Find Your Windows 11 Product Key
Backblaze Cloud Backup Review Updated 2021
How to Clear Google Chrome Cache, Cookies, and Browsing History
In-Store Price Matching: How to Get Online Prices while Shopping in the Store
How to Gift a Disney Plus Subscription with a Digital Gift Card

If you want to let someone select from a list of items in a spreadsheet, its 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. Lets take a look at the process and how you can customize your drop-down list once youve 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, well 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:

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 youre done.

Now, youll 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 youll 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 youre 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 youve created. When youre done, youll 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 arent 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, youll 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 users 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 doesnt 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 its 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 dont 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 dont 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!