Excel reference

Data Validation

Quick answer

Not a function, but a restriction. Limits what a cell will accept, most often shown as a dropdown list of the only values allowed.

Data → Data Validation → Allow: List → Source: value,value,... (or a range like $D$2:$D$4)

A data validation dropdown doesn't change what a cell is capable of holding; it changes what a person filling it in is allowed to type. That distinction matters for how the list of allowed values gets built: typing the options straight into the dialog works fine for a short, fixed set, while sourcing the list from a range instead means editing that range updates the dropdown everywhere it's used, with the dialog never reopened.

Either way, checking a value against the rule is about the set of allowed values, not the order they happen to have been typed or listed in.

Syntax

Data → Data Validation → Allow: List → Source: value,value,... (or a range like $D$2:$D$4)

Argument	Description
Cell	Required. The cell (or range of cells) the restriction applies to.
Source	Required. Either a typed, comma-separated list of the allowed values, or a reference to a range that already holds them.

Rather than retyping a fixed list of departments into the validation dialog, the source points at a range that already holds them:

Source: =$D$2:$D$4

Editing D2:D4 later, adding a new department or renaming one, changes the dropdown immediately everywhere it's applied, with no need to find and update the validation rule itself.

Worked examples

A typed list

	A	B
1	Order	Status
2	#101

Source: Pending,Shipped,Delivered,Canceled → B2 only accepts one of those four exact values

A list sourced from a range

	A	B	D
1	Employee	Department	Departments
2	Priya		Sales
3			Engineering
4			Support

Source: =$D$2:$D$4 → B2's dropdown shows Sales, Engineering, Support, whatever's currently in D2:D4

Common mistakes

Typing the allowed values with inconsistent case or extra spaces ("yes " vs "Yes"). LearnExcel (like real validation logic downstream) treats the visible value as what matters, so keep the list clean.
Adding an extra, plausible-sounding option that was never actually on the approved list. Read the requirement, don't guess at it.
Forgetting that a range-sourced list still needs to be reproduced correctly in the dropdown. The point is the two stay in sync, not that you skip typing it.

Related functions

Try it yourself

A free, graded practice exercise using this exact function: instant feedback, no signup required.

Practice Data Validation →