How to use data validation in Google Sheets
Learn how to set up data validation in Google Sheets to restrict entries, build dropdowns, add checkboxes, and enforce custom rules.
Quick answer
Select the cells you want to control, open the Data menu and choose Data validation, then pick a criteria type such as a dropdown list, number range, checkbox, or date restriction. Set whether invalid entries are rejected or flagged with a warning, add a custom message, and click Done to save the rule.
Steps at a glance
- Select the cell range you want to validate.
- Open Data menu and click Data validation.
- Choose a criteria type: list, range, date, or custom formula.
- Set rejection or warning behavior for invalid entries.
- Add a custom error message to guide users.
- Click Done to apply the rule.
Summary
Data validation in Google Sheets lets you define exactly what can be entered into a cell — a fixed list of options, a number range, a date window, or a custom formula rule. It's a free built-in feature that keeps shared spreadsheets clean and prevents typos or invalid entries from breaking formulas and reports.
Step-by-step guide
Step 1
Select the cells you want to control
Click and drag to highlight the range where you want to enforce a rule — a single column, a specific row, or an entire table. For example, in a task tracker, you might select the 'Status' column across all 10 rows so every entry stays consistent.
Step 2
Open the Data validation menu
Click Data in the top menu, then select Data validation from the dropdown. A panel opens on the right side showing the range you selected and letting you build a rule from scratch or edit an existing one.
Step 3
Choose a criteria type
Click the Criteria dropdown and pick the rule type that fits your data: 'Dropdown' or 'Dropdown from a range' for a fixed list of choices, 'Checkbox' for true/false values, 'Number' for value ranges, 'Date' for date restrictions, or 'Custom formula is' for advanced logic. If you only need a simple choice list, the same panel handles what's covered in the guide to add a dropdown list.
Step 4
Set what happens with invalid entries
Under 'If the data is invalid,' choose 'Reject input' to block anything that doesn't match your rule, or 'Show a warning' to flag it with a small alert triangle while still allowing the entry. Rejecting input works best for strict fields like status or category; warnings work well when you want flexibility but still want visibility into outliers.
Step 5
Add a custom validation message
Check the box for 'Show validation help text' and type a short message explaining what's allowed, such as 'Enter a date between today and the project deadline.' This message appears when someone clicks the cell, reducing confusion and repeated errors.
Step 6
Save the rule and test it
Click Done to apply the validation rule to your selected range. Test it by typing a value that should fail — you should see the rejection message or warning triangle appear immediately, confirming the rule is active.
Step 7
Copy the rule to other cells if needed
Select a validated cell, copy it with Ctrl+C (or Cmd+C on Mac), then select the new range and use Paste special > Paste data validation only from the right-click menu. This applies the same rule without overwriting existing formatting or values, which is useful once you've also set up conditional formatting to visually flag those same cells.
Why this matters
You're sharing a tracker or budget sheet with a team, and someone keeps typing inconsistent status labels or entering numbers outside your expected range. Setting up data validation stops those mistakes at the source, so your formulas, pivot tables, and reports stay accurate without constant manual cleanup.
Frequently asked questions
Is data validation available on the free version of Google Sheets?
Yes. Data validation is a core built-in feature available to every Google account with no paid upgrade required, whether you're using a personal Gmail account or a work account.
Can I apply data validation to multiple cells at once?
Yes. Select the full range before opening Data validation, or copy a cell with an existing rule and use Paste special > Paste data validation only to apply it to new cells.
What's the difference between rejecting input and showing a warning?
Rejecting input blocks any entry that doesn't match your rule, forcing users to correct it before moving on. Showing a warning allows the entry but flags it with a small alert triangle, which is useful when you want visibility without strict enforcement.
Can I use a formula to create a custom validation rule?
Yes. Choose 'Custom formula is' in the Criteria dropdown and enter a formula that returns TRUE or FALSE, such as one that checks a cell against another column or enforces a specific format.
Create interactive demos like this one — free, no coding required.
Start for free →