Validate Cell Entries
Shared spreadsheets rot from the inside: dates in the name column, 'N/A' in the numbers, typos no one catches until the report is wrong. Data Validation locks cells to sensible inputs — dropdowns, ranges, formats — so bad data cannot enter. This guide bulletproofs your sheets.
TL;DR / Quick Fix
Quick fix: stop typos in shared sheets with Data Validation — select the cells, go to Data, Data Validation, and set allowed values like a dropdown list, whole numbers, or dates. Users pick from valid options instead of free-typing. Add an input message explaining the expected format.
Follow these steps
- Select the cells to guard. Highlight the column or range where junk appears. Validation applies to the selection — target the messy columns first.
- Open Data Validation. Data tab, Data Validation. The dialog offers Allow types: List, Whole Number, Decimal, Date, Text Length, Custom.
- Build a dropdown list. Allow: List, then type options separated by commas or point at a range. Users pick instead of typing — typos become impossible.
- Set numeric and date ranges. Whole Number between 1 and 100; Date after today. Ranges catch the fat-fingered 1000 where 100 was meant.
- Add input and error messages. Input Message guides ('Enter a date after today'); Error Alert stops or warns on violations. Guidance plus guardrails beats either alone.
- Combine with formatting. Validation prevents bad entry; conditional formatting highlights what is already there. Together they keep shared sheets honest.
What NOT to do
- Do not validate without explaining. A cryptic error with no input message just frustrates — tell users what is expected.
- Do not forget existing bad data. Validation guards new entries; circle invalid existing data to find the old rot.
- Do not copy-paste over validation blindly. Pasting can overwrite validation rules — paste values carefully or reapply.
- Do not use validation as access control. It guides honest users; it does not stop determined ones — determined ones need permissions.
Frequently asked questions
Can I make dependent dropdowns?
Yes — INDIRECT formulas build cascading lists (country picks the city list). The classic advanced validation trick.
Why is my dropdown not showing?
The cell may have validation cleared by pasting, or the list source broke. Reapply and check the source range.
Does validation work in Google Sheets?
Yes — Data, Data Validation works similarly. Concepts transfer; menus differ slightly.