A lot of people are using Google Sheets to make tables, to record and to organize the data that they are working with. In this article, I’m going to show you on how to create Google Sheets dropdown list with data validation. This article includes how to validate data, to create drop-down menus and fill out formats, to set the format and lastly to set the rules!

Google Sheets Dropdown List : Benefits of using Data Validation in Google Sheets

  • Benefit 1. Drop-down menus are faster to fill in data
  • Benefit 2. Restrict users to follow the rule
  • Benefit 3. Easier for statistic analysis
  • Benefit 4. Avoid entering the wrong data that is not found

How to create Google Sheets Dropdown List?

Step 1. Select the data validation

Select the Google Sheets Data Validation

Select the range of your preference, then right click, to find the”Data Validation”.

Step 2. Select your criteria.

Select the list of items criteria to create items Google Sheets dropdown list

Under Criteria, click and choose “List of Items”

Step 3. Key in the items.

List the items to be included in the dropdown list.

Enter the items separated by a comma “,”, then click”Save”

Step 4. Set the rule for invalid data

You can either choose”Show Warning” or “Reject Input”to enforce end users to insert valid data. In this example, we choose “Show Warning”. Hence, when there is data other than what we have set, it will have a different appearance.

How to create number entry criteria in Google Sheets Dropdown List?

Step 1. Select the range.

Select the cell range, right click and click”Data Validation”

Step 2. Select your criteria.

Select the number criteria to create Google Sheets dropdown list.

Under the criteria, choose”Number”to create the number entry in the Google Sheets.

Step 3. Key in the contents.

Select the number of range of Google Sheets data validation.

For an example, if you want to limit the number of days of the leave application, you can set a range of”Number” as above. Any numbers which is not in the preset range will be categorized as the invalid input

Step 4. Set the rule for invalid data

Set the rule for Google Sheets data validation.

In this example, I set the number rule between 1 to 7 and “Reject Input”for this data validation. The end users will not be able to enter a larger number than 7 and if they admit to enter, it will show the preset data validation rule.

Error message for wrong entry of number entered.


I hope you enjoyed learning on how to create Google Sheets Dropdown List by using the Data Validation. It is conducive to the future of statistic and analysis for your company. Hereby, we are one step closer to the quantitative working.


Related Articles:

  1. How to create a Google Sheets Filter Views without affecting other users?
  2. Google Sheets Tutorial : Easy tips to make charts in Google Sheets