Data Validation in Google Sheets

August 7th, 2026

Formulas cannot fix every data problem after the fact. The cheapest clean data is data that was never allowed to be dirty. Data → Data validation (or right-click → Data validation) lets you restrict what users can enter: dropdown lists, number and date bounds, checkboxes, and custom formulas. Pair validation with the cleaning techniques in Data Cleaning, Sorting, and Analysis Tips in Google Sheets and your analysis stops fighting typos.

Where validation lives

1. Select the cells that should be constrained (usually a whole input column: B2:B).
2. Data → Data validation.
3. Choose criteria, optional help text, and whether invalid input is rejected or only warned.
4. Optionally show a dropdown affordance for list-based rules.

Apply rules to open-ended ranges (B2:B) so new rows inherit the same constraints without you reopening the dialog every week.

List of items — fixed dropdown

Best for short, stable enums: status, region, priority.

Criteria: Dropdown (from a list) / list of items, e.g. Active, Pending, Closed.

Users pick from the list; free-typed values that are not on the list can be rejected. This kills the classic Active / active / ACTVE mess that breaks COUNTIF and pivot tables.

List from a range — maintainable dropdown

When the allowed values change, store them on a helper sheet (e.g. Lists!A2:A) and point validation at that range.

| Lists!A | | | --- | --- | | Active | | | Pending | | | Closed | |

Criteria: Dropdown (from a range) → Lists!A2:A.

Adding a new status is one cell edit, not a validation rewrite on every form sheet. Combine with UNIQUE or a sorted source column if the list is derived from live data.

Number and date rules

Common criteria:

• Number — between, greater than, integer only (quantities, scores, ages)
• Date — valid date, on or after =TODAY(), between project start and end
• Text length — max characters for codes or notes
• Checkbox — see below

Example intent: “Amount in column D must be a number ≥ 0.” Invalid text like TBD never enters the model, so SUM and charts stay trustworthy.

For date-heavy models, validation plus the patterns in Working with Dates in Google Sheets: A Comprehensive Guide keeps serial dates consistent.

Custom formula — when the built-ins are not enough

Custom formula rules must return TRUE for allowed values. Sheets evaluates the formula relative to the top-left cell of the validated range.

Email-shaped text with REGEXMATCH

For a range starting at B2:

=REGEXMATCH(B2, "^[^@\s]+@[^@\s]+\.[^@\s]+$")

That is a practical email shape check, not a full RFC validator — enough to catch missing @ and obvious typos. For richer contact cleanup after import, see Clean Names and Emails in Google Sheets with REGEXEXTRACT and SPLIT. Sheets also has ISEMAIL for a simpler boolean test you can use in formulas or in a custom validation rule:

=ISEMAIL(B2)

Value must exist in a master list

=COUNTIF(Lists!A:A, B2)>0

Useful when you want strict referential integrity without a visible dropdown, or when the list is long.

Cross-column rule (end date after start date)

With start in C2 and end in D2, validate D2:D with:

=D2>=C2

Unique values in a column

=COUNTIF($B$2:$B, B2)=1

Rejects duplicate IDs as soon as someone pastes a second copy.

Checkboxes — TRUE / FALSE inputs

Insert → Checkbox (or validation criteria Checkbox) turns a cell into a boolean control. Checked is TRUE; unchecked is FALSE.

=IF(E2, "Done", "Open")
=COUNTIF(E2:E, TRUE)
=FILTER(A2:C, E2:E=TRUE)

Checkboxes beat "Yes"/"No" text for filters, QUERY, and conditional formatting because the type is already logical. They also play well with dashboard patterns in How to Use FILTER + XLOOKUP for Dynamic Dashboards.

Validation and cleaner analysis

| Without validation | With validation | | --- | --- | | Status spelled five ways | One canonical list | | Amounts entered as text | Numbers only → reliable SUM | | End date before start | Custom formula blocks it | | “Done” as free text | Checkbox → real TRUE/FALSE |

Validation is not a substitute for IFERROR on calculated columns, but it dramatically reduces how often those guards fire. Highlight invalid historical rows with Advanced Conditional Formatting Techniques in Google Sheets when you inherit a sheet that never had rules.

Practical setup checklist

1. Decide which columns are inputs vs formulas — only validate inputs.
2. Put dropdown sources on a dedicated Lists sheet; protect it if needed.
3. Prefer reject for structured fields (IDs, amounts); warn for notes if collaborators need flexibility.
4. Add short help text (“Pick a status from the list”) so users are not stuck.
5. Test paste and Autofill — both should still honor the rule.

Common mistakes

• Validating a single cell instead of the column. New rows skip the rule. Use B2:B (or a named range that grows with your layout).
• List of items with inconsistent spelling in the rule itself. The dropdown becomes the source of truth — proofread it once.
• Custom formula written for the wrong anchor cell. If the range starts at B2, the formula should reference B2, not B1 or absolute $B$2 unless you mean “compare everyone to one cell.”
• Using validation as the only “security.” Determined users can copy values from elsewhere; protect sheets when policy requires it.
• Forgetting blank handling. Decide whether empty cells are allowed; many rules fail closed on blanks unless you explicitly allow them with =OR(B2="", …).

Going further

Lock down inputs with validation, then keep formulas robust with Dealing with Errors in Google Sheets. For URL fields, Working with Hyperlinks and URLs in Google Sheets covers ISURL and link hygiene. Related functions you will use beside validation: REGEXMATCH, ISEMAIL, COUNTIF, FILTER.