You can use data validation to restrict the type of data or the values that users enter into a cell.
These types of data to which input can be restricted are:
- Whole Number - to restrict the cell to accept only whole numbers.
- Decimal - to restrict the cell to accept only decimal numbers.
- List - to pick data from the drop-down list.
- Date - to restrict the cell to accept only a date.
- Time - to restrict the cell to accept only a time.
- Text Length - to restrict the length of the text.
- Custom – for a custom formula/condition.
The data conditions which can be set are:
- between
- not between
- equal to
- not equal to
- greater than
- less than
- greater than or equal to
- less than or equal to
Below is an example where it is only allowed to enter a value between 1 and 10:
1. Select the cell(s) you want to create a rule for.
2. Select Data >Data Validation.
3. On the Settings tab, under Allow, select an option:
4. Under Data, select a condition:
5. Set the other required values, based on what you chose for Allow and Data. For example, if you select between, then select the Minimum: and Maximum: values for the cell(s):
7. Select the Ignore blank checkbox if you want to ignore blank spaces or unselect the Ignore blank checkbox if you don't want to ignore blank spaces.
8. If you want to add a Title and message for your rule, select the Input Message tab, and then type a title and input message:
9. Select the Show input message when cell is selected check box to display the message when the user selects or hovers over the selected cell(s).
10. Select OK.
Now, if the user tries to enter a value that is not valid, a pop-up appears with the message, “This value doesn’t match the data validation restrictions for this cell.”
If the above error alert is to vague you can add a custom alert by following the steps below:
11. To add a custom error alert perform the same steps as above but before selecting OK go to the Error Alert tab and add a Title and/or Error message and then click OK:
This is the message that will appear when invalid data is entered into the selected cells:
The Error Alert has three styles:
This is the message that will appear when invalid data is entered into the selected cells:
The Error Alert has three styles:
Data validation is thus a very useful tool when you want to ensure that the correct information is entered into cells. It prevents/warns users of their error thus allowing them to make the necessary correction rather than having to locate it after the fact.
Link to original post: https://excellentsolutionsza.blogspot.com/
Discuss this article
ℹ You can select the account to comment from with the dropdown arrow on the left, and you can easily mention businesses using their @businesshandle in order to let them know about this article.