In this lesson you can learn how to use Data Validation. If you share one sheet to use with lots of people, you might want to use this feature to validate data entry.
Data Validation checks whether another users enter data in accordance with the criteria imposed by you. In this lesson you can learn it with step by step examples.
Example 1 - Enter positive integers
Select a portion of the sheet, which you establish criteria for. Go to Ribbon. Select Data and click Data Validation.
In the Settings tab, set the criteria, as in the following screenshot.
In the Input Message tab, enter the message which appears when you select a cell in the area controlled by the validation.
Error Alert appears when a user types the number did not meet the conditions imposed (eg negative, fraction, zero).
You can also set the Style, which is a reaction to the validation. Excel provides three styles: Stop, Information and Warning. Only Stop warning is a way to not allow the user entered an invalid value. Information and Warning styles allow for such a possibility.
Let's see how it looks in your worksheet. Now you see a comment in the cell.
After entering the number which is less than 0 appears alert message. Data Validation does stop the possibility of entering an incorrect value.
Example 2 - Entering a specific number of characters
This approach will be useful, for example, when you enter social security numbers, or in such cases where the user has to enter a fixed number of characters. Excel reports error if the entry gets longer or shorter.
Example 3 - Entering only the current date
If you will use the date function =TODAY() so the procedure will cause the user will be able to enter only the current date.
If the cells are filled with consecutive dates with any of the options Copy-Paste data validation rules will not work! Validation works only for the data entered from the keyboard!
Example 4 – drop-down list
If you are completing the data sheet enter same information (eg. days of the week, months) you can use a drop-down list to validate.
First, prepare list of all possible options. In this example, these are the days of the week. Then on the Settings tab, select the Source list and select cell address.
Drop-down list works like that:
Example 5 - Accepting text only
To get the selected cell could only enter text (no numbers), use the following formula:
=ISTEXT(A1)
Example 6 - Acceptance of value only if it is greater than the value in the previous cell
The following formula for verifying the accuracy of the data allows the user to enter the cell only a value that is greater than the value contained in the cell located directly above it:
=A2>A1
Further reading: Array Formulas Drop - down list Multiple Linked Drop-Down List