excel tutorialExcel Data Validation
You can use data validation in Excel to make sure that users enter certain values into a cell. Input messages can be used to tell the user what to enter. If users ignore this message and enter a value that is not valid, you can show them an error alert.
Note: The next chapter (not this chapter) teaches you how to restrict data entry to values in a drop-down list.
Data Validation Example
In this example, we restrict users to enter a whole number between 0 and 10.
1. Open the accompanied Excel file.
Create Data Validation Rule
To create the data validation rule, execute the following steps.
1. Select cell C2.
2. On the Data tab, click Data Validation.
The Data Validation dialog box appears. On the Settings tab:
3. In the Allow list, click Whole number.
4. In the Data list, click between.
5. Enter the Minimum and Maximum values.
6. Click OK, and try to enter a number smaller than 0 or higher than 10.
Input Message
Input messages appear when the user selects the cell (in this example, selects cell C2) and tell the user what to enter.
On the Input Message tab:
1. Check 'Show input message when cell is selected'.
2. Type a title.
3. Type an Input message.
Result when you select cell C2:
Error Alert
If users ignore the input message and enter a number that is not valid, you can show them an error alert.
On the Error Alert tab:
1. Check 'Show input message when cell is selected'.
2. Type a Title
3. Enter an error message.
Result when you enter a number that is not valid.
You can create all sorts of other data validation rules for cells. For example, you can restrict data entry to a date within a date range (select Date in the Allow list), to a decimal number within a certain range (select Decimal in the Allow list), to text of specific length (select Text length in the Allow list), etc.
Did you like this free Excel tutorial? Show your appreciation, vote for us.