Excel Data Validation — Prevent Errors Before They Happen

Data validation is your first line of defence against bad data. It restricts what users can enter into cells — dropdown menus, number ranges, date limits, and custom rules that catch errors before they corrupt your analysis.

Basic Validation: Dropdown Lists

The most common use case. Select your target cells, go to Data → Data Validation:

1. Under "Allow", select List
2. In "Source", type your options separated by commas: North,South,East,West
3. Or reference a range: =Sheet2!$A$1:$A$10

Now users can only select from the dropdown — no typos, no inconsistencies.

Number Restrictions

Restrict cells to accept only numbers within a range:

• Allow: Whole number — Between 1 and 100
• Allow: Decimal — Greater than 0
• Allow: Date — Between 01/01/2026 and 31/12/2026
• Allow: Text Length — Maximum 50 characters

Custom Validation with Formulas

For complex rules, select "Custom" and enter a formula:

No Duplicate Entries

=COUNTIF(A:A, A1)<=1

Must Start with a Letter

=ISNUMBER(FIND(LEFT(A1,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz"))

Date Must Be in the Future

=A1>TODAY()

Email Format Validation

=AND(ISNUMBER(FIND("@",A1)), ISNUMBER(FIND(".",A1,FIND("@",A1))))

Input Messages and Error Alerts

In the Data Validation dialog:

• Input Message tab — Shows a tooltip when the cell is selected, guiding the user on what to enter
• Error Alert tab — Customise what happens when invalid data is entered: Stop (block entry), Warning (allow with warning), or Information (allow with info)

Dependent Dropdowns

Create cascading dropdowns where the second list depends on the first selection. For example, selecting "North" in column A shows only northern cities in column B:

1. Name your lists — select each city list, go to Formulas → Define Name (e.g., "North", "South")
2. In column B validation, use: =INDIRECT(A1)

Audit Existing Validation

To see which cells have validation rules: Home → Find & Select → Data Validation. This selects all cells with validation rules, making it easy to audit and update.

Best Practices

• Always add an input message explaining what's expected
• Use "Stop" error alerts for critical fields, "Warning" for flexible ones
• Keep dropdown source lists on a hidden sheet for maintainability
• Test validation with edge cases — blank entries, copy-paste, and special characters