Google Sheets™ data validation not working when people paste? Here's why
You set a dropdown on a Status column. A week later the dropdown is gone from half the rows and the column holds free text again. Nobody deleted the rule: they pasted over it. This is the most misunderstood behaviour in Google Sheets™ validation.
What a paste actually does
A normal paste copies more than the value. It carries the source cell's formatting, and its validation (or lack of one), onto the target cell. Paste a plain value into a dropdown cell and the dropdown rule is replaced by "no rule". The cell keeps its new value and loses its protection in one keystroke.
The fixes
- Paste values only. Right-click → Paste special → Values only (or the equivalent shortcut) writes the value without touching the target's validation. This is the habit to teach every collaborator.
- Set validation to reject input, not just warn. Open Data → Data validation, pick the rule and choose Reject the input. Rejected values bounce even when pasted.
- Re-check validation regularly. Rules die silently; a monthly look at Data → Data validation catches the gaps before they matter.
Find every lost rule
Shared Sheet Guardrails™ checks a Google Sheets™ file for lost and broken validation, typed-over formulas and the rest, with by-hand fixes for each. Checking any sheet is free.
Install from the Google Workspace Marketplace™
Google, Google Sheets™ and Google Workspace™ are trademarks of Google LLC. App Garden™ is not affiliated with or endorsed by Google.