Google Sheets offers the feature of Data Validation. Data validation limits the data that can be entered into a cell. You could use this feature to create a drop-down list in Google Sheets. This feature can be especially helpful when you’re trying to collect quantitative data.
If you’ve landed on this page, we assume you’re interested in creating a dropdown list in Google Sheets. In this article, we have presented detailed instructions on how you could use Data Validation to create a dropdown list on Google Sheets so keep reading!
How to Make Dropdown List in Google Sheets
There are two ways you could use Data Validation in Google Sheets to create a drop-down menu. You could either add a new list of data or existing data from the sheet as your options for the drop-down menu.
Refer to the following steps to create a Dropdown list in Google Sheets:
- Open your worksheet.
- Select the cell you wish to place your drop-down menu.
- From the menu bar, select Data > Data Validation.
- In the Data Validation tab, drop the menu down next to Criteria:
- List from a range: If your data is in the sheet, select this option, then enter the range in the message box next to it.
- List of items: If you do not have the data existing in the sheet, select this option, then enter the data items in the box next to it. To separate each value, use the comma (,) sign.
- Configure the response for invalid entry and appearance accordingly.
- Select Save.
You can expand the cell with validation to the corresponding rows and/or columns using Flash Fill. Simply place your cursor on the bottom-right corner of the cell and drag across the row or column.
How to Apply Formatting in the Dropdown List?
You could apply conditional formatting to your list to make it look better. For example, you may have created a dropdown list to update the status of a lead. You could format the Confirmed status as green, Pending as yellow, and Rejected as red.
If you’re interested in using conditional formatting in the dropdown list, follow these steps:
- Select the cells with the data validation.
- From the menu bar, select Format > Conditional Formatting.
- On the Conditional format rules panel to your left, drop the menu for Format cells if.
- Select Text Contains.
- Enter your first value. For this example, we’ve entered Confirmed.
- Select Default under Formatting Style and choose a color. We’ve left the color green for this example.
- Click Done.
- Select Add another rule, then repeat the steps for the remaining values.
After applying the settings, every time you select the formatted value, the cell is filled with the set color.
How to Edit or Delete Dropdown List?
You could easily create changes or remove the dropdown list from your spreadsheet. To clear or edit the dropdown list, you will have to alter or remove the data validation you have applied on the cell.
Refer to the following steps if your wish to edit or delete the dropdown list you created through Data validation:
- Select the cell with the dropdown list.
- Head to Data > Data Validation from the menubar.
- If you wish to edit the options, make your changes in the message box in the Criteria section.
- Select Remove Validation.