Data Validation in Excel

Data Validation is an Excel feature that controls what users can enter into a cell. It helps prevent incorrect data entry and ensures data accuracy.

Why Use Data Validation?

Location of Data Validation

Data Tab → Data Tools Group → Data Validation

Data Validation Dialog Box

When you click Data Validation, a dialog box appears with three tabs:

Types of Data Validation

Example 1: Marks Between 0 and 100

Steps

1. Select the Marks column.
2. Go to Data → Data Validation.
3. In Allow, select Whole Number.
4. In Data, select Between.
5. Minimum = 0
6. Maximum = 100
7. Click OK.

Now Excel allows only numbers from 0 to 100.

Example 2: Create a Dropdown List

Dropdown lists are the most commonly used Data Validation feature.

Example Course List

Steps to Create Dropdown

1. Select the target cells.
2. Go to Data → Data Validation.
3. Choose List in Allow.
4. In Source, type:

   =Excel,Python,Tally,Power BI

5. Click OK.

A dropdown arrow appears in the selected cells.

Example 3: Text Length Validation

Restrict a Mobile Number to exactly 10 digits.

Steps

1. Select the Mobile Number column.
2. Open Data Validation.
3. Select Text Length.
4. Choose Equal To.
5. Enter 10.
6. Click OK.

Excel accepts only 10-character entries.

Input Message

An Input Message appears when the user selects a validated cell.

Error Alert

Error Alert appears when a user enters invalid data.

Remove Data Validation

1. Select the validated cells.
2. Open Data Validation.
3. Click Clear All.
4. Click OK.

Keyboard Shortcut

Custom Formula to Restrict Duplicate Entries

Sometimes we want a value to be entered only once in a worksheet. For example:

• Student ID should not be repeated.
• Employee ID should be unique.
• Mobile Number should not be duplicated.
• Invoice Number should be entered only once.

In such cases, we can use Data Validation with a Custom Formula to prevent duplicate entries.

Example: Prevent Duplicate Student IDs

If a user tries to enter 1002 again, Excel should display an error message.

Custom Formula

Formula Breakdown

Steps to Apply

1. Select the cells (A2:A100).
2. Go to Data → Data Validation.
3. In Allow, select Custom.
4. Enter the formula:

5. Click OK.

Now duplicate entries will not be allowed.

Real World Examples

Error Alert Message