Google Sheets data validation: complete guide

every shared spreadsheet eventually has the same problem. someone types “Apac” instead of “APAC”. someone enters a date in MM/DD/YYYY when the rest of the file uses YYYY-MM-DD. someone puts a phone number into the email column. small inconsistencies compound. by the time you try to run a QUERY or build a dashboard, the data is so messy you spend an hour cleaning before you can analyze.

data validation prevents that at the source. you set rules on which cells can hold which values, and Sheets either rejects bad input or warns the user before they save. the cleanup hour goes away because the data was clean to start with.

this guide walks through Sheets data validation from zero. we use a realistic solopreneur stack (orders sheet, customer database, lead capture form) so the patterns mirror real workflows. by the end you will know the seven validation types, the dependent-dropdown trick, and the custom-formula pattern that handles every edge case.

Google Sheets data validation restricts what users can enter in selected cells. Set rules via Data → Data validation. Seven main rule types: list of items, list from range, number, text, date, custom formula, and checkbox. Use “Reject input” to block invalid entries, “Show warning” to allow but flag them. Dependent dropdowns (where one cell’s choices change based on another) use INDIRECT inside list-from-range rules. The fastest way to ship a shared spreadsheet your team can actually use without breaking.

what data validation does and why solo founders should use it

data validation enforces structure. instead of asking your team to remember “always type APAC in caps”, you make APAC the only value the cell accepts. instead of explaining the date format in a comment, you set a date validation rule that rejects anything that is not a valid date.

three concrete benefits:

clean data from the start: no post-hoc cleaning required.
trust in shared sheets: a sheet collaborators trust gets used; one they distrust gets ignored.
better lookups and aggregations: VLOOKUP, INDEX/MATCH, and QUERY all break on inconsistent text. validation prevents the breakage.

every solopreneur who has built a CRM-like sheet, a project tracker, or a content calendar in Sheets has felt this pain. data validation is the smallest investment with the highest return on shared spreadsheets.

opening the data validation panel

three ways to access:

select a cell or range → Data → Data validation.
right-click a cell → View more cell actions → Data validation.
keyboard: select cell → press the Menu key (or right-click) → Data validation.

the validation panel opens on the right. you set:

Add rule for new validation
Apply to range (select where the rule applies)
Criteria (the rule type)
Advanced options (display style, help text, what to do on invalid input)

[SCREENSHOT: Google Sheets right sidebar showing the Data validation panel with a list-of-items rule being created]

the seven data validation rule types

rule type	what it does	best use
list of items	hardcoded options separated by commas	small fixed lists (Yes/No, Priority levels)
list from range	options pulled from a range of cells	dynamic lists that grow over time
number	numeric value within a range	quantities, prices, IDs
text contains / does not contain	basic text pattern	URLs (must contain http), emails (must contain @)
date	valid date, optional range	birthdays, due dates, event dates
custom formula	any formula returning TRUE/FALSE	the unlimited rule type
checkbox	true/false toggle	task completion, included/excluded flags

start with list of items and list from range. they cover 70% of validation use cases. graduate to custom formula when standard rules cannot express your logic.

rule type 1: list of items (the simplest dropdown)

problem: a Status column should only accept “Open”, “In Progress”, “Closed”, “On Hold”.

select the Status column (say E2:E1000).
Data → Data validation.
Add rule.
Criteria: dropdown.
enter the four options, one per line:
– Open
– In Progress
– Closed
– On Hold
(optional) assign colors to each value.
for invalid input: select Reject input.
click Done.

every cell in column E now shows a dropdown arrow. clicking shows the four options. typing anything else is rejected.

[SCREENSHOT: Google Sheets cell with a dropdown showing Open, In Progress, Closed, On Hold options with colored chips]

rule type 2: list from range

problem: a Region column should only accept values that appear in a master list elsewhere in the file.

on a Lookup sheet, list the regions in column A: APAC, EMEA, NA, LATAM, MENA. add new regions there as your business grows.
on the data sheet, select the Region column.
Data → Data validation → Add rule.
Criteria: dropdown (from a range).
select the source range: Lookup!A2:A100 (or use a named range like AllRegions).
Reject input on invalid.

every Region cell now has a dropdown of regions from the Lookup sheet. add a new region there, the dropdown updates everywhere.

this is the right pattern for any value list that might grow over time. tagging it with a named range makes it cleaner.

rule type 3: number ranges

problem: a Quantity column should accept positive integers only.

select the Quantity column.
Data → Data validation → Add rule.
Criteria: Is between → 1 and 10000.
Reject input.

now any non-numeric or out-of-range value is rejected.

variants:
– Greater than: minimum-only validation
– Less than: maximum-only
– Is equal to: exact value (rare)
– Is not between: exclude a range

note: by default, decimal numbers pass. for integer-only validation, use a custom formula (covered below).

rule type 4: text contains / does not contain

problem: an Email column should reject anything without an “@”.

select the Email column.
Data → Data validation → Add rule.
Criteria: Text contains → @
Reject input.

cheap but effective email validation. add another rule for “Text contains → .” to also require a domain dot. or move to custom formula for proper email validation.

rule type 5: date validation

problem: a Due Date column should accept future dates only.

select the column.
Data validation → Add rule.
Criteria: Date is on or after → today.
Reject input.

variants:
– Is exactly: a specific date
– Is between: a date range
– Is on or before: a deadline cutoff
– Is valid date: any valid date passes

rule type 6: custom formula (the most powerful)

custom formulas unlock anything. write a formula that returns TRUE for valid input, and Sheets accepts it. FALSE means rejected.

example 1: integer-only validation

=AND(ISNUMBER(A2), A2=INT(A2), A2>0)

returns TRUE if the value is a positive integer.

example 2: email validation with regex

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

returns TRUE for strings shaped like an email. far stricter than just “contains @”.

example 3: prevent duplicates in a column

=COUNTIF($A$2:$A, A2)<2

returns TRUE only if the value does not yet appear elsewhere in the column. great for unique customer IDs or order numbers.

example 4: dependent date logic

problem: a Project End Date must be after the Project Start Date in the same row.

=B2>A2

assuming Start is in column A and End in column B. only validates correctly when applied to the End column.

example 5: validation referencing multiple cells

problem: in a quote calculator, a Discount cell should not exceed the SubTotal cell.

=B2<=A2

simple, effective. custom formulas reference cells with relative references that adjust as the rule applies down the column.

dependent dropdowns (the killer pattern)

problem: column A is a Country dropdown. column B should show only cities in the selected country.

setup:

on a Lookup sheet, structure cities by country:
– col A: country (USA, UK, SG)
– col B: cities for USA (NY, LA, SF)
– col C: cities for UK (London, Manchester)
– col D: cities for SG (Singapore)
each city list gets a named range matching the country name: USA, UK, SG.
on the data sheet, the Country column gets a list-from-range validation pointing at Lookup!A2:A4.
the City column gets a custom formula validation:

=INDIRECT(A2)

now in B2: type a country in A2 (say “USA”), the City dropdown in B2 shows NY, LA, SF. change A2 to “UK”, B2’s dropdown shows London, Manchester.

note: data validation in Sheets does not natively support “from range” with a dynamic INDIRECT in newer Sheets versions cleanly. workaround:

set the City column’s validation to “List from a range”.
use the formula =INDIRECT(A2) as the range source.

if your Sheets version blocks INDIRECT in list-from-range, fall back to a custom formula validation that uses MATCH against the relevant range.

dependent dropdowns are how solopreneur Sheets stop feeling like spreadsheets and start feeling like apps.

numbered walkthrough: build a clean lead-capture sheet

we will build a lead-capture sheet with full validation.

headers in row 1: Name, Email, Phone, Country, City, Source, Date Added, Status.
select B2:B1000 (Email). Data validation → custom formula → =REGEXMATCH(B2, "^[^\s@]+@[^\s@]+\.[^\s@]+$") → reject.
select C2:C1000 (Phone). custom formula → =AND(ISNUMBER(VALUE(SUBSTITUTE(SUBSTITUTE(C2," ",""),"-",""))), LEN(SUBSTITUTE(SUBSTITUTE(C2," ",""),"-",""))>=8) → reject. allows formats like “+65 9123 4567” while requiring at least 8 digits.
select D2:D1000 (Country). list from range → Lookup!Countries. reject.
select E2:E1000 (City). list from range → =INDIRECT(D2). reject.
select F2:F1000 (Source). list of items → Referral, Website, LinkedIn, Cold Outreach, Other. reject.
select G2:G1000 (Date Added). date is valid date. reject.
select H2:H1000 (Status). list of items → New, Contacted, Qualified, Converted, Lost. reject.

every cell now constrains its content. paste a 50-row CSV into it, the bad rows are visible immediately because Sheets flags them with red triangles.

[SCREENSHOT: Lead capture sheet with dropdown arrows visible in Country, City, Source, and Status columns, with red triangles flagging invalid entries in Email and Phone]

comparison: validation in Sheets vs Excel vs Airtable

feature	Google Sheets	Excel	Airtable
dropdown lists	yes	yes	yes
list from range	yes	yes (Tables)	yes
dependent dropdowns	yes (with INDIRECT)	yes (with named ranges + INDIRECT)	yes (linked records)
email regex validation	yes (custom formula)	yes (custom formula)	yes (built-in field type)
phone validation	yes (custom formula)	yes (custom formula)	yes (built-in)
auto-cleanup of bad data	no	no	partial
visible-state validation	red triangle on cell	red border or icon	inline error message

Sheets and Excel are functionally similar for validation. Airtable is purpose-built for structured data and edges out for new projects, but for solopreneurs already in Sheets, validation rules cover 90% of the gap.

validation display options

three display styles for invalid input:

style	behavior	when to use
Reject input	Sheets blocks the entry	strict columns: emails, IDs, money values
Show warning	input goes through, red triangle warns	flexible columns: notes, optional fields
(no validation set)	anything passes silently	columns where you trust the input

start with Reject for must-be-clean fields and Show warning for nice-to-have-clean fields. the worst pattern is “show warning” everywhere, because users learn to ignore the triangles.

common data validation mistakes

mistake 1: validation on the wrong range

apply rules to the entire intended range (B2:B1000, not just B2). otherwise new rows escape validation.

mistake 2: forgetting to update list-from-range as the source grows

a list pointing at A2:A10 misses entries added at A11. either use unbounded ranges (A2:A) or use a named range you control centrally.

mistake 3: rejecting too aggressively

a strict regex that rejects every name with an apostrophe (“O’Brien”) will frustrate users. test rules against real edge cases.

mistake 4: not communicating why input was rejected

users see a generic “invalid input” message. add custom help text (Advanced options → Show validation help text) explaining what is expected.

mistake 5: layering too many rules on one cell

one cell can have only one validation rule. if you need multiple constraints, combine them in a single custom formula with AND or OR.

conclusion: validate early, save hours later

every solopreneur who has watched their CRM-like Sheets file slowly turn to mush has paid for the absence of data validation. an extra 10 minutes when you set up the sheet saves 10 hours of cleanup six months later, and saves the embarrassment of bad numbers in client reports.

start with the highest-impact column on your most-shared sheet. usually that is Status, Region, or Email. add one validation rule. share the file. notice that the column stays clean.

then add validation to every column that should follow a structure. the discipline becomes second nature, and your spreadsheets become small databases that your team can actually rely on.

next action: open the shared spreadsheet you maintain. count how many cells you have manually corrected in the past month. add validation to those columns. the next month of corrections drops to zero, and the dashboards built on top of the data become trustworthy.

Google Sheets Data Validation Complete Guide (2026)