Data Validation in Excel

Data Validation in Excel is used to control what users can enter into cells, ensuring accurate and consistent data entry. It allows rules like limiting text, restricting dates, and creating dropdown lists. Found under the Data tab, it also provides input messages and error alerts to guide users and prevent invalid entries.

Setting Up Data Validation

1. Preparing the Dataset

We enter our data, such as a form with Name, Date of Birth and Exam Centre columns, to apply validation rules.

2. Applying Basic Validation

Step 1: Click on the Data Tab in the Ribbon.

Step 2: Now select Data Validation.

After clicking on Data Validation, a menu appears.

Step 3: Select Data Validation and a dialog box appear.

There are Three Tabs in the Dialog Box

1. Settings: Defines the type of data allowed in a cell and sets validation rules.
2. Input Message: Displays a message when the cell is selected, guiding users on valid input.
3. Error Alert: Shows an error message when invalid data is entered.

Note: Data validation is not fully reliable. Copying data from cells without validation and pasting into validated cells can remove or overwrite the existing validation rules.

Settings Tab

In the settings tab, we can find options to set validation criteria. The Setting Tab helps us to choose the validation rule as per our need from the in-built options. We also have the option to select custom rules with the customized formula for user inputs. There are all the options for Data Validation in the settings tab.

1. Input Message Tab (Optional)

The Input Message tab has a Text box to enter a message displayed as soon as the user selects the respective cell to enter the data. We want to display a message that helps the user to understand what type of data is allowed to enter in the data in the given cell.

Follow the below steps:

Step 1: Open the Input Message Tab.

Step 2: Make sure there should be a tick mark on the "When a cell is selected, show input message".

Step 3: Enter the Title and Text of our message in the fields.

Step 4: As soon as the user selects the Validated cell, It will show this message.

2. Error alert Tab (Optional)

The error alert tab helps us to provide the option to control how the validation is enforced. We can apply different criteria and then use any desired error style according to the user input. We can also display a message to the user informing them about the type of error and what values must be entered in the given cells.

There are three types of error styles in Excel:

• Stop
• Warning 
• Information

If we want to configure a custom error alert message then follow the steps below:

Step 1: Click on the Error Alert in the Data Validation

Step 2: Make sure there is a tick mark in the box "Show error alert after invalid data in entered".

Step 3: We can Select the desired error style in the Style box.

Step 4: Enter the title and text of the error message and click "ok".

Excel Data Validation List

Data Validation also provides a feature of adding a drop-down list to a cell or group of cells. Follow the below steps to add a drop-down list:

Step 1: Select a cell in which we want to add the drop-down list.

Step 2: On the setting tab, Enter List in the Allow box.

Step 3: Type the Items of our Excel Validation list in the Source Box, Separated by commas, for example, To limit the user input into two choices type Yes, NO.

Step 4: Now select the In-cell dropdown box in order for the drop-down arrow to appear next to the cell. 

Step 5: Press "OK"

Example of Data Validation

Let's take the example of filling out a form. The form requires our name which has a limitation of 3-7 characters, it requires our date of birth and has a list of cities for the exam center. Not considering all the other requirements as of now.

The form looks like this.

To apply data validation with a word limit of 3-7 characters for the Name cell.

Step 1: Select the empty cell in front of the Name.

Step 2: From the DATA tab in the ribbon, select Data Validation.

Step 3: After clicking Data Validation, a dialog box appears where we can configure validation rules.

Step 4: In the dialog box from the setting tab, in the dropdown, select Text Length (as shown in the image below).

Step 5: We want our user to enter the name between 3-7 characters, So in the Minimum column we'll write 3 and in the Maximum column we'll write 7 and then click OK.

The Name row will now accept only text between 3-7 characters.

1. To use Data Validation as Date of Birth

Step 6: Select the cell in front of Date of Birth in Excel.

Step 7: Repeat steps 2 and 3.

Step 8: In this step, instead of selecting text length, we need to select Date (as shown in the image below).

 The user must enter a date of birth between 1st January 2000 and 1st January 2021. Enter the Start date as 1st January 2000 and the End date as 1st January 2021. 

Step 9: Enter the required values and click OK to apply the rule.

Now, the Date of Birth row will accept dates between 1st January 2000 to 1st January 2021.

2. Excel Data Validation List (Dropdown)

Step 10: Select the empty cell in front of the Exam Centre.

Step 11: Repeat steps 2 and 3.

Step 12: Select List (as shown in the image below).

We want to add "Kanpur", "Agra", "Aligarh", "Lucknow", "Varanasi" to the list.

Step 13: Add the names in the source column separated by commas(,).

Step 14: Click OK to save the settings.

The Exam center cell will look like this.

We've successfully created a form with 3 requirements using Data Validation.