Learning how to restrict data in Google Sheets with data validation is useful to prevent others from entering the wrong form of information into your shared spreadsheets.
Data validation enables you to restrict the kind of data users can input by specifying or creating certain criteria. By doing so, the data inputted into the spreadsheet would remain clear, clean, and accurate to be used.
This helps ensure that the data collected can be fully trusted. Using the data validation feature in Google Sheets is also a proactive way of safeguarding the data being inputted from incorrect email addresses, invalid phone numbers, etc.
Table of Contents
Let’s take an example:
You would like to collect data on which type of sports your students would want to play for the next class. However, you want to limit them to only three choices.
This can easily be done using the data validation function to create three choices for the students to select from.
By listing down the items limited to these three choices, students can only select either one of these sports from the dropdown. They are unable to key in any other sports as the cell is restricted from doing so.
Besides creating a dropdown with choices, we can also use the data validation function to create cell restrictions. It can vary from checkboxes to signify true or false to a statement or validating an acceptable date.
Data validation can also create criteria by limiting between two dates. For example, you want to ask your classmates’ preferred date for a gathering between 12 Feb 2021 and 18 Feb 2021.
By using the data validation feature in Google Sheets, you can set the data criteria to be dates between 12 Feb 2021 and 18 Feb 2021. Any date inputted into the cell would be invalid and rejected.
For example, your students are given an assignment to write an essay for their upcoming midterms. They are told to submit them through Google Docs, and you would like to collect all URL links.
Similarly, you can do the same with a URL link. By using the data validation feature in Google Sheets, you can validate the inputted URL link to verify that it is valid.
For example, a valid URL link would contain a protocol (http), a hostname (www.example.com), and a file name (index.html). If the input does not contain any one of the three factors mentioned, it would be rejected.
A Real Life Example of Using Data Validation
Let us use a real-life scenario to utilize the data validation function in Google Sheets.
You are the admin executive of your company and are responsible for planning the annual company dinner this year.
You would like to collect data on the number of guests each employee brings to estimate the number of tables to reserve. However, each employee is only allowed to bring a maximum of five guests.
Besides, you would also like to understand if any employees have a restricted diet or are allergic to certain types of food. This will allow you to communicate with the caterer if there are any food restrictions.
By using the data validation function in Google Sheets, these limitations can be easily collected and provide clean and accurate data. 🤗
How to Use Data Validation in Google Sheets
- Simply click on the cell that you want to write down your function at. In this example, it will be ‘C5’.
- First, we would like to limit the data inputted into cells ‘C5:C8’ to be in numeric format. We would also insert criteria to only let employees input from 0 to 5.
- In order to use the data validation function, you would need to select Data. From the dropdown, select Data validation.
- In the criteria section, select Number, between, and input ‘0’ and ‘5’. To reject any other input, select Reject input on the invalid data section. We will also select to Show validation help text to let users understand the criteria written within the cells.
- When users enter any number other than 0 to 5, the help text box would appear to instruct users on the valid data to enter. The help text is customizable.
- Second, we will create a checkbox to let employees tick if or not they have a food restriction.
- To create a checkbox, select Checkbox in the criteria section. We will also customize the cell values. If the Checkbox is ticked, it means ‘Yes’. If not ticked, it means ‘None’.
- Once we press Save, checkboxes will appear for users to tick.
- Lastly, we will create a dropdown menu for the type of food restrictions the employees may have.
- We will select List of items in the criteria section to create a dropdown of items. Then we can enter different items to display in the dropdown menu. To separate each item, we will use a comma.
- Once we are done, the dropdown menu will look like this.
There you go, now you have different ways to use the data validation feature in Google Sheets to standardize the inputted information. 😄
You may make a copy of the spreadsheet using the link I have attached below.
How to Remove Data Validation in Google Sheets
If you accidentally created the wrong criteria on a certain cell, do not worry as the data validation features can be removed easily!
- First, select the cells where you wrongly created a data validation feature on. In this example, it would be cell ‘C3’.
- Next, click Data, Data validation. Once the pop-up appears, select Remove validation. This would immediately remove the data validation created in cell C3.
- Once you have pressed Remove validation, cell C3 no longer has a dropdown box like cells C4 and C5.
Now you know how to remove unwanted or wrongly created data validation features on Google Sheets!
If you are interested in other ways of applying data validation to a real-life scenario, check out how to incorporate data validation into the INDIRECT function!