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.
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.
Contents
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.
- 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.
Create dropdown list in google sheets (using List of Items)
- Open a spreadsheet in Google Sheets.
- To Highlight cells you’d need to add a dropdown, press Ctrl + A in windows or CMD + A in mac os( press CMD / Ctrl + Arrow left / right key used to ignore other cells). For the above example, I selected a status cell.
![To Highlight cells you'd need to add dropdown]()
Remember that: you must ignore the row header (cell title) when highlight the cell which you need to add dropdown if not your dropdown will override the row title too.
- Then move your mouse pointer to the top of navigation and click Data ( middle of Format and Tool navigation link ), Then select in Data > Data validation.
Expressway: Too quick, open Right-click select column (cell ) Scroll Down > Data validation.![Data Data validation dropdown in google sheets]()
- Then the Data validation dialog box appears.
![data-validation dialog-box]()
- Select list of item in criteria dropdown.
![]()
- Add items that you need to show as a dropdown option (separate by comma).
![dropdown option (separate by comma) - google sheet]()
- Check the show dropdown list on the cell checkbox & enter helper text ( preview as a tooltip in cell top right ) if you need to inform any specific message.
![show dropdown list on the cell checkbox in google sheets]()
- Click the save button.
Create a dropdown list in google sheets (using List from a range with dynamic update ).
use on the above example, we’ll move mark status (dropdown items) in a separate cell in the spreadsheet
- Open a spreadsheet in Google Sheets.
- Click the Mark Status sheet tab.
![]()
- Highlight cells you’d need to create a dropdown ( from the whole-cell).
![To Highlight cells you'd need to add dropdown in google sheets]()
- Then move your mouse pointer to the top of navigation and click Data ( middle of Format and Tool navigation link ), Then select in Data > Define Named Range.
![select in Data-Named Range]()
How to learn about named ranges in Google Sheets ? click this link
- Give Proper name to name range text box.
- Then move the parent sheet tab which you need to add dropdown.
- Highlight rows which you need to add dropdown (make sure select without row header )
![Highlight rows which you need to add dropdown]()
- Then move your mouse pointer to the top of navigation and click Data ( middle of Format and Tool navigation link ), Then select in Data > Data validation.
Expressway: Too quick, open Right-click select column ( cell ) Scroll Down > Data validation.![Then select in Data - Data validatio]()
- Then the Data validation dialog box appears.
![Data validation dialog box appears]()
- Select list from a range in criteria dropdown.
- Add name range which you create using datasheet & save data validation dialog box.
- Your dropdown will appear on the datasheet.
- This is work with dynamic data change. that‘s mean if you update or add new data record to list range data. It will automatically track in to dropdown
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
![row range which you need to remove drop down]()
- Open data validation dialog box ( Data > Data validation )
![Open data validation dialog box ( Data > Data validation )]()
- click remove validation button from the button of dialog box.
![click remove validation button from 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.
