How to Use Data Validation in Excel

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?

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.

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
See also  How to Use What If Analysis in Excel

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.

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 that appears when you select a cell in the area controlled by the validation.

Data Validation Input Msg

Error Alert appears when a user types a number that does not meet the conditions imposed (e.g., negative, fraction, zero).

See also  The Ultimate Guide to Pivot Tables in Excel

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 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.

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 eliminate 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 cases where the user has to enter a fixed number of characters. Excel reports an error if the entry gets longer or shorter.

Data Validation Specific

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.

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 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.

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 where you could only enter text (no numbers), use the following formula:

See also  How to Consolidate Data in Excel

=ISTEXT(A1)

Data Validation ISTEXT

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

Data Validation greater

Data validation can help to ensure that your data is accurate and consistent.