How to Use Data Validation in Google Sheets

Nikita M.

Media-savvy content creator, with a curiosity for all Salesforce experiences.

Updated: Jan 9th, 2025

To ensure data in your spreadsheet is accurate and consistent, you want to know about the data validation feature in Google Sheets. When you use data validation, you set rules for what can be entered into spreadsheet cells. In this way, you get to control what data you want to work with and reduce project errors in the long run. 

In the article below, we explore the data validation feature from Google Sheets in more detail. We think it’s a valuable tool for teams that work collaboratively on spreadsheets. The feature helps professionals maintain high data quality, reduce errors, and quickly complete work tasks related to data entry. 

Join us to learn more about the Google Sheets feature supporting online and collaborative work. Let’s get started!

What is Data Validation in Google Sheets? 

Data Validation is a feature in Google Sheets. People use it when working with online spreadsheets to control the type of data entered into a specific cell. By knowing how to do data validation in Google Sheets, people configure special rules for their spreadsheets so any user collaborating on the document only inserts valid data. 

For example, an accountant can use data validation to ensure only specific numbers, dates, or predefined options configured in a dropdown list can be entered into their spreadsheet. This feature is essential for reducing errors and maintaining high data quality in spreadsheets. 

Check out our Excel Dynamic Formulas article to learn about other features for spreadsheets. But let’s get back to our Google Sheets discussion!

How to Put Google Sheets Data Validation in Online Spreadsheets

Here is a short guide that shows how to use data validation in Google Sheets: 

1. In a spreadsheet, select a cell.
2. Then click on the Data tab and select Data Validation.

3. A dialog box will pop up. Choose your validation settings in the Criteria dropdown, such as Dropdown, Dropdown (from a range), or Text contains.
4. Continue to configure your validation settings by configuring Advanced Settings.

5. This optional step is helpful if you collaborate with many people on a spreadsheet. Click on the Insert tab and then Note to provide a message that explains to colleagues what can be added to the cell. 

6. Another useful but optional step is configuring a cell’s error alert. To do this, select the Show a warning radio button to alert an individual whenever they try to insert invalid data into a cell.

7. Click Done to set your validation rules in a spreadsheet.

And that is how to add data validation in Google Sheets!

How to Edit Data Validation in Google Sheets

Using data validation rules in Google Sheets is useful for keeping details accurate. But what happens when your business requirements or project changes? During these times, when you want to update validation rules, you can use the edit feature so that you do not need to delete previous work or configurations. 

Follow our simple steps to learn how to edit data validation in Google Sheets:

First, select the cells with data validation and navigate to the Data Validation settings. The data validation dialog box will appear and display the rules you initially set up. Now, you can change the cell’s criteria, error alerts, and more. Click Done to apply your new fine-tuned settings. 

And that’s how to change data validation rules in Google Sheets.

How to Lock Data Validation in Google Sheets

Sometimes, you might want to lock cells with data validation so collaborators cannot change it. To do so, you can follow these simple steps.

1. Select the cells with data validation you want to lock.
2. Right-click on them, hover over View more cell actions, and then choose Protect range. 
3. Click the Change Permissions button, select the Restrict who can edit this range radio button, and choose who can edit the cell from a dropdown.
4. Click on Done to approve your settings. 

It’s that simple!

How to Remove Data Validation in Google Sheets

There are times when you need to know how to remove data validation in Google Sheets. Follow the two steps below to learn how.

1. First, select the cells with data validation and navigate to Data Validation from the Data tab.
2. Click on the Remove all button.

You will see that your data validation has been removed from the cell immediately.  And that is how you remove data validation in Google Sheets!

Google Sheets: Data Validation Based on Another Cell

Once you have mastered the easy steps above to work with data validation rules, it’s time to tackle the more complex configurations in Google Sheets. How about setting up data validation based on another cell in Google Sheets?

This configuration is useful because it lets you set rules that will dynamically change depending on what values are in another cell. To set this up, one needs to choose “Custom formula is” under the Criteria dropdown in the Data Validation settings. 

Tips + Best Practices in Google Sheets

And those are all the suggestions we have for you when working with data validation in Google Sheets. We wanted to explore this product since it gives teams real-time collaboration capabilities. That means multiple people can work on a single document at the same time.

With Google Sheets, remote or online teamwork becomes easy. Colleagues can share information, edit spreadsheets, and make comments on cells together as long as they have a stable internet connection. 

See you soon!

• Twitter
• Facebook
• YouTube
• LinkedIn

Disclaimer: The comparisons listed in this article are based on information provided by the companies online and online reviews from users. If you found a mistake, please contact us.