What is data validation in Google Sheets?
Quick answer
Data validation is a rule on a cell or range in Google Sheets that limits what people can enter, most often a drop-down list of allowed values (Booked, Done, Paid), but also numbers in a range, valid dates or checkboxes. Entries that break the rule are rejected or flagged with a warning.
Last updated
Updated · By Robert Breen
Why it matters
Formulas, filters and color rules only work if the data is consistent. One person types "Done", another "done ", a third "Finished", and a SUMIF on "Done" quietly misses two of them. A drop-down makes everyone pick the same spelling.
It also makes a sheet easier for people who rarely use spreadsheets. Picking from a list on a phone is faster and less error-prone than typing.
A worked example
An event venue tracks 60 bookings with a Payment column. Before validation the column held "paid", "PAID", "Pd", "deposit" and blanks, and the "unpaid total" formula was off by about $3,000. The manager selects the column, adds a Dropdown rule with Deposit, Paid in full and Unpaid, and sets invalid entries to be rejected.
She fixes the old entries once using the drop-downs. The next week the unpaid total matches the bank exactly, and new staff cannot type anything else.
Often confused with
Data validation vs conditional formatting
Validation controls what can be entered. Conditional formatting changes how a cell looks based on what is in it. They work well together.
Reject vs show a warning
Reject blocks any value that is not allowed. Show a warning lets it in but marks the cell, which is gentler for columns where exceptions happen.
Sheet validation vs form validation
Web forms check answers before they are submitted. See form validation in the AI glossary. Sheet validation checks values typed straight into cells.
Tips
- Apply the rule to the whole column (for example D2:D1000) so new rows get it.
- Keep long option lists on a separate tab and point the drop-down at that range.
- Use colors on the options so status stands out at a glance.
Related terms
Learn it in a free lesson
- Google Sheets Tutorial: Build a Job Tracker That Totals Itself (about 15 minutes)
Related in the AI glossary
Sources
Written from these official help pages. Menus change, so if a button has moved, the help page has the current wording.
Frequently asked questions
- How do I add a drop-down in Google Sheets?
- Select the cells, choose Data > Data validation > Add rule, set Criteria to Dropdown, type the options and click Done. Insert > Dropdown is a shortcut.
- Can a drop-down use a list from another tab?
- Yes. Choose "Dropdown (from a range)" and select the range that holds the options.
- Does pasting bypass data validation?
- Pasted values that break the rule are flagged or rejected depending on your setting, but check after big pastes.
All google workspace terms, A to Z · Free google workspace lessons