Master Data Validation in Google Sheets in 15 Minutes

Data validation in Google Sheets is essential to ensure you have the correct data types in your spreadsheet. This is especially true when it comes to shared spreadsheets. Often, you don’t want the other users accidentally to mess with parameters. Join us as we take a deep dive into Google Sheets data validation.

What Is Data Validation in Google Sheets?

Data validation is a way of formatting Sheets that allows it to determine the input type that you can add to the spreadsheet. Google Sheets can give an error message or reject the input if the input by the user isn’t in the data range or isn’t the type required by the cell.

Why Data Validation Matters

When writing an assignment or a blog, we check our work to ensure that the words make sense in the context and that there aren’t any grammatical or factual mistakes. Data validation in Sheets is essentially the same concept as it allows you to set specific parameters around the text, numbers, dates, and currency to prevent the users from making any errors.

For example, let’s say you wish to input the student’s marks in a test, and the marks can only be between 0 and 100. With data validation, you can limit the input so that an error is displayed if the user enters a number greater than 100 or smaller than 0. For businesses, it can be important as a product’s purchase date can not be earlier than the date it was released.

Data validation can also prevent the user from entering misspelled words or unwanted values by restricting the selection to a dropdown menu or selecting from a specific range. An example is when entering addresses, some users may input the full state name like “Texas” while others may choose to use the “TX” abbreviation. Using dependent dropdown boxes with data validation means that users can only select one way of entering the state name, which helps prevent discrepancies in the data.

Reasons to Use Data Validation

There are several reasons to use data validation in Sheets. Some of them are:

• Getting Correct Data: when you set up parameters around the input of the data in your spreadsheet, users won’t be able to input incorrect data or data that isn’t formatted properly, as Google Sheets will recognize the wrong input and notify the user accordingly.
• Ensures Consistency: The data looks much more consistent when you ensure that the data in a row or column follows the same rules. For example, this makes searching cities or countries easier as the names will stay the same for each value.
• Easy searching: When data validation is applied to a dataset, it becomes easier to search through as all the elements have the same name, guaranteeing consistent and much more accurate results.
• Controlling User Input: Data validation is very helpful when you share your spreadsheet in real time with other users, as you have total control over the data they can enter into the sheet.
• Saves Time: As data validation allows you to control the data input into the sheet, you won’t have to read through and edit the data. The data validation will automatically do that for you.

How to Use Data Validation in Google Sheets

Here are a few examples of the data validation Google Sheets feature in action.

Setting a Number Limit Using Data Validation

In this example, we have a data set containing the names of seven students and space for entering their marks in a test. The marks can be only between 0 and 100. Let’s take a look at how you can use Google Sheets data validation to do this:

1. Click and drag your cursor across the cells to which you wish to apply the data validation.

2. With the cells selected, click on Data in the top bar and click on Data validation. This will open the Data validation rules menu towards the right side of the screen.
3. Ensure the Apply to range textbox contains the cell range you want to apply data validation.

4. Now, click on the Criteria option and select the Is between option in the dropdown menu.
5. You will see two textboxes under the criteria option that allow you to set the minimum and maximum values. In this case, we used the 0 and 100 values, respectively.
6. Towards the bottom, you can choose what Google Sheets will do If the data is invalid. In this case, we simply want Google Sheets to Show a warning.
7. Click on the green Done button to save the changes.

Now when we enter the values between 0 and 100, they will be accepted by Sheets without displaying an error. However, when a value lower than 0 or larger than 100 is added, Google Sheets will display an error through a small red triangle on the top right of the cell. If you hover over the triangle, the full reason for the error will display.

Related: How to Make Multiple Selection in Dropdown Lists in Google Sheets

Creating a Dropdown List Using Data Validation

In this example, we wish to enter the birth month for various students in a column. As the birth month can only be selected from the 12 months, we can use Google Sheets data validation with multiple selections to create a dropdown menu. Here is how to do this:

1. Click and select the cells you wish to apply the data validation to.

2. With the cells selected, click on Data in the top bar and click on Data validation.

3. We have a cell range in the spreadsheet that contains the months in a year. Note: You can also have the data for the dropdown menu in another sheet and perform Google Sheets data validation from another sheet.
4. Select the Dropdown (from a range) option in Criteria to add them to the dropdown list.
5. With the cell range added to the dropdown list, click the green Done button to save the changes.

You will now see a pill-shaped icon in the cell where you can access the dropdown list. Click on it to display the dropdown menu, where you can see the added parameters.

Using a Custom Formula With Data Validation

You can also use a custom formula in conjunction with data validation to achieve specific results, as custom formulas allow you to set specific criteria that aren’t built into the data validation menu. In this example, we will use the LEN formula to ensure that the length of characters in a cell is lower than five characters.

Here is how you can use Google Sheets data validation custom formula:

1. Click and select the cells you wish to apply the data validation to.

2. With the cells selected, click on Data in the top bar and click on Data validation.

3. In the Data validation rules menu, click on the option under Criteria and select Custom formula is.
4. A new textbox will appear where you can enter the formula. Type in the desired formula here.
5. Click on the green Done button to save the changes.

Once the formula is executed, the cells with a number higher than 4 characters will be highlighted.

Checking Dates With Data Validation

You can also check dates with Google Sheets data validation to check and see if the date is before, after, or on the same day. In this example, we want to check the submission date for an assignment to highlight the students who submitted their assignments late.

Here is how to do this:

1. Click and select the cells you wish to apply the data validation to.

2. With the cells selected, click on Data in the top bar and click on Data validation.

3. Under Criteria, select the Date is on or before option. This will show a textbox where you can select whether you want to check the current date, tomorrow, or yesterday.
4. As we wish to specify a specific date, we use the DATE formula to enter the date.
5. Click on the green Done button to save the changes.

As soon as the formula is executed, the dates will be highlighted to show the date that exceeds the specified date. Hovering your cursor will show a pop-up message showing why data validation has failed.

Related: Google Sheets Date Picker – An Easy Step-by-Step Guide

Frequently Asked Question

Why Is My Data Validation Not Working In Google Sheets?

One reason data validation may fail to work properly in Google Sheets is when formulas or values are pasted directly into Google Sheets. Pasting data inside a Google spreadsheet bypasses the data validation, which is why it may not properly apply. This is fairly common with checkboxes and dropdown menus. To fix this, refresh the spreadsheet and reenter the values in the cells to force Google Sheets to refresh data validation in that specific row or column.

How Do I Change Data Validation on Multiple Cells in Google Sheets?

To change the data validation on multiple cells, hold the Ctrl button on your keyboard and select the cells you want to apply the data validation on. With the cells selected, click on Data and choose the Data validation option. In the Data validation rules menu, choose the Criteria and save the changes by clicking on the green Done button.

Wrapping Up

Data Validation in Google Sheets is a fairly straightforward process where you can simply highlight the cells and click on Data > Data validation, then choose the rules you want to apply. If you found this guide useful, check out some of our related content below.

Related:

• How to Hide Columns In Google Sheets
• How to Insert Multiple Rows in Google Sheets
• How to Insert CheckBox (Tick Box) in Google Sheets
• How to Insert BULLET POINTS in Google Sheets
• How to Sort by Date in Google Sheets
• How to Insert Timestamps in Google Sheets
• The Ultimate Google Data Studio Tutorial
• 7 Best Google Data Studio Certification Courses