If you’re working with others on a shared Google Sheets file, sometimes people can enter unexpected data or something that breaks a formula. One way to ensure everyone enters the data you want is to provide it for them in a drop-down validation list.
How to Create a Drop-Down List
A drop-down list is a great way to ensure the data people enter into your form, application, or spreadsheet is exactly what you’re expecting. It also provides a much faster way for people to input that data since they’re selecting from a pre-configured list you provide.
The first thing you need to do is open up your Google Sheets file and select the cell(s) for which you want to use a drop-down list.
RELATED: 5 Google Sheets Features You Should Know
Next, open the “Data” menu and select the “Data Validation” command.
From the Criteria drop-down, choose either “List From a Range” or “List of Items.”
List From a Range: A list of values that have been selected from other cells in the same or a different sheet. For example, if you want to use the values in cells B1-B9 on sheet 2, you’d type Sheet2!B1:B9 to have the data contained in them appear in the drop-down list, or by selecting any of the cells from your Sheet directly. List of Items: A list of predetermined data items. This can be either text or numbers, and you’ll type each value yourself, separating them by commas (and no spaces). This option does not allow you to insert data directly from other cells.
Here, we’re using the “List of Items” option and providing several numeric choices.
After you’ve entered the data you want to appear in a drop-down list, make sure you have “Show Drop-Down List In Cell” option enabled or else the values won’t appear in the selected cells.
You can also select what happens when someone tries to enter a value that isn’t on the list. The “Show Warning” option lets them enter the invalid data, but marks it in the sheet (we’ll look at how in just a bit). The “Reject Input” option prevents them from entering anything that’s not on your list.
And finally, you can enable the “Show validation help text” option to give people some indication about what they can choose in the cells. After selecting the option, type whatever instructions you want.
Go ahead and click “Save” when you’re done.
How to Use Your New Drop-Down List
When you’re done, anyone using the sheet can click the drop-down arrow in those cells and choose a value from the list.
If you selected the “Show validation help text” option, that text pops up whenever anyone selects one of the validated cells.
If someone enters a value that does not match something on the list and you have the “Show Warning” option turned on, the invalid data is marked in the cell.
Hovering your mouse over it shows why it’s marked.
If instead, you have the “Reject Input” option selected, people will get a warning like this one when they try to enter anything that isn’t on your list.
If you need to remove or modify any of the items from your drop-down list head back to Data > Data Validation to edit any items from the lists you’ve created. Removing the list completely is as easy as clicking the “Remove Validation” button located at the bottom.
