The moment more than one person types into a shared sheet, the data starts to drift: text where a number should go, “N/A” next to “n/a” next to a blank, dates in three different formats. Data validation is how you stop that at the source — it controls what a cell will accept before a bad value ever lands. This guide covers the current Google Sheets validation UI, dropdowns from a list or a range, number/date/text rules, custom-formula validation for the cases the menu can’t express, and the reject-vs-warning behavior that trips most people up.
How data validation works in Google Sheets
A validation rule is attached to a cell or range and checked every time someone edits that cell. If the new value passes the rule, it’s accepted; if it fails, Google Sheets either blocks it or flags it, depending on how you configured the rule. The rule lives with the cell, so it keeps working when other people open the file, when the sheet is copied, and when rows are inserted inside the validated range.
That persistence is the whole point. Unlike a one-time “find and fix” cleanup, a validation rule keeps enforcing itself on every future edit, including edits made months later by someone who never saw your original instructions. It turns a convention you have to remember into a constraint the sheet remembers for you.
In the current interface (updated in 2024), validation moved into a side panel with chip-style dropdowns rather than the old modal dialog. The steps:
- Select the cell or range you want to control.
- Open Data → Data validation, then click Add rule in the side panel.
- Pick a criterion under Criteria (dropdown, number, date, text, or custom formula).
- Choose what happens on a bad entry — Show warning or Reject the input — and click Done.
The exact labels match Google’s official data validation documentation, which is worth bookmarking since the panel wording shifts between updates. If you also manage spreadsheets in Excel, the same controls in Excel live under Data → Data Validation with slightly different rule names.
Where validated cells show up
Cells with a list rule render a small dropdown chip; other rules are invisible until someone enters a failing value. That invisibility is why a validation rule and a matching conditional formatting rule are often paired — validation blocks bad input going forward, while formatting highlights anything that slipped in before the rule existed. To see or remove a rule later, reselect the cell and reopen the Data validation panel; deleting the rule there leaves the existing values untouched.
Building dropdown lists from items or a range
Dropdowns are the most common validation rule because they remove free-text entirely. Instead of trusting everyone to type “In progress” the same way, you give them a fixed set of choices to click. There are two ways to build one, and the right choice depends on how often the list changes.
List of items (fixed values)
Choose Dropdown as the criterion and type the options directly: Open, In progress, Done. Best for short, stable lists that rarely change — statuses, priorities, yes/no flags. The downside is that editing the options means reopening the rule on every range that uses them.
List from a range (maintainable)
Point the rule at a column of values instead, so editing the list is just editing cells. Put your options somewhere on the sheet:
| A | B | |
|---|---|---|
| 1 | Task | Status options |
| 2 | Draft outline | Open |
| 3 | Review copy | In progress |
| 4 | Done |
Set the dropdown criterion to List from a range and enter B2:B4. Add a status to B5 and every dropdown using that range updates automatically — no rule editing required. For lists that grow, reference the whole column (B2:B) so new rows are picked up without touching the rule at all.
Lists. Your data range stays clean, collaborators won’t accidentally edit the options, and you can reuse the same list across multiple sheets in the file.
Number, date, and text rules
When you don’t want a fixed list but still need to constrain values, the built-in criteria handle the common cases without any formula. Each maps to a real-world guardrail: a quantity that can’t be negative, a deadline that can’t be in the past, an ID that must contain a prefix.
| Goal | Criterion | Example setting |
|---|---|---|
| Whole numbers 1–100 | Number → between | 1 and 100 |
| Future dates only | Date → on or after | today |
| Non-empty text | Text → contains | (a required substring) |
| Anything from a set | Dropdown | list of items / range |
Number and date rules reject values outside the range; text rules check for a substring or pattern. They cover a surprising amount of day-to-day data entry — quantities, prices, due dates, order codes — without ever writing a formula. The limit shows up when a rule depends on another cell or on a pattern: “must be a valid email”, “must be greater than the cell to the left”, “must match our SKU format”. For those, you reach for a custom formula.
Custom formula validation for real rules
The Custom formula is criterion accepts any formula that returns TRUE or FALSE for the edited cell. This is where validation goes from “pick from a list” to genuinely enforcing business rules, because you have the full formula language available — text functions, comparisons, lookups, and references to other cells.
Pattern and email checks
=REGEXMATCH(A2, "^[^@]+@[^@]+\.[^@]+$")Applied to A2, this accepts the entry only if it looks like an email address. REGEXMATCH returns TRUE/FALSE, which is exactly what the validation engine needs, and the same approach validates phone numbers, postal codes, or any format you can describe with a regular expression. To restrict a cell to numbers only, =ISNUMBER(A2) is more reliable than the Number criterion when the column also receives pasted text that looks numeric but isn’t.
Rules that reference other cells
=B2>A2On column B, this forces every “end date” to be after the matching “start date” in column A. Because the formula is relative, row 3 automatically checks B3>A3, and so on down the column. Custom formulas can also drive dynamic dropdowns when combined with helper ranges — the same dependent-list idea you’d use for cascading menus, and a natural companion to the QUERY function when your option list is itself the result of a query rather than a fixed column.
A2 if the range starts at row 2). Sheets adjusts the reference per row automatically — hard-coding $A$2 applies the row-2 check to the entire column and silently breaks every other row.
Reject input, show warnings, and guide users
Every rule has an “on invalid data” behavior, and the choice changes how strict the sheet feels. It’s the single most important setting to get right, because it decides whether a bad value is impossible or merely discouraged.
Add help text in the rule’s advanced options so the error explains what’s expected — “Enter a date after the start date” beats a generic rejection. Clear help text is the difference between a rule people work with and one they fight, especially for collaborators who weren’t around when the sheet was designed. A good rule of thumb: if the reason for the rule isn’t obvious from the column header, write it into the help text.
Common mistakes and how to fix them
Most validation problems trace to a handful of causes. Run this checklist before assuming the feature is broken:
- ✓ Custom formula references the range’s top-left cell, not an absolute
$A$2 - ✓ “List from a range” points at the current extent of the list (extend it when you add rows, or use a whole-column reference like
B2:B) - ✓ The rule is set to Reject if you actually need to block input — Warning only flags
- ✓ Pasted values are re-checked: pasting can bypass warnings, so pair critical rules with conditional formatting
- ✓ A number stored as text isn’t silently passing a number rule — clean the column first with VALUE or a re-type
The most common surprise is that paste operations can carry invalid values past a “warning” rule. Someone copies a block of cells from another sheet, pastes it in, and the warnings simply don’t fire on the pasted content. If the data absolutely must stay clean, use Reject, and back it with a formatting rule that highlights anything out of bounds so you can catch historical entries that predate the rule. Treat validation and formatting as two halves of the same job: one prevents new errors, the other exposes old ones.
Putting it to work
That single change removes the majority of entry errors in a shared sheet. From there, custom formulas let you encode the rules specific to your data — email patterns, date ordering, cross-cell checks — so the spreadsheet enforces them whether or not anyone remembers to. Validation is quiet infrastructure: set it once, and every future edit stays inside the lines you drew.