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:
- Apply to range: This is the range of cells your data validation will be applied to.
- Criteria: This is the type of data validation you want to apply. The options are the following: Dropdown, Dropdown from a range, Text, Date, Number, Custom formula, and Checkbox. You will see an in-depth explanation in the section on How to do data validation in Google Sheets.
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.
Sheetgo Forms can make your life even easier. With Sheetgo, you can input data in Google Sheets easily by simply filling out a form. Click the button below to start using Sheetgo Forms.
Sheetgo
Use case
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.
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.
Dropwdown and dropdown from a range
This is the option to create a pull-down menu in the sheet by inputting the choices manually.
First, under Criteria, select Dropdown.
Then choose the range of cells where you want to create the dropdown list.
Add the text to the boxes. In this example, I’ll add all the departments.
You can also choose different colors for each textbox you create.
If you prefer, you can select the options from a range of cells instead of typing all the text in the boxes.
In this case, select Dropdown (from a range) in the Criteria section.
After selecting the range where you want to create the dropdown, choose the range of cells containing all the options you need to add to the list.
Now, I don’t need to input the data manually. All I need to do is choose one of the options from the dropdown list.
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 selecting Text is valid email.
Under Advanced options, you can choose what will happen if I input invalid data. Google Sheets can show a warning or reject the input when the data is invalid.
I’ll choose Show a warning and click Done.
Now if I input invalid data, Google Sheets will show a warning message.
Date
The Date criterion helps you make sure only valid dates are added to the cells and even lets you control date inputs before or after a certain date, or between certain dates.
For example, let’s say in my “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 option, any invalid dates will be rejected by Google Sheets.
Now Google Sheets will only accept a valid date when you input data into the cells where the data validation is applied. If you double-click a cell within this range, a calendar will appear.
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 choose Number is between and add the lowest value as 1000 and the highest as 9999.
This means that users can only input a 4-digit number.
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.
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 want to make it even easier to input data in Google Sheets, check out our Sheetgo Forms feature. With Sheetgo Forms, you can input data in Google Sheets easily by simply filling out a form.
Not only will you be able to input only valid data, but you will also have more control over your spreadsheet. For example, If you need your colleagues to input data, you don’t need to share your spreadsheet with them. Instead, you can share only the form and ask your colleagues to fill it out whenever they have to input new data. This would protect your data and prevent them from editing your spreadsheet accidentally.
If you want to take data collection to the next level, click the button below to start using Sheetgo Forms.
Sheetgo
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!
Editor’s note: This is a revised version of a previous post that has been updated for accuracy and comprehensiveness.
