Shared sheets collect creative data: “Dilli”, “delhi ”, “NCR” and “New Delhi” all meaning the same place. Data Validation (Data → Data Validation) stops that at the point of entry.

1. Dropdown lists

Allow: List, Source: =$H$2:$H$10 (or type Yes,No,Maybe). People pick instead of type, so spelling is always consistent.

2. Numbers and dates within limits

• Quantity: Whole number between 1 and 500.
• Invoice date: Date between =DATE(2017,4,1) and =TODAY() — no future invoices.

3. Custom formula rules

=COUNTIF($A:$A, A2)=1              ' no duplicate IDs
=AND(LEN(B2)=10, ISNUMBER(--B2))   ' exactly 10 digits (mobile number)
=ISNUMBER(SEARCH("@", C2))         ' looks like an email

A custom rule allows the entry when the formula returns TRUE.

4. Messages that help

Use the Input Message tab to show a hint when the cell is selected (“Enter 10-digit mobile, no +91”), and the Error Alert tab to explain what went wrong instead of Excel’s generic warning.

5. Find what slipped through

Validation only checks new typing — pasted data can bypass it. Data → Data Validation → Circle Invalid Data highlights every existing cell that breaks the rule.