How to Use Data Validation in Google Sheets

If you’re looking to reduce the chance of errors in your Google Sheets spreadsheet by only accepting valid data, Data Validation is exactly what you’re looking for. With data validation, you can instruct Google Sheets to only accept a specific data type or range for cells, or warn the user when they enter invalid data.

Data validation is simple to use, but it requires mastery of the concepts to use it correctly. Read on to find out what data validation is and how you can use it in Google Sheets to improve your spreadsheets.

What is data validation in Google Sheets?

Data validation is a feature of Google Sheets that allows you to limit the type of input your users can enter into specific cells. You can either use data validation to warn the user when they enter invalid data, or to reject invalid data altogether.

Using data validation, you can improve spreadsheets intended for use by other people. You can give limits to the value they can enter or, for example, create a drop-down list of items they can choose from. Ultimately, you can also enter a custom formula as data validation criteria. This way, Google Sheets will reject the value if it doesn’t satisfy the custom equation.

How to Use Data Validation in Google Sheets

Data validation is simple to use in Google Sheets. The basic steps for using data validation are:

1. Select the cell or range of cells on which you want to use data validation.
2. Go to the Data menu then select Data validation.
3. Enter your criteria.
4. Click on Save.

Before moving on to the examples where we deploy data validation, let’s take a closer look at the elements of data validation.

• Range of cells: This is the range in which the input data will pass the data validation criteria. It can be a single cell or a range of cells.
• Criteria: This is the criterion where you indicate what type of data is valid. The valid data type can be a number, text, date, list, checkbox, or custom formula.
• About invalid data: This indicates the reaction of Google Sheets to invalid data. You can choose to either pass invalid data and display a warning, or reject invalid data and display a warning.
• Appearance: Here you can enter the validation help text. This text will be displayed when the user enters invalid data into a cell.

Now that you know what data validation setup is, let’s see data validation in action with some examples.

Data Validation Example 1: Setting a Numeric Value Limit

In this example, we have a sample spreadsheet designed to collect information about which books the user liked the most. The spreadsheet includes a column containing the book names and another column waiting for user input. The user is supposed to rate each book out of 10.

Since the rating is on a scale of 10 and we don’t have time to check every entry, the goal is to limit the values ​​the user can enter between 1 and 10. The user should not being able to give a book a rating lower than 1 or higher than 10. This requires data validation.

1. Select the cells to which you want to apply data validation. They will be cells B2 for B7 in this example.
2. Go to the Data menu and select Data validation. This will bring up the data validation window.
3. For Criteriato select Number and Between.
4. Enter the limit number of your choice. For this example, we will enter 1 and 10.
5. To verify Reject entry. This will force the user to enter valid data.
6. To verify Show validation help text.
7. Enter a validation helper text to inform the user of the type of data expected.
8. Click on Save.

Now your table is ready for user data. As a test, try rating one of the books with a value greater than 10 or less than 1. You should get an error and your data should be rejected. Now, if you enter a value between 1 and 10, the entry will be accepted. It’s like clockwork!

Data Validation Example 2: Creating a Drop-Down List

One of the common uses of data validation is to create drop-down lists in Google Sheets. You can create a drop-down list to give the user a choice between the items you have decided as valid data.

In this example, we have some student names in a spreadsheet and the goal is to create a drop-down list where the user can choose their major.

1. Select the cells where you want to have a drop-down list. They will be cells B2 for B7 in this example.
2. Go to the Data menu and select Data validation. The Data Validation menu will open.
3. In the criteria, select List of objects.
4. In the text box, enter the list of items. You can separate items with a comma (,). For this example, we entered the items below:

   Medicine,Pharmacy,Biology,Applied Math

5. To verify Show drop down list in cell.
6. To verify Reject entry.
7. To verify Show validation help text.
8. Enter a validation help text.
9. Click on Save.

You will notice a small arrow in each cell. You can now click cells to choose from a drop-down list. If you try to enter a value that is not in the list, your value will be rejected. However, you can still grab the list items.

If you want to add bullets to your list items, read our article on how to add bullets in Google Sheets.

Data Validation Example 3: Setting a Text Length Limit

You can use data validation with custom formulas to do all kinds of things in Google Sheets. Custom formulas allow you to define criteria that are not in any of the criteria types built into data validation in Google Sheets.

For example, let’s create a cell that only allows 5 characters or less. We will do this using data validation.

1. Select the cell. It will be D1 in this example.
2. Go to Data then select Data validation.
3. In the criteria, select Personalized formula.
4. For a custom formula, enter the formula below:

   =LEN(D1)<5

   This formula uses the LEN function to count the characters in the cell and returns true if the length is less than 5 characters.

5. To verify Reject entry.
6. Click on Save.

Now, if you enter a name longer than 5 characters, Google Sheets will reject it and you’ll have to try again. You can use the autofill feature to apply this data validation to other cells.

Validate your data with Google Sheets

Data validation is a tool that allows you to limit what you or another user can enter into specific cells. This way, data validation can be useful in creating surveys to only accept valid data, or in your own spreadsheets to increase accuracy.

There are many types of criteria you can set using Data Validation in Google Sheets, and if the type of data you want isn’t built into Data Validation in Google Sheets, you can set criteria with a custom formula. Now you know how to use data validation in Google Sheets. Rest assured that less invalid data will make your analysis easier and more accurate!

Read more

About the Author