Working with other people on a shared Google Sheets file can sometimes result in unexpected data or something that destroys a formula. One way to ensure that everyone types in the data they want is to put them in a drop-down validation list.
Creating a drop-down list
A drop-down list is an excellent way to ensure that data people enter into your form, application, or spreadsheet exactly matches your expectations. It also provides a much faster method for entering this data, as they select from a preconfigured list that you provide.
First, you need to open your Google Sheets file and select the cells) for which you want to use a drop-down list.
RELATED: 5 Google Sheets ̵
Next, open the Choose command from the Data menu "Data validation" off.
From the Criteria drop-down menu, select either "List from a range" or "List of items".
- list from a range: A list of values selected from other cells in the same or another worksheet. For example, if you want to use the values in cells B1-B9 on Sheet 2, enter
Sheet2! B1: B9to display the data in the drop-down list
- List of items: A list of predefined data items. This can be either text or numbers. You will enter each value yourself and separate it with commas (and no spaces). With this option, you can not insert data directly from other cells.
Here we use the "List of Elements" option and provide several numeric choices.
After you enter the desired data If you see it in a drop-down list, make sure that the option "Show drop-down list in cell" is checked, otherwise the values will not be in the selected cells.
You can also choose what happens when someone tries to enter the data A value that is not on the list. With the option "Show Warning" you can enter the invalid data but mark it in the worksheet. The Reject Input option prevents you from entering items that are not in your list.
You can also check the "Show validation help text" option to show people what they can select in the cells. After selecting the option, enter the desired instructions.
When done, click "Save."
Using the New Drop-Down List
When you use the sheet, you can click the drop-down arrow in those cells and select a value from the list.
If you selected the Show Validation Help Text option, this text appears every time someone chooses one of the validated cells.
If someone enters a value that does not match an entry in the list and the "Show warning" option is selected, the invalid data is marked in the cell.
Hovering over it shows why it is highlighted.
If you selected the Reject Input option instead, a warning will be displayed if you try to type something that is not on your list.
When you need to remove or change All items in the drop-down list go back to Data> Data Review to edit items from the lists you create. Removing the list completely is as easy as clicking the "Remove Validation" button below.