Excel Data Validation

Data Validation is a feature in Excel that controls what type of data can be entered into a cell. By setting rules, incorrect or invalid entries can be prevented before they cause problems. This helps maintain data accuracy and consistency — especially in shared spreadsheets where multiple people enter data.

Why Use Data Validation?

• Prevent entry of invalid data (e.g., a letter where a number is expected).
• Restrict input to a specific range (e.g., only ages between 18 and 65).
• Create dropdown lists to standardize entries and reduce typos.
• Guide users with input messages and alert them when they enter wrong data.

Accessing Data Validation

1. Select the cell or range of cells to apply validation to.
2. Go to Data → Data Validation.
3. The Data Validation dialog box opens with three tabs: Settings, Input Message, and Error Alert.

Validation Types (Settings Tab)

The Allow dropdown in the Settings tab lets the type of data permitted be chosen.

1. Whole Number

Allows only whole numbers within a specified range.

Example

  Rule: Allow whole numbers between 1 and 100
  Allow: Whole Number
  Data: Between
  Minimum: 1
  Maximum: 100

  → Entering 45 is accepted.
  → Entering 150 or a decimal like 2.5 is rejected.

2. Decimal

Allows decimal numbers within a range. Useful for prices, percentages, and measurements.

Example

  Allow: Decimal
  Data: Greater than or equal to
  Minimum: 0.0
  Maximum: 100.0

3. List

Creates a dropdown list from which users can only select an option. This is one of the most commonly used validation types.

Example: Dropdown for Department

  Allow: List
  Source: Sales,IT,HR,Finance,Marketing

  (Separate entries with commas — no spaces around commas)

  Or reference a cell range as the source:
  Source: =$G$1:$G$5  (where G1:G5 contains the list items)

When the validation is applied, a dropdown arrow appears in the cell. Clicking it shows the list of options.

4. Date

Restricts entries to valid dates within a specified range.

Example

  Allow: Date
  Data: Between
  Start Date: 01/01/2024
  End Date: 31/12/2024

  → Only dates within 2024 are accepted.

5. Time

Restricts entries to valid times within a range.

6. Text Length

Restricts the number of characters that can be entered in a cell.

Example

  Allow: Text Length
  Data: Less than or equal to
  Maximum: 10

  → Only text with 10 or fewer characters is accepted.
  → Useful for phone numbers, postcodes, and short codes.

7. Custom

Uses a formula to define the validation rule. This allows for complex, custom conditions.

Example: Allow only unique values (no duplicates)

  Allow: Custom
  Formula: =COUNTIF($A$2:$A$100, A2)=1

  → Counts how many times the value in A2 appears in the range.
  → If the count is more than 1, it is a duplicate and will be rejected.

Input Message (Optional)

An Input Message appears as a small tooltip when a validated cell is selected. It can guide the user on what to enter.

How to Set Up

1. In the Data Validation dialog, click the Input Message tab.
2. Check Show input message when cell is selected.
3. Enter a Title and a Message.

Example

  Title: "Age Entry"
  Message: "Please enter the employee's age between 18 and 65."

Error Alert

When an invalid value is entered, Excel can show a warning or error message. There are three alert styles:

Stop (Most Restrictive)

Prevents the invalid entry from being saved. The user must enter a valid value or cancel.

Warning

Shows a warning message but allows the user to proceed with the invalid entry if they choose.

Information

Shows an informational message without preventing the entry. The user is simply notified.

Setting Up an Error Alert

1. Click the Error Alert tab in the Data Validation dialog.
2. Check Show error alert after invalid data is entered.
3. Choose the Style (Stop, Warning, or Information).
4. Enter a Title and a message.

Example

  Style: Stop
  Title: "Invalid Entry"
  Message: "Score must be between 0 and 100. Please re-enter."

Removing Data Validation

1. Select the cells with validation rules.
2. Go to Data → Data Validation.
3. Click the Clear All button.
4. Click OK.

Circle Invalid Data

If data was entered before validation rules were applied, existing invalid entries can be identified using the Circle Invalid Data feature.

Go to Data → Data Validation dropdown → Circle Invalid Data. Red circles will appear around cells that do not meet the current validation rules.

Practical Example: Employee Registration Form

  Cell B2 (Name): Text Length ≤ 50
  Cell B3 (Age): Whole number between 18 and 65
  Cell B4 (Department): List = Sales, IT, HR, Finance
  Cell B5 (Join Date): Date between 01/01/2020 and today
  Cell B6 (Salary): Decimal > 0

  Each cell has an input message guiding the user.
  Each cell has a Stop error alert to prevent invalid entries.

Summary

• Data Validation controls what can be entered in a cell by setting rules.
• Validation types include: whole numbers, decimals, lists, dates, time, text length, and custom formulas.
• Dropdown lists (type: List) are one of the most practical uses, ensuring standardized entries.
• Input Messages guide users on what to enter when they select a validated cell.
• Error Alerts notify users when an invalid entry is attempted (Stop, Warning, or Information styles).
• Circle Invalid Data finds and highlights existing invalid entries in a spreadsheet.