Google Sheets is a collaborative tool. Its collaborative nature can lead to incorrect data entry and discrepancies when multiple users use the same sheet. Drop-down lists help people enter correct data from set options. This helps to maintain the integrity of the sheet. It also helps save time by making the data entry process quick.
Creating and Modifying drop-down lists on Google Sheets
- Creating a drop-down list using data validation
- Modifying a drop-down list using data validation
- Removing data validation
Creating a drop-down list using data validation
Step 1: Cell selection for drop-down list.
Open your spreadsheet and select the set of cells for which you need the drop-down option.
Step 2: Applying data validation.
Click on Data in the main tab.
Select Data validation from the drop-down that opens up.
Step 3: Choosing the criteria from the given options.
Click on Criteria for a drop-down. Select the one you want to use. The various options available are discussed below. List from a range and List of items are explained in greater detail since they can be trickier than other options.
1. List from a range
The List from a range is a selection from the same sheet or another sheet on the same spreadsheet.
Set the range with the cells where you want to apply the drop-down options. For example, column B is where we are applying the drop-down. So we select it and click on Data and Data validation.
Cell range indicates where the values are being applied. In this case, it is B1 to B5.
Input the range from where you want the values to be picked up. In this case, it is A1 to A5 from the same sheet.
You can also input the cells from another sheet as given below:
Here, cells C8 to C13 from the SalesOrder sheet are from where we are taking values to show in the drop-down. Once you’re done, skip over to Step 4.
2. List of items
The List of items is a set of values that you will input and fix as the data that anyone can enter. It can be a text or numbers with no spaces.
Input the list of items separated by commas, but no spaces.
This is how your cells will look.
Once you’re done, skip over to Step 4.
3. Number
The Number option allows you to input a range of numbers.
4. Text
The Text includes options to filter for a specific text string.
5. Date
The Date gives the options to input dates in the following ways:
6. Custom formula
The Custom formula is option allows you to input a specific formula for the cell.
7. Checkbox
The Checkbox option allows you to input checkboxes in specific cells.
Step 4: Other validation options.
Select the Show warning option to prevent input of incorrect data and Save.
If you input any other value than the ones that you have entered in the list, it will appear with a red mark, like below. A message about incorrect data will appear when you click on the red mark.
On Invalid data if you select Reject input by ticking the option, you cannot enter a value other than what you have set in the List of items beforehand.
You will get an error message like the one below if you add an incorrect value.
Select Show dropdown list in cell so that you see the options on hovering on the cell.
You can see the options in a cell when you try to enter a value, as given below.
If you leave Show dropdown list in cell unchecked, the Reject input option will get selected. This would ensure that you won’t be able to input incorrect values in the cells.
Click the Show validation help text option on Appearance. It will show you a help message for clicking and entering the correct value.
Step 5: Click Save.
When you’re through, click Save to apply the settings and exit the data validation dialog box.
Modifying a drop-down list using data validation
Step 1: Cell selection for removing the modification.
Select the cell range that you want to change data validation for by press-holding the Shift key and dragging the selection using your mouse or touchpad.
Step 2: Modify the data validation.
Navigate to Data on the main tab.
Select Data validation from the drop-down and make your edits.
Click Save to apply your edits when you are done.
Removing data validation
If you no longer need to apply data validation to a selection of cells, you can easily remove it. Here’s how you can do so.
Step 1: Cell selection for removing the modification.
Simply press Shift and click the first and last row or column that you need to select.
Or drag the cursor to select the cells where you want to apply the modification.
Step 2: Removing the data validation.
Navigate to Data on the main tab.
Select Data validation from the drop-down and make your edits.
Click on Remove validation and click Save to apply your edits when you are done.
Wrapping up
Drop-down lists are crucial to preserve the sanctity of data being entered in a Google Sheet. You would’ve gained a comprehensive understanding of creating a simple drop-down list from the detailed explanation provided above. You can now also ensure that the data added is correct and the sheet is error-free.