Blog
>
Tutorials

Data Validation in Google Sheets - The Ultimate Guide for 2023

5 Minutes

In this article we will teach you everything you need to know about data validation in Google Sheets along with examples

A demonstration sheet was created for the purpose of these examples. Follow along by clicking the following link:

https://docs.google.com/spreadsheets/d/17qQWyxECJnu9RZ8sjpL5ncmSqRLKDnFUGLsuYSb6yfo/edit?usp=sharing

Data Validation in Google Sheets

Data validation allows the control and limitation of the data a user can input. This Google Sheets feature is useful to improve the accuracy and functionality of spreadsheets that are intended for use by other people.

The limitations can be applied to a variety of input types and can be utilised to ensure the data entered matches set criteria.

The below examples are demonstrations of how to use data validation in Google Sheets including the limitation of inputed text and numeric values. We will also show how to make a drop-down list using data validation.

How to Use Google Sheets Data Validation

Data validation is quite a simple feature to use.

1. Select the individual cell or cell range

Click an empty cell to select it, alternatively, you can select a cell range by clicking and dragging over the required cells, highlighting them.

In our example, we will select the cell range C4:C9

validated data

2. Select Data > Data Validation

From the file menu select Data to produce a drop-down menu, from this menu select Data Validation.

validating data

3. Select “Add Rule” from the Data validation rules menu

The Data validation rules menu will now display on the right-hand side, from this window select + Add Rule.

google sheets data validation formula

4. Set the Criteria for the Data Validation

A set of options will now appear in the Data validation rules menu

using data validation in google sheets

Expand the drop-down menu in the criteria section and choose the criteria you want to apply for the data validation to take place. Depending on the criteria you can input various parameters.

data validation google sheets

5. Click Done to Apply the Data Validation

With the criteria and parameters click Done to apply the criteria, the data validation will be applied to the data range selected earlier.

How to Use Data Validation to Set a Number Value Limit in Google Sheets

In this example, we will demonstrate how to set a number value limit in Google Sheets using the useful data validation feature. We will now apply data validation to show a warning on any number that is less than (<) 20.

1. Select the Cell or Cell Range

Select the cell or cell range you want to apply the data validation to.

In our example, we will select the cell range G5:G10

2. Click Data > Data validation followed by +Add Rule

From the file menu at the top of the screen click Data followed by Data Validation from the drop-down menu

How to use data validation in Google Sheets

From the displayed Data validation rules menu, select +Add rule.

3. Select “greater than” From the Criteria Drop-Down Menu

Expand the Criteria drop-down menu, and select the greater than option. This is specific to applying a limit under a certain number. If you need to apply data validation to specify a limit over a number or equal to then select the relevant option.

As we are applying data validation to limit numbers under 20 we will choose the greater than option.

4. Choose an Action for Invalid Data and Click Done

From the if the data is invalid section select the option you want to action if the data validation is not in the defined limit:

Show a warning: Allows the input however will show a warning message.

 

Reject the input: Does not allow the input and clears the text upon invalid limit.

For our example we will check the option to Show a warning. 

ALT: Data validation in Google Sheets

After this click Done to see the results.

How to Make a Drop-Down List Using Data Validation in Google Sheets

We can use data validation to create a drop-down list in Google Sheets. This is exceptionally useful to allow very specific input. In our example, we will create a drop-down list for a person's hair color.

1. Select an Empty Cell or Cell Range

Highlight the cell or cell range you want to apply the data validation to.

In our example, we are going to highlight the cell range D23:D28

Google spreadsheet data validation

2. Select Data followed by Data Validation. Then Select + Add Rule

Click Data followed by data validation from the file menu at the top of the display.

data validated in a google sheet

Now click +Add rule from the displayed data Validation options menu on the right-hand side of the screen.

Data validation in Google Sheets

3. Select “Drop-down” from the Criteria and input the Parameters

From the Criteria section, ensure the Drop-down option is selected. You will now see a list appear underneath. Complete the list with your chosen parameters, you can also choose colors to apply to the list when the specific option is chosen. 

In our example, we will enter five different hair colors and assign a corresponding color to each one.

 

validating data in google sheets

4. Select “Done” and Test the Results

With the parameters and criteria chosen, click the green Done button. You can now test your drop-down list by clicking the relevant cell to see the list values.

As can be seen in our example when a cell in the previously selected cell range is clicked, the list values now apply, we can simply click a value to input it in the cell.

how can i validate data in a google sheet?

Use Google Sheets to Set a Text Length Limit Using Data Validation

We can also limit inputted text length in Google Sheets, here is how.

1. Select the cell or cell range

Select the cell or cell range to where you want to apply the data validation.

We will select the cell range D5:D10 in our example.

how to validate data in google sheets

2. Select Data validation from the Data menu and select +Add rule

From the file menu, select Data followed by Data validation from the drop-down menu.

The Data validation rules menu will appear on the right hand side of the display. Click the + Add rule button.

Using data validation in Google Sheets

3. Select “Custom formula is” from the Criteria menu

Expand the drop-down menu underneath the Criteria heading, scroll to the bottom and select the option for Custom formula is.

Data validation in Google Sheets

4. Input the Formula “=LEN(First_Cell)<Text_Length”

In the criteria input box enter the formula in the following format:

=LEN(First_Cell)<Text_Length

Formula Breakdown:

=LEN: Informs the program to count the length of the input

First_Cell: This is the first cell in your selected range

<: Using the less than operator (<) defines the text needs to be below a defined text range, to set a minimum input text limit simply use the greater than (>) operator.

Text_Length: The defined amount you want to set the entered limit to.

In our example we will use the following formula to ensure that any text entered over 7 characters displays the data validation warning. To achieve this the formula is:

=LEN(D5)<7

5. Press “Done” to apply the data validation

With the formula entered, click the done button. The data validation rule will be applied to the previously selected cell range. 

As can be seen in the example, any cell containing text longer 7 characters displays an error message.

Data validation Google Sheets

Lido is a new spreadsheet built for automation. Trigger emails, slack messages, and more directly from a spreadsheet.