How To Create Drop Down List In Sheets

How to Create a Dropdown List In Google Sheets : Best Tips 2021

Written by Amy Brink Technically reviewed by Channa S Bandara.
Publish Date : April 9, 2021 | Review Date : April 11, 2021
Last Update : May 31, 2021

when you working with repeatable data in the spreadsheet, you have to make sure that it cannot enter manually and ensures that there are no errors (to reduce human errors ). The best way to ensure everyone enters the data you want is to provide it as preset . Dropdown list in google sheets is one of the most suitable options for that kind of situation.

About This Article
In this article describe features of Dropdown in google sheets and usage with different user requirements and how to implement different condition operation in real-world situation.

Type of criteria in the dropdown list in google sheets

There two types of data validation

List of Items: this is a hardcord option, you can type in all the option items separated by commas. This is a bit hard to maintain because there are similar inline ( hardcoded) data & that does not support dynamic update and insert of latest items. That means you may have to update all rows manually.
List of range: If you plan to update or insert a new feature, your list of items also may change. You can keep all list items in a separate cell and assign the named range into the dropdown. This is the most recommended way & you can maintain your data without any hassle.

What is Data Validation?

Dropdown in google sheets, Data Validation allows control of what can enter by end users. it can ensure your data is accurate and correct. data validation using to trigger an error or reject input that was entered by the user.

Feature of Data validation dialog box in google sheets

Feature of Data validation dialog box.

Cell range: data cell range which you need to implement dropdown ( which you highlight & select )
Criteria Option: this is a defined condition type that needs to be implemented. List of item & List from a range applicable for dropdown)
Value Field: this will change based on your criteria type

List of items: enter option items separated by a comma you can keep up to 500 items. But if you have a large set of list item google sheet recommend name range instead of the list item.
List from a range: enter cell range which allows to the dropdown option, it will cell reference or name range.

Show dropdown list in the cell: this checkbox represents the show/hide option. If uncheck, dropdown will remove the cell which highlights.
Invalid data - Show warning: if enter an invalid or out of range value in the cell row, you'll see a warning message top right in the cell ( red colour). But your entered value will not be removed automatically.
Invalid data - Reject Input: if enter invalid or out of range value in cell row, an error dialog box will appear & your entered value will be removed automatically.
Show validation help text: this checkbox will represent whether show or helper text (tooltip) in the dropdown cell.
Help text box: if checked the help checkbox, you can change the help text which needs to mention any special message. There doesn't have any character limit.
Remove validation: remove validation from the cell.

About example

Let's take a student exam mark spreadsheet. In this spreadsheet contain 5 data row ( Student Name, Gender, Subject, Mark, Status ). Every row has an exam mark and each subject mark has a specific mark rate.

Total of all expenses
Total of greater than $600 expenses

Download Example Sheet

So, we need to create this mark status as a selectable field (dropdown ) & it needs to be restricted ( validate) if someone tries to enter custom input.

If you have a large set of items or dynamically change items, google sheet recommends maintaining that list set as cell range. It will help to easy maintain your dropdown option.

Remove Dropdown From Your Google Sheet

This is a very simple step whatever you used list criteria to create a dropdown in google sheet.

Select row or row range which you need to remove dropdown
Open data validation dialog box ( Data > Data validation )
click remove validation button from the button of dialog box.

Your selected item automatically converts to text value. You can change it manually.

Conclusion

When you are working with selectable data with spread sheet, dropdown will really be helpful for that. You can ensure that the user entered the most correct data which you only provide.l. You can use the drop down feature very easily using an inline list item or separate datasheet. Most google sheet users are recommended to use a separate data sheet and combine it with the name range. Because it can help to easily maintain & avoid memory leak.

Table of article :