Data validation in Google Sheets

The data validation feature in Google Sheets allows you to control what type of data you can add to the cells in your spreadsheet. This is a particularly useful feature when sharing your Google Sheets files with other users, or trying to control the type of data you have inside of your files.

Not only can data validation give you more control over your spreadsheet data, but it also saves you a huge amount of time and effort from having to edit pieces of data. Once you have set up data validation, users are reduced to a specific range of data to input.

Let’s take a look in more detail at what the data validation feature offers in Google Sheets, and how you can validate data in your own spreadsheets in just a few simple steps!

What is data validation?

Data validation is a type of formatting feature in Google Sheets that allows you to determine the type of inputs you can add to your spreadsheet file. This will then limit the type of input a user can enter into your spreadsheet; if the input is not within the data range you have established, then the input becomes invalid.

Let’s take a look at each element within the data validation feature:

• Cell range: This is the range of cells your data validation will be applied to. By default, this range will cover your entire spreadsheet.
• Criteria: This is the type of data validation you want to apply. The options are the following: List from a range, List of items, Number, Text, Date, Custom formula is, and Checkbox. You will see an in-depth explanation and example of each in the section on How to do data validation in Google Sheets.
• On invalid data: You can choose to either Show warning when a user has inputted invalid data, or prevent that input completely by opting for Reject input.
• Appearance: If you would like Google Sheets to display a help text when a user inputs invalid data, you can check this box.

You must edit all of the above elements before applying your data validation. Now that you know what data validation actually is, why use it for your own data?

Why validate your data in Google Sheets?

There are many reasons why you should use data validation in Google Sheets. Here are some of the most important benefits:

• Accurate data: By setting up parameters around data input, users will not be able to input the wrong data or formatting without Google Sheets either recognizing this or rejecting the input entirely.
• Consistent data: You can make sure each row of data follows the same rules by adding specific data ranges – for example, if dealing with percentages, you can adjust the data range so that no user can input a value over 100.
• Save time from editing: Because this feature controls the data for you, there is no need to read through and edit any incorrect data, saving you a lot of time.
• Control other users’ inputs: Data validation is particularly helpful if you are sharing your Google Sheets file with others, or receiving data from other sources. You will have full control over what data they can and cannot enter.

These benefits apply to everyday data management, so data validation can indeed be used in almost any spreadsheet process and use case.

For example, let’s say I am in charge of collecting employee data to monitor monthly salary payments. I can use data validation to control the type of input received, such as the formatting of their date of birth, the department they belong to, or their employee ID.

With data validation, there is no risk of accidental errors from manual input, or employees inputting incorrect data.

As a result, I will have a large file of accurate, employee data that I don’t have to read through and edit, ultimately saving me hours of work.

How to do data validation in Google Sheets

Let’s use the use case above to go through a step-by-step data validation process.

1. Select cell range

Open the Google Sheets file you wish you carry out the data validation and head to Data > Data validation.

A pop-up should appear with the settings for the feature. In the Cell range section, choose the cell range you wish to apply your data validation to.

For this example, I want to apply it to my ‘Department’ column, so I will input the cell range E2:E50.

2. Select criteria and enter data range

Next, it’s time to choose the criteria. You can scroll down to look at an in-depth explanation of each.

Once you’ve selected your criteria, input the data range. In this example, I will choose the criteria List of items and add the various departments as my data range.

3. Adjust settings

Now, you can adjust further settings according to your needs. For the On valid data section, you can choose whether Google Sheets should reject any invalid inputs, or simply give a warning message of invalid input.

Furthermore, you can choose whether Google Sheets should display a help text by checking the Appearance box and editing the help message.

4. Apply data validation

Once you’re happy with your settings, click Save.

Now, you should be able to see your data validation inside your cell range.

And there you go! Validating data is as simple as that. It’s important to know all the different criteria that Google Sheets offers in the data validation feature, so you can make the most of your own data management.

Let’s explore these in more detail.

Different data validation in Google Sheets (with examples)

As mentioned previously, there are several different criteria for data validation in Google Sheets.

Let’s go through each of these to understand exactly what they do and what they may look like inside your own Google Sheets file, using the use case mentioned above.

1. List from a range

The List from a range criterion allows you to create a dropdown list of data options based on values already in your Google Sheets file.

For example, let’s say I want to create a dropdown for my ‘Supervisor’ column. I already have a list of all the supervisors in column K. I can use List from a range to turn this column of supervisors into the dropdown menu for my ‘Supervisor’ column.

Here’s what it would look like:

Once I have applied this, you will see my dropdown now contains the supervisor names.

2. List of items

The List of items criterion will create a dropdown menu based on the values you manually input as your data range.

For example, I want to create a dropdown list for the ‘Department’ column that contains all of the different departments in my business. I can use List of items to add these departments as values:

Once I have applied this, you will see that my ‘Department’ column now contains a dropdown list of all the departments that my employees can choose from.

3. Number

The Number criterion allows you to define a range of numbers that users can input. You have options to validate numbers above or below a certain value, or between two values.

For example, in my ‘Employee ID’ column, I want to make sure that employees correctly add their 4-digit ID number. I can use the Number criterion to set the parameters to Between, and add the lowest value as 1000 and the highest as 9999.

This means that users can only input a 4-digit number.

As you can see, Google Sheets will reject any input that is not 4 digits, and users will see this error message.

4. Text

The Text criterion allows you to control the type of text a user inputs into a cell range. This criterion gives you a lot of options of how to control data, from only accepting an exact text match, to accepting a valid email address or URL.

In this example, I can ensure employees input a valid email address in my ‘Email’ column by using the Text criterion and selecting is valid email.

Once applied, if a user tries to enter an invalid email address, the following warning message will appear.

5. Date

The Data data validation helps to ensure valid date inputs and even lets you control date inputs before or after a certain date, or between certain dates.

For example, let’s say in my ‘DOB’ (date of birth) column, I want to make sure that each employee adds their dates correctly. By choosing the Date criterion and the is valid date setting, any invalid dates will be rejected by Google Sheets.

Once I apply this validation, the ‘DOB’ column will show a calendar that users can use to add their date of birth.

Any invalid date will trigger a warning message.

6. Custom formula

The Custom formula is criterion uses a formula to validate specific data. This means you can create a custom formula to set the parameters of your data validation.

For example, let’s say I want employees to input their monthly pay in the ‘Monthly salary’ column. I want to make sure that no employee inputs a monthly salary of above $8,000. I can do this by choosing Custom formula is, and adding the formula =H2<8000.

Now, if an employee tries to input a monthly salary of over $5,000, they will receive the following error message.

7. Checkbox

The Checkbox criterion is perhaps the most straightforward data validation. It allows you to format the cell as a checkbox.

For example, let’s say I want to add a checkbox to my ‘Paid?’ column so that employees can check this when they have received their paycheck. Simply select the Checkbox criterion as shown below.

Once applied, your specific data range will now include checkboxes.

How to validate data in Google Sheets?

And there you have it! Now you should be able to validate your own data inside Google Sheets using any of the several criteria demonstrated above. You will find that by using data validation, you not only have more control over data when other users input into your sheets, but you also save the time you’d usually spend reviewing and editing your data.

If you would like to learn more about the various features of Google Sheets, why not start with our article on Sorting and filtering data in Google Sheets?

Alternatively, take a look at our related blog posts below!