Excel data validation examples: number, date and list

Set three entry rules on a small form, then test accepted and rejected inputs before relying on it.

Illustrative data. English function names and comma argument separators are shown; Excel may use different separators or translated function names. The file was structurally inspected, but native Excel calculation and UI behavior need local verification.

Try three rules

The workbook puts editable examples in B2:B4. B2 accepts whole numbers from 1 to 10: 5 is valid and 11 should be rejected. B3 accepts dates from 21 through 30 September 2026: 24 September is valid and 1 October should be rejected. B4 offers Open, Closed and On hold; Other is outside that list.

Select B2 and use Data → Data Validation → Allow: Whole number → between 1 and 10.
Select B3 and use Allow: Date → between the two dates. Use unambiguous dates in your locale; the file stores a true Excel date.
Select B4 and use Allow: List with the three exact status values.

Test the stop behavior

After selecting an input cell, check its validation rule and Error Alert settings. A rule can warn instead of stopping invalid entries. Pasted or inherited values may bypass or predate validation: use Excel’s Circle Invalid Data or a separate review formula to inspect an existing sheet. Data validation guides entry; it is not a retrospective guarantee that every value is valid.

If the choices in one list must depend on another cell, use the dependent-list example.

Compatibility and source

Compatibility: The formulas and rules use desktop Excel’s conditional formatting or Data Validation controls. Excel for web and Mac may present different menus or rule editors. Check each rule after opening the file in your edition; native dialog behavior has not been tested here.

Formula and UI behavior are described in Microsoft’s Excel guidance. Start with the broad formula and shortcut reference or browse more Excel tips.