Status dropdown
Reject input on Stage column pulling from Settings list so pivot charts never see typo statuses.
What is data validation in Google Sheets?
Data validation restricts what can be entered in a cell before bad data spreads through your model. Rules can enforce dropdown lists, number ranges, dates, text length, or custom formulas that return TRUE. Invalid entries can be rejected or warned with a note. Validation is the front door quality check for shared trackers and intake sheets.
When to use it
Use validation on status columns, currency amounts, dates, email-shaped text, and any field that feeds VLOOKUP, pivot tables, or client-facing reports.
When to skip it
Skip validation on free-form notes, one-off scratch columns, and creative brainstorming tabs where rigid rules slow people down without payoff.
How it works
-
1
Select cells, then Data > Data validation.
-
2
{:"Pick criteria type"=>"list, number, date, text, checkbox, or custom formula is."}
-
3
Choose Reject input or Show warning for invalid attempts.
-
4
Add help text explaining allowed values for editors who do not read README tabs.
-
5
Point list validation at a Settings range or chip list for maintainable options.
-
6
Audit with conditional formatting on ISERROR or custom checks for legacy rows before validation existed.
Examples in Google Sheets
Date must be future
Custom formula =A2>TODAY() on due date column blocks accidental backdated deadlines on intake form linked sheet.
Number between 0 and 100
Percent entry column allows only 0-100 with warning text Score must be 0 to 100.
Build this without starting from a blank cell
Use a Better Sheets tool for Data validation, then watch a walkthrough when you want the full pattern.
Better Sheets resources
Common mistakes
- Validating after thousands of dirty rows already exist without cleanup pass first.
- Allow invalid input warning only, then ignoring yellow warnings forever.
- List source on editable tab someone deletes mid-quarter.
- Custom formula written for row 2 but applied wrong relative to top-left of range.
- Validation on merged cells behaving unpredictably for editors.
Frequently asked questions
- Validation vs dropdown?
- Dropdown is one validation type. Validation also covers numbers, dates, checkboxes, and custom logic.
- Validation vs protect?
- Validation limits values. Protection blocks edits entirely. Use both on templates.
- Dependent dropdown?
- FILTER a list based on parent cell, use result range as validation source for child column.
- Validation on paste?
- Bulk paste may bypass per-cell rejection depending on content; audit after large imports.
- Checkbox validation?
- Insert > Checkbox or validation criteria checkbox type for TRUE FALSE consistency.
- Copy validation down?
- Copy cell and paste special validation to new rows in growing tables.
- Validation custom formula tip?
- Write formula as if top-left cell of range is active, like dropdown conditional formatting.
- Remove validation?
- Select cells, Data > Data validation, Remove validation.
Related Tutorials
Watch how Data validation works
Data Validation with Formulas
You Should Know The Limitations of Data Validation
Restrict Access to a Cell if Another Cell is Blank
Related blog posts
Guides that explain Data validation in more depth.
Getting Started in Google Sheets Spreadsheet Management
Users can also customize the appearance of their spreadsheets with different fonts, colors, and themes.
Read post →Philippines Phone Number Validation: Use of REGEXMATCH in Google Sheets
Every school year thousands of students and teachers’ data were being added to the school database. As someone who was always given the task to narrow down lots of information after the school enrollment, validating students’ data manually is very exhausting. Take this as an example, when you ask students to include the phone numbers of their emergency contact person, there are some instances wherein the declared numbers were invalid due to human error. And the assigned personnel during enr...
Read post →The Hidden Joys of a Well-Crafted Spreadsheet
We delve into how a good design can make it easier to find, edit, and input data, and how it can be beneficial to your everyday work. It can help yourself and others.
Read post →Related glossary terms
dropdowns / data validation
Data validation restricts what people can type in a cell. A dropdown is the friendly face of that feature: pick from a list instead of free text. It keeps statuses, categories, and owners consistent so filters, pivot tables, and scripts do not break on typos.
Read guide →protect sheets/ranges
Protection limits who can edit specific cells, ranges, or entire tabs while others keep view or comment access. It is how you let teammates update input columns without touching formula blocks, summary rows, or archived data. Protection is not encryption; owners and editors with rights can still adjust protections.
Read guide →Checkbox
A checkbox is a cell control that stores TRUE when checked and FALSE when unchecked. Insert checkboxes from Insert > Checkbox or data validation. They power task lists, approval flags, and conditional formulas without typing yes or no by hand.
Read guide →Google Forms
Google Forms can send each submission as a new row in a linked spreadsheet. Create the form from Forms or insert a form from Sheets. Response columns match question titles. Sheets becomes the system of record for analysis, mail merges, and Apps Script automation while Forms handles data entry UX on phone and desktop.
Read guide →Done reading about Data validation?
Membership unlocks 636+ tutorials, unlimited generators, and every template. Practical lessons. Zero fluff.
Need this once
Jump to a free tool or a single tutorial for this topic.
Learning Sheets for real
Unlock the full library, generators, and templates with membership.