Data validation in Excel

[vishrut-singhal]

Data validation is a feature of Excel that controls what a user can enter in Excel cells. Basically, it is used in Excel to restrict the type of data/value to be entered in the Excel cells. In Excel, you can use predefined data validations as well as define your own custom conditions.

For example , use the number data validation on a column to restrict the user to enter only numeric data in the cell of the respective column. Similarly, there is various type of data validation to be put in Excel.

Several types of data validations are used in Excel. The most commonly used data validation is to create a dropdown list . The advantage of using data validation in an Excel worksheet is that - it stops the users from entering invalid data into the Excel table.

In this chapter, we will brief you on data validation. In the next chapter, you will see how actually the data validations are applied on Excel cells.

Advantages of using data validations

There are various advantages of using data validations on Excel cells, some of them are as follows:

1. Data validation stops the users from entering invalid data into the Excel table.
2. You can control what type of information input can be entered by the users.
3. It also displays a message to the users that what type of data the respective cell accepts. However, it is completely up to you to display a message to the users.
4. Additionally, data validation can also stop the user from entering any input by creating a dropdown list. It presents the predefined choices to the users for input.
5. Dropdown list creation is the most commonly used data validation. For example, A gender dropdown list having only three options - Male, Female, and Other. Hence, you have no other choice instead of choosing one of them.
6. You can apply any number of validations in an Excel worksheet.
7. One column can also have more than two validation, like text and length validation on a single column.

What happens when data validation applies?

When you apply data validation on an Excel worksheet, it restricts the users from entering any unnecessary input to the cells. For example , if you put the number validation on a cell/column, then you are not allowed to enter any other type of input in it.

If you try to input the wrong data type of input, a message automatically displays by the data validation that which type of data the respective cell will accept. This message is a customized message, which is created while putting validation on the Excel worksheet.

By applying data validation, you can stop the user from entering the unreasonable input into the Excel worksheet.

Excel data validation examples

In [Excel], you can put several types of data validations on Excel cells, such as -

1. Whole number validation
2. Decimal number validation
3. length validation
4. Date and time validation
5. Excel data input validation (Dropdown List)

These all are predefined data validations. Besides the predefined data validations, which are offered by the Excel, you can also define the custom validations on data to be entered in Excel worksheet.

6. Custom data validations

We will show you the example of each validation.

Number Validation

The number validation allows the users to enter only numeric values to the cell. When this validation applies to a particular cell/column, you are not allowed to enter any other type of data in it. For example , Age validation: Enter the age greater than 18 only.

Decimal number Validation

Decimal numbers are floating-point numbers that contain a decimal point in them. When the decimal number validation applies on an Excel worksheet, only numbers having a decimal point are allowed to enter into that Excel worksheet . For example , 1.25, 8.9754, 2.9, etc.

Length Validation

Length validation restricts the users to enter the input length more than the specified number in the validation. For example , you set the length validation on a cell to 8, then the users are not allowed to enter the data having more than 8 characters/numbers in it.

This validation limits the number of characters or digits to be entered in the restricted cells.

Date and time validation

Date and time validations are the validation applied on the column where you want to restrict the users to enter only date and type of input in it. You can individually apply date and time validations to two different columns.

This will allow the user to enter only the date and time type of input value in the restricted column. For example ,

Date - Enter a date only between January 01, 2020, and January 01, 2021.

Time - Enter a time only between 9:30 AM to 6:30 PM.

Excel input validation (dropdown list)

This is a bit different validation from all the other validations mentioned above. It is an input validation that presents predefined choices to the users to enter the input to an Excel cell. Hence, you have the only option to provide input to an Excel cell is to choose input value from the dropdown list.

Dropdown list creation is the most commonly used data validation. E.g., A gender dropdown list having only three options - Male, Female, and Other.

You can also enter the value to the validated field manually. But if it does not meet with any of the specified values in the list, it will generate a default alert message.

Custom data validation

Besides all these built-in validation rules, user can also set their own custom validations using their formulas. You can create custom validations in Excel. These custom validations require a formula to be entered by the creator.

You can define the custom condition from the settings tab by choosing Custom in the validation criteria. We will discuss it later, below in this chapter.

Defining data validation Rules

In Excel, navigate to the Data tab, where inside the Data Tools section, you will get a Data Validation option. Data > Data Tools > Data Validations

From here, you can set data validation rules. Data validation panel has three tabs to set its rules, i.e., Settings, Input Message, and Error Alert.

Following are the three tabs:

Setting tab

A setting tab is a place where you define the validation criteria for a column/cell. This tab contains several built-in validation rules to choose. It also has a Custom data validation option to define your own custom rule. You can choose it too and use your own formula to put the validation.

Input Message tab

The input message tab contains two fields where you can provide a title and a message for the users. This message displays to the users when a cell with the validation rule is selected. It is just a message nothing else, which appears when a cell with validation rule is selected. It indicates to the users before inputting the data into cells.

It is completely optional to provide a message. No message will appear if you set no input message.

Error Alert tab

The error alert tab is a dialogue box, which appears when a wrong entry is filled. It basically shows an error message to the users when they provide the wrong type of input value. It can stop the process of entering the data till correction.

For example ,

In the Style field, you have set " Stop " and provide a message in the Error message field. Hence, the user tries to input the wrong value, input is not allowed and an error message will appear like as showing below:

When the Style is set to another option, like Warning or Information, a different icon will display with a custom message. The below table will summarize the alert type, which are of three types: Stop, Warning, and Information.

Alert	Description
Stop	When this alert is applied, it stops the user from entering the wrong data into the validated cells. The users have to enter a value that passes the data validation applied by you.
On the stop alert window, you have only two options: Either Retry or Cancel .
Warning	The warning alert warns the users for invalid data entry. It just displays a warning message to the user but does nothing to enter invalid data.
The warning alert window has three options: Yes, No , and Cancel . Select Yes to accept invalid data, No to edit that invalid data, and Cancel to remove the invalid data.
Information	This alert informs the users for the invalid data. Just like the Warning alert, it also does nothing to stop the user from entering invalid data.
It has two options: Yes to accept the invalid data, and No to remove that invalid data.