Number validation rules
Whole number data validation rule
We want to make sure that no more than three students attend the appointment.
In other words, we need to create an error message as invalid data if the data entry is not a whole number or is more than 3.
Let’s learn how to apply the whole number validation rule.
- Select the “Whole number” as the data validation criteria from the settings tab.
- Select a condition under data in the data validation dialog box.
I select “between” and give 1 & 3 as the minimum and maximum values.
Students can enter only 1, 2, or 3 in column C.
Decimal data validation rule
We would like to get the group results when they are scheduling the appointment.
The results should be between 0% to 100% (i.e., the results should be between 0 to 1).
Let’s learn how to do data validation in excel for decimal values.
- Select “Decimal” as the data validation criteria from the settings tab.
- Select a condition from the list under data and apply validation criteria.
Text validation rule
Do you think we can have data validation in Excel only for numeric values?🤔
No. We can apply for data validation in excel for text length as well.
When the user inputs a text that has a different length to the given data validation that data entry can be recognized as invalid data.
In this table, the group name should be less than 15 letters.
Let’s look at how we can validate the group name.
- Select the “Text length” as the data validation criteria from the settings tab.
- Select a condition for the data validation from the data box.
In the last column, students must enter either “Batch 1” or “Batch 2”.
We can give those 2 options in a cell dropdown box.
If the user selects “Batch 1”, it will show in that cell.
Let’s learn how to create a drop down list.
- Select the “List” as the data validation criteria.
- Enter the items for the drop down list in the source box or give a specified range for the drop down list in the source box.
To give a cell reference, click the upside arrow in the source box.