spot_img

Data Validation in excel

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.

Data Validation Ribbon

In the Settings tab, set the criteria, as in the following screenshot.

Data Validation Criteria

In the Input Message tab, enter the message which appears when you select a cell in the area controlled by the validation.

Data Validation Input Msg

Error Alert appears when a user types the number did not meet the conditions imposed (eg negative, fraction, zero).

Data Validation Error Alert

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.

Data Validation Style

Let’s see how it looks in your worksheet. Now you see a comment in the cell.

Data Validation Comment

After entering the number which is less than 0 appears alert message. Data Validation does stop the possibility of entering an incorrect value.

Data Validation Alert

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.

Data Validation Specific

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.

Data Validation TODAY

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.

Data Validation Drop-Down list

Drop-down list works like that:

Excel Data Validation Drop-Down list

Example 5 – Accepting text only

To get the selected cell could only enter text (no numbers), use the following formula:

=ISTEXT(A1)

Data Validation ISTEXT

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

Data Validation greater

Template

You can download the Template here – Download
Further reading:
Array Formulas
Drop - down list
spot_img
Previous articleAvoid Errors Using IFERROR-Everyone Should Know
Next articleLinking Text Box To A Specific Cell