What Is Data Validation In Google Sheets?
Data Validation in Google Sheets is a feature that enables us to control what a user can update in a cell. It permits us to set specific data entry conditions and rules, with an option to show a customized message if a user enters an invalid value into a cell.
Users can avail of the Data Validation feature in Google Sheets to ensure the quality of the entered financial and statistical data by restricting unapproved values.
For example, we must update the monthly inventory level data in column B of the following dataset.
The criterion is that the entered inventory level for each month should be more than 0. Otherwise, the user must see a warning message stating that the input value must be greater than 0.
Then, we can secure the desired outcome using Data Validation in Google Sheets. For that, we must choose the concerned column B cell range B2:B13 and select the Data Validation option from the Data tab.
The above action opens the Data validation rules pane on the right of the workspace, where we must click the + Add rule option to view the options, as depicted below.
Since we already chose the required cell range, the chosen range address appears in the first field, Apply to range. Next, we use the second field Criteria dropdown button to set the required condition, Greater than, followed by which we update the next field as 0. The reason is that we need to ensure the user updates values more than 0 in all the cells of the chosen range.
Next, check the option to show the help text for a chosen cell when a user enters an incorrect value under Advanced options.
Finally, click Done to complete the data validation settings.
Now, we can start updating the concerned column B cells with the valid inventory level values.
Assume we enter 0 in cell B3 and press Enter. Immediately, Google Sheets will show the warning message with the help text, stating that the input should be more than 0. So, now, we can correct the entry in cell B3 and continue updating the valid values in the remaining cells.
Table of contents
Key Takeaways
- The feature Data Validation in Google Sheetshelps in restricting the input users are allowed to update in a specific cell or a range.
- The Data Validation option in Google Sheets is useful for applying the conditions when users try to create a list and update texts, numbers, and dates in specific cells. Also, the feature allows one to create checkboxes and use custom formulas to control how a user can enter data in the selected range.
- We can utilize the Data Validation feature in Google Sheets as a standalone functionality. However, utilizing the feature with other inbuilt features, such as Conditional Formatting,makes it highly productive.
Data Validation Options
When we add Data Validation in Google Sheets, we will see the following list of validation options in the Criteria field to apply the required validation criterion.
- Dropdown
This option enables us to achieve a pull-down menu in Google Sheets by entering the dropdown options manually. We can also choose different colors for every textbox. Also, we can choose the options from a cell range instead of manually entering every text value in the specified fields.
- Text
The Text condition enables us to restrict the type of text a user enters into a cell. This condition offers a variety of options, from entering a text that contains, excludes, or matches a specific value to updating a valid email or URL.
- Date
The Date condition enables us to ensure that users update only valid dates in the specific cells. Furthermore, the condition permits us to set the criterion that the entered date should be prior to or after a specific date or between a certain date range.
- Number
The Number condition helps us define the valid set of numbers that a user can update in a cell. We get the option to validate number values above or below a specific value or between two numbers.
- Checkbox
The Checkbox condition permits us to format the chosen cell as a checkbox.
- Custom Formula
We can enter a customized formula to set the required data validation condition in the chosen cell.
Steps To Validate Data In Google Sheets
The steps to add Data Validation in Google Sheets to validate data in the chosen cells are as follows:
- Choose the concerned cell or cell range by clicking an empty cell or by clicking and dragging over the specific cells.
- Choose the Data tab –> The Data validation option.
3. The Data validation rules pane will now show on the right of the workspace, where we must select the + Add rule option.
The Data validation rules pane will show the mandatory fields we must update to perform the validation. Since we already chose the specific cell, its address gets updated in the first field, Apply to range. We can also click the icon against the first field to capture the required cell or range address.
4. Update the data validation condition using the Criteria field dropdown list of options.
Next, based on the chosen condition, we will see the fields where we must update the appropriate values to apply the specific condition.
5. Click Advanced options to set the settings to show the help message or reject the value when a user enters an invalid value.
6. Click Done to apply the data validation condition on the chosen cell or cells.
Examples
We shall see the effective ways of using Data Validation in Google Sheets with illustrations.
Example #1 – List Validation
We have two sheets. The first sheet lists employees, and we must update their designations and teams based on the valid values provided in the second sheet for the two columns of the first sheet.
Thus, this is an example of Data Validation in Google Sheets from another sheet.
Step 1: Open the second sheet, choose the range A2:A5, update the range name as Designation in the Name Box on the top-left corner of the work area, and press Enter.
Next choose the range C2:C6, update its name as Team in the Name Box, and press Enter.
Step 2: Open the first sheet and select the range B2:B6. Next, choose Data à Data validation.
The Data validation rules window opens, where we must click the + Add rule option.
Step 3: The first field shows the address of the chosen cell range.
We must click the second field dropdown button to choose the required condition from the dropdown list. In this case of Data Validation in Google Sheets from another sheet, we shall choose the Dropdown (from a range) option.
Next, we must update the range to reference the list of values in the field below the Criteria field, which is the ‘=’ sign followed by the named range Designation.
Next, click Advanced options to set the required fields to show the warning message in the case when a user updates an invalid value in a chosen cell.
Click Done.
Step 4: Choose the range C2:C6 and iterate Step 3 to apply the condition that a user can update the named range Team values in cells C2:C6.
Finally, click Done.
Now, clicking on each cell in the chosen ranges will show the corresponding list of valid values, from where we can choose the required entry to populate the specific cell.
Example #2
We shall see a scenario for conditional Data Validation in Google Sheets.
Consider the first sheet contains dates, and we must update the product available for dispatch on each date based on the product availability status data in the second sheet. Assume the target cell range is B2:B6.
However, the second sheet shows a list of products, with some being available and the rest unavailable. So, since it is a case of conditional Data Validation in Google Sheets, we shall filter the products list to get the set of available products in column D. On the other hand, we shall create a list of unavailable products in column F for other requirements.
Step 1: Select cell D2 in the second sheet, enter the FILTER(), and press Enter.
=FILTER(A2:A,B2:B=”Yes”)
We will get the list of products whose availability status is Yes.
Next, select cell F2, enter the FILTER(), and press Enter.
=FILTER(A2:A,B2:B=”No”)
We will get the list of products whose availability status is No.
Step 2: Choose the range D2:D6, update the range name in the Name Box as PDT_Yes and press Enter.
Step 3: Go to the first sheet and select the range B2:B6 and then Data à Data validation.
The Data validation rules pane will appear, where we should click the + Add rule option.
Step 4: The first field will show the address of the chosen range.
We can now click the second field’s dropdown button to select the required criterion from the list. In this case, we shall choose the Dropdown (from a range) option.
Next, update the named range PDT_Yes in the next field to apply the condition that users must enter the required product from the valid list of available products.
Next, click Advanced options to set the fields to show the warning message in the case when a user enters an incorrect value.
Finally, click Done.
Now, we can click on a cell in the chosen range to view the list of valid values according to the specified condition, which we can select to populate the specific cell.
How To Edit Google Sheets Validation Rules
The steps to edit Google Sheets validation rules are as follows:
- Open the sheet where we must edit the data validation rules. Next, choose Data à Data validation to access the Data validation rules panel.
2. Click on the rule we aim to edit.
We will see the option settings we set while creating the rule. Now, we can edit the required field settings to make the required rule changes.
3. Click Done to apply the edited data validation rule.
How To Remove Data Validation In Google Sheets
The steps to remove Data Validation in Google Sheets are as follows:
- Open the sheet where we must delete the data validation rules. Next, choose Data à Data validation to access the Data validation rules panel.
2. Hover the mouse’s cursor over the rule we aim to delete.
The specific rule gets highlighted and we see a trash can icon against the rule description. Click the icon to delete the rule.
On the other hand, if we must remove all the validation rules, click the Remove all option under the list of validation rules in the Data validation rules pane.
Alternatively, we can click the rule to expand it and click the Remove rule option at the bottom to delete the rule.
Important Things To Note
- Ensure not to paste formulas or values directly into Google Sheets. Otherwise, the feature Data Validation in Google Sheets will not work.
- Select the correct cell range and the applicable condition in the Criteria field in the Data validation rules window to ensure the required data validation applies to the chosen range.
- Set the fields under Advanced options correctly, based on whether the user must see the warning message or their input data should get rejected.
Frequently Asked Questions (FAQs)
We can limit text entry in Google Sheets using Data Validation using the various text-based conditions available in the Criteria field.
For instance, we have a clients’ list, and we must update each client’s email ID in column B, ensuring we update valid email IDs.
Then, the steps to limit the text entries using the Data Validation feature are as follows:
Step 1: Choose the range B2:B5, and then Data →Data validation.
The Data validation rules window opens, where clicking the + Add rule option will show the data validation fields, as depicted below:
Step 2: The first field will show the address of the chosen range. Now, click the second field’s dropdown button to choose the Text is valid email condition.
Next, click Advanced options to set the required options to display the warning message.
Click Done.
Now, we can update the email IDs in the column B cells. Furthermore, if we enter an invalid email ID, we will see an error message stating that the input should be a valid email address.
We can immediately correct the input value and continue updating the valid email IDs in the required cells.
We can copy data validation in Google Sheets using the following steps:
• Choose the cell or cell range with the data validation applied to its data. Next, right-click on the cell and choose Copy or press Ctrl + C (for Windows) or Cmd + C (for Mac).
• Paste to the cell where we wish to add the data validation rule. Otherwise, choose the required cell and select Paste Special à Data validation only if we must paste only the data validation.
So now, the new range gets added to the current data validation rule.
Your Data Validation is not working in Google Sheets, perhaps because you pasted formulas or values directly into Google Sheets. It leads to the data validation getting skipped, which typically happens in the case of checkboxes and dropdown lists.
The solution is to refresh the sheet and enter the values in the cells again to compel Google Sheets to refresh data validation in the concerned row or column.
Download Template
This article must be helpful to understand Data Validation In Google Sheets, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is Data Validation In Google Sheets. Here we explain how to use the Data Validation option in Google Sheets with examples. You can learn more from the following articles. –
Leave a Reply