Excel Data Validation: Complete Guide to Controlling Cell Input

Data validation lets you restrict what values can be entered into a cell, preventing bad data before it becomes a problem. You can create dropdown lists, limit numbers to a range, restrict dates, and more — especially useful in shared workbooks.

Where to Find Data Validation

1. Select the cell or range you want to validate
2. Go to Data tab > Data Validation
3. Set your criteria in the Settings tab

Keyboard shortcut: Alt > D > L (press keys in sequence)

Creating a Dropdown List

This is the most popular type of data validation. It forces users to pick from a predefined set of values.

Method 1: Type Values Directly

1. Data Validation > Settings tab
2. Allow: List
3. In the Source box, enter values separated by commas:

New York,Los Angeles,Chicago,Houston,Phoenix

Method 2: Reference a Cell Range

Maintain your list on a separate sheet for easier updates.

1. Enter items in a “Lists” sheet, e.g., A1:A5
2. In the Source box, enter:

=Lists!$A$1:$A$5

Tip: If the list may grow, convert it to a Table or use OFFSET/INDIRECT to make the range dynamic.

Method 3: Use a Named Range

1. Select your list range > Formulas tab > Define Name > Name: “CityList”
2. In the Source box, enter:

=CityList

Named ranges keep formulas clean and let you reuse the same list across multiple validation rules.

Restricting Numbers to a Range

Limit cells to accept only numbers within a specific range.

Example: Allow Only Quantities Between 1 and 100

1. Allow: Whole number
2. Data: between
3. Minimum: 1, Maximum: 100

Restriction Type	Description
Whole number	Integers only
Decimal	Numbers with decimals
Between	Min–Max range
Greater/Less than	Relative to a single value

Restricting Dates

Example: Only Allow Future Dates

1. Allow: Date
2. Data: greater than or equal to
3. Start date: =TODAY()

Example: Only Allow Dates Within 2026

1. Data: between
2. Start date: 2026-01-01
3. End date: 2026-12-31

Restricting Text Length

Example: Notes Column — Max 50 Characters

1. Allow: Text length
2. Data: less than or equal to
3. Maximum: 50

Custom Formula Validation

The most flexible option. The cell accepts input only when your formula returns TRUE.

Example: Prevent Duplicate Entries

Block duplicate values in column A:

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

Example: Require a Specific Prefix

Product codes must start with “PRD-”:

=LEFT(A1, 4)="PRD-"

Example: Basic Email Validation

Ensure the value contains an ”@” symbol:

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

Input Messages and Error Alerts

Input Message

Display a helpful hint when the cell is selected.

1. Data Validation > Input Message tab
2. Title: “Select a city”
3. Message: “Choose a city from the dropdown list”

Error Alert

Show a message when an invalid value is entered.

1. Error Alert tab
2. Choose a style:

Style	Behavior
Stop	Blocks invalid input entirely
Warning	Shows a warning; user can override
Information	Shows info; user can override

Recommendation: Use “Stop” when data accuracy is critical. Use “Warning” for advisory rules.

Copying and Removing Validation

Copy

Copy a validated cell > select target range > Ctrl+Alt+V (Paste Special) > check Validation

Remove

Select validated cells > Data Validation > click Clear All

Quick Reference

Goal	How to Set It
Limit choices	List > Dropdown
Restrict numbers	Whole number/Decimal > Between
Restrict dates	Date > Start/End
Complex rules	Custom formula
Guide users	Input Message + Error Alert

Setting up data validation takes about a minute, but it can save hours of data cleanup later.