Number & Date Rules as Check Formulas
All lessons
- Number & Date Rules as Check FormulasFree
- List Rules: Allowed Values with COUNTIF & MATCHFree
- Dependent Lists: Category → Item with INDEX/MATCH Pro
- Text Length & Pattern Checks: LEN, LEFT, SEARCH Pro
- Duplicate Prevention: COUNTIF & COUNTIFS Rules Pro
- Cross-Field Rules: End ≥ Start and Shares = 100% Pro
- Input Form with Error Messages & an Error Summary Pro
- Template Protection Checklist & Completeness Dashboard Pro
You are not signed in — the lab works, but your progress is not saved. Start free
Read first
In Excel: the Data Validation dialog
- Select the input cells (e.g.
B2:B9), then Data > Data Validation. - Settings tab: Allow = Whole number, Decimal, Date, Time, Text length, List or Custom; Data = between, greater than … ; Minimum/Maximum may point to cells such as
=$B$13. - Input Message tab: a yellow tooltip that tells the user the rule before typing (e.g. “1 to 500 units”).
- Error Alert tab: Stop blocks the entry, Warning and Information only warn. Write a helpful message: what is allowed, not just “Wrong”.
- Already filled data? Data Validation > Circle Invalid Data draws red circles around existing entries that break the rule.
Custom rule = check formula
Under Allow: Custom you type a formula written for the top-left cell of your selection, e.g. =AND(B2=INT(B2),B2>=$B$13,B2<=$C$13) for B2:B9. Excel moves the relative B2 for every other cell, exactly like copying a formula down.
Loading the spreadsheet…