In this article:

Data Validation in Google Sheets - The Ultimate Guide for 2024

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

We hope this article has helped you and given you a better understanding of data validation in Google Sheets. You might also like our articles on how to use data visualization in Google Sheets and how to add a drop-down list in Google Sheets.

To optimize your workflow, we recommend reading our guide on how to send an email from Google Sheets and trying our software for invoice reminders!

Automate repetitive tasks with Lido

Save hours on repetitive and tedious work. Lido is a new spreadsheet that connects your spreadsheets, forms, PDFs, and email inbox.

Trigger
Action
Select trigger
When a cell value in Google Sheets changes
Check Google Sheets for today's date
When a new row is added to Google Sheets
On new Google Form submission
Send me a daily reminder
On new Typeform submission
When a cell value in database changes
Check database for today's date
When a new row is added in database
When a new HubSpot customer is created
Select action
ADDCALENDAREVENT
Creates a one- or multiple-day calendar event with optional attendees
ADDHUBSPOT
Adds an object to Hubspot
ADDSLACKCHANNEL
Create a Slack channel, and optionally add a topic or members
CALLURL
Makes any HTTP request
CREATEGOOGLEDOC
Replaces [@column_name] values in a Google Doc with the corresponding table row's values
CREATEPDF
Replaces [@column_name] values in a Google Doc with the corresponding table row's values, then export it as a PDF
FETCH
Makes any HTTP request and returns the response
INSERTROWS
Inserts given array below defined values in given worksheet
SENDGMAIL
Sends an email using your Google account
SENDOUTLOOK
Sends an email using your Microsoft Outlook account
SENDSLACK
Sends a Slack message
SENDSMS
Sends an SMS message using your Twilio account
UPDATECELL
Updates cells with given values
UPDATEHUBSPOT
Updates a property of a Hubspot object
Trigger is required
Action is required
Get started