The phrase "garbage in, garbage out" is especially true for spreadsheets. If you allow users to type whatever they want into your Google Sheets, you will eventually end up with inconsistent formatting, missing information, and broken formulas.
By implementing strict Data Validation rules, you can force users to input data correctly at the source. In this guide, we'll show you how to set up automated validation rules to keep your spreadsheets flawless.
Why Basic Data Validation Isn't Enough
Most people know how to create a simple dropdown menu in Google Sheets (Data > Data validation > Dropdown). While dropdowns are great for categorizing items, they don't solve problems like:
- Users entering invalid email addresses.
- Inconsistent phone number formatting (e.g., +1-555-0100 vs 5550100).
- Leaving critical fields completely blank.
Advanced Custom Formula Validation
To truly protect your data, you need to use Custom Formulas in your data validation rules.
1. Forcing Valid Email Addresses
If you have an email column, you want to ensure every entry contains an "@" symbol and a valid domain. You can enforce this using Regex.
- Select the Email column (e.g., Column C).
- Go to Data > Data validation.
- Under Criteria, select Custom formula is.
- Enter:
=REGEXMATCH(C1, "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$") - Select "Reject input" to prevent bad entries entirely.
2. Preventing Future Dates in Date of Birth Fields
If you're collecting birthdays or historical dates, a user shouldn't be able to enter a date in the future.
- Select the Date column.
- Choose Data Validation > Date.
- Set the condition to is on or before and enter
=TODAY().
Handling Legacy Bad Data
Data validation rules only prevent new bad data from entering the sheet. They do not fix existing bad data. If your sheet already has thousands of rows of messy data, you'll see tiny red warning triangles everywhere, but the data will remain.
To clean up existing data, you need to perform a bulk cleanup. This usually involves removing entirely empty rows, deleting duplicates, and trimming whitespace.
Conclusion
By combining strict Data Validation rules on the front-end with powerful cleanup tools like CleanSheet on the back-end, you can create a bulletproof Google Sheet that your entire team can rely on.
Frequently Asked Questions
Does data validation stop bad data that's already in my sheet?
No. Data validation rules, including custom formula and regex-based rules, only apply to new entries typed after the rule is created — they do not retroactively check or fix existing rows. To clean up legacy bad data, you need a separate bulk cleanup pass, such as removing empty rows and duplicates with CleanSheet.
Can I use a regex formula to validate email addresses in Google Sheets?
Yes. Select your email column, go to Data > Data validation, choose Custom formula is, and enter a REGEXMATCH formula like =REGEXMATCH(C1, "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$"). Set the rule to "Reject input" so entries that don't match a valid email pattern are blocked at the source.
Why do I still see red warning triangles even after setting up validation rules?
Warning triangles appear on cells that violate a validation rule but were entered before the rule existed, or where "Show warning" was selected instead of "Reject input". Switching to "Reject input" prevents new violations, while running a cleanup tool like CleanSheet helps clear out the pre-existing bad rows causing the warnings.