In this lesson, you 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.
Why is data validation needed?
From my business experience, I can say that the correctness of the data is very important. This is absolutely essential when working with data, especially when working with large databases.
If you don't make sure that the format of your input is uniform, you will have a problem. Your problem will be that you have to spend a lot of time "cleaning up" the data when you need it.
Data validation in Excel is a functionality that limits the possibilities for users. Thanks to data validation, we have the option of limiting the possibilities of entering data and allowing data to be entered only in a format that suits us.
Examples of such data validation include:
- permission to enter values only from the predefined drop-down list
- only numbers can be entered
- permission to enter up to 15 letters
- possibility of entering only numbers from the indicated range
- restriction on entering dates from the future
- entering a minimum or maximum value that can be entered by the user
- display a message warning or informing about what should be entered in a cell of the workbook
Data validation in Excel offers many possibilities. You have the option of introducing fancy formulas if that is your business need.
Data Validation content
Let's move on to an overview of the content of the tabs that we can find in the correctness of the data.
Settings
In the Settings tab, you can find eight different options that I discuss below.
- Any Value - no validation
- Whole Number - allows you to enter numbers according to the criteria given below (e.g., between min and max values)
- Decimal - Similar to whole numbers but for decimal values
- List - a drop-down list
- Date - criteria for dates
- Time - Same for the time-related data type
- Text Length - limits on the length of the text in the cell
- Custom - you can define your own formula for data correctness. Excel gives you almost unlimited possibilities, so this option meets the expectations of even the most demanding analysts
Input message
In the input message tab, you can enter a message for the user. Often times, entering an input message is necessary because the user may not know what to type in the cell.
Error alert
You can define three types of alerts: Stop, Warning or Information.
The error alert tab is also used to define the message for the user. In this case, it is a message about an incorrect input of data by the user and information that the content of the cell does not comply with the format defined by us.
The user has to correct the contents of the cell. Otherwise, he will not be able to enter the data. This is what the stop alert says.
Alternatively, you have the option of defining a warning or just providing information. In this case, you can accept the data entered by the user and limit yourself to just warning the user or informing them that you expected the data in a different format.
In practice, the stop alert is most often used.
Next, you can learn how to validate data in Excel with step-by-step examples.
Examples of Data Validation Usage
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 that appears when you select a cell in the area controlled by the validation.
Error Alert appears when a user types a number that does not meet the conditions imposed (e.g., 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 the Stop warning is a way to not allow the user to enter 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 eliminate 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 cases where the user has to enter a fixed number of characters. Excel reports an error if the entry gets longer or shorter.
Example 3 - Entering only the current date
If you use the date function =TODAY() so the procedure will cause the user to 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 with the same information (e.g., days of the week, months), you can use a drop-down list to validate.
First, prepare a 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 a cell address.
Drop-down list works like that:
Example 5 - Accepting text only
To get the selected cell where you could only enter text (no numbers), use the following formula:
=ISTEXT(A1)
Example 6 - Acceptance of a 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