Excel Data Validation

What Data Validation Actually Does

Data Validation is a rule attached to a cell or a range of cells that restricts what can be typed into it. Instead of trusting everyone to type 'Open', 'open', or 'OPN' consistently, you can force the cell to accept only a value from an approved list, a number inside a range, or a date inside a window. If someone tries to enter something outside the rule, Excel can warn them or block the entry outright, depending on how you configure it.

Opening the Data Validation Dialog

1. Select the cell or range you want to restrict.
2. Open the Data tab and click Data Validation.
3. On the Settings tab, choose what to Allow.
4. Fill in the criteria that appear (a range, a list, a date window, and so on), then click OK.

Building a Dropdown List

The List option is the most common use of Data Validation. Choose Allow: List and either type the choices directly, separated by commas, or point the Source box at a range of cells that holds the choices (handy when the list is long or changes over time). Once applied, the cell shows a small dropdown arrow so users can pick a value instead of typing one - which also rules out typos entirely.

Allow:  List
Source: Open,In Progress,Closed

Or, to pull the choices from cells instead of typing them:
Source: =$D$2:$D$4

Custom Rules with a Formula

The Custom option accepts any formula that evaluates to TRUE or FALSE for the active cell. This is how you build rules that check other cells rather than just a fixed range - for example, making sure an End Date always falls after the Start Date typed in the row, or making sure a Quantity stays between 0 and the amount currently in stock.

Custom formula: =AND(B2>=0,B2<=100)
Applies to the Quantity column - blocks anything outside 0-100

Custom formula: =B2>A2
Applies to the End Date column - blocks an end date earlier than the Start Date in A2