Master Data Validation in Google Sheets in 15 Minutes

Data validation in Google Sheets is essential to ensure that your spreadsheet contains the correct data types. This is especially true for shared spreadsheets. In most cases, you don’t want other users to accidentally mess with your parameters. Please join us for an in-depth look at validating data in Google Sheets.

What Is Data Validation in Google Sheets?

Data Validation is a sheet formatting method that allows you to determine the types of inputs you can add to your spreadsheet. Google Sheets can display an error message or reject the input if the user’s input is not within the data range or is not of the required type for the cell.

Why Data Validation Matters

When writing assignments or blogs, we check our work to make sure the words make sense in context and that there are no grammatical or factual mistakes. Data validation in spreadsheets is essentially the same concept in that it sets specific parameters for text, numbers, dates, and currencies to prevent user errors.

For example, let’s say you want to enter a student’s score on a test. Its score is only between 0 and 100. You can use data validation to constrain the input so that if the user enters a number greater than 0-100, an error is displayed. 100 or less than 0. This can be important for businesses as the product purchase date cannot be earlier than the release date.

Data validation can also prevent users from entering misspelled words or unnecessary values ​​by restricting selections to drop-down menus or selecting from specific ranges. For example, when entering an address, some users enter the full state name, such as “Texas,” while others choose to use the abbreviation “TX.” Using a dependent dropdown box in data validation prevents data discrepancies because the user can select only one method for entering the state name.

Reasons to Use Data Validation

There are several reasons to use Data Validation in your spreadsheets. Some of them are:

• Get correct data: When you set parameters for entering data in Sheets, Google Sheets recognizes incorrect entries and notifies you, so users can enter incorrect or poorly formatted data lose according to.
• Ensuring Consistency: Ensuring data in rows or columns follows the same rules makes data more consistent. For example, each value will have the same name, making it easier to search for cities and countries.
• Easier Searching: When data validation is applied to a dataset, all elements have the same name, making searching easier and ensuring consistent and more accurate results.
• User input control: Data validation is extremely useful when sharing your spreadsheet with others in real time, as it gives you complete control over what data can be entered into the sheet.
• Save time: Data Validation lets you control data entry into sheets so you don’t have to read or edit data. Data validation does that automatically.

How to Use Data Validation in Google Sheets

Here are some real-life examples of Google Sheets’ data validation features.

Setting a Number Limit Using Data Validation

In this example, we have a dataset with spaces to enter the names of seven students and their test grading scheme. Marks can only be specified between 0 and 100. Let’s see how to do this using Google Sheets Data Validation:

1. Click the cell where you want to apply data validation and drag the cursor.

1. With the cell selected, click Data in the top bar, then click Data Validation. This will open the Data Validation Rules menu on the right side of the screen.
2. Make sure the Apply to range text box contains the cell range to which you want to apply data validation.

1. Now click on the Criteria option and select the Between option in the drop-down menu.
2. Two text boxes appear below the condition options, allowing you to set the minimum and maximum values. In this case, we used values ​​of 0 and 100 respectively.
3. At the bottom, you can choose what Google Sheets should do if the data is invalid. In this case, I just want to display a warning in Google Sheets.
4. Click the green “Done” button to save your changes.

Now, if you enter a value between 0 and 100, the spreadsheet will accept it without giving you an error. However, if you add a value less than 0 or greater than 100, Google Sheets will display an error with a small red triangle in the upper right corner of the cell. Mouse over the triangle to see the full reason for the error.

 How to multi-select in a dropdown list in Google Sheets

Creating a Dropdown List Using Data Validation

In this example, we enter the birth months of various students in the columns. Since the month of birth can only be selected from among her 12 months, you can use Google Sheets Data Validation to create a dropdown menu with multiple selections. Here’s how to do this:

1. Click to select the cells to apply data validation.

1. With the cell selected, click “Data” on the top bar, then click “Data Validation.

1. A spreadsheet has a range of cells containing months of the year. Note: You can also save the dropdown menu data to a separate sheet and run Google Sheets Data Validation from the separate sheet.
2. Under Condition, select the Dropdown (From Range) option to add it to the dropdown list.
3. After adding the cell ranges to the drop-down list, click the green Done button to save your changes.

A pill-shaped icon appears in the cell and you can access the drop-down list. Click to display a drop-down menu with added parameters.

Using a Custom Formula With Data Validation

Custom formulas can also be used in conjunction with data validation to achieve specific results, as custom formulas allow you to set specific criteria not built into the data validation menu. This example uses the LEN formula to ensure that the length of characters in a cell is less than 5 characters.

Here’s how to use Google Sheets data validation custom formulas:

1. Click to select the cells to apply data validation.

1. With the cell selected, click “Data” on the top bar, then click “Data Validation.

1. In the Data Validation Rules menu, click the option under Criteria and select Custom expression is.
2. A new text box will appear in which you can enter the formula. Enter the desired formula here.
3. Click the green “Done” button to save your changes.

When the formula is executed, cells containing numbers longer than 4 characters are highlighted.

Checking Dates With Data Validation

You can also check the date in Google Sheets Data Validation to see if the date is before, after, or on the same day. In this example, we check the assignment submission date and highlight the students who submitted the assignment late.

Here’s how to do this:

1. Click to select the cells to apply data validation.

1. With the cell selected, click “Data” on the top bar, then click “Data Validation.

1. Under Criteria, select the Date is on or before option. This will bring up a text box where you can choose whether to check the current date, tomorrow, or yesterday.
2. Since we want to specify a specific date, enter the date using the DATE expression.
3. Click the green “Done” button to save your changes.

As soon as the formula is executed, the dates will be highlighted and the dates that are greater than the specified date will be displayed. Hover over to see a pop-up message indicating why data validation failed.

 Date Picker in Google Sheets – Easy Step-by-Step Guide

Frequently Asked Question

Why Is My Data Validation Not Working In Google Sheets?

One reason why data validation doesn’t work properly in Google Sheets is when formulas or values ​​are pasted directly into Google Sheets. Pasting data into Google Sheets bypasses data validation and may not apply correctly. This is very common with checkboxes and dropdown menus. To fix this, refresh the spreadsheet and re-enter the value in the cell to force Google Sheets to update the data validation for that particular row or column.

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

To change data validation for multiple cells, hold down the Ctrl button on your keyboard and select the cells to apply data validation to. With the cell selected, click Data and select the Data Validation option. Select Criteria in the Data Validation Rules menu and click the green Done button to save your changes.

Wrapping Up

Data validation in Google Sheets is a very simple process, just highlight a cell, click Data > Data Validation, and select the rules you want to apply. If you found this guide helpful, check out the related content below.