How to Use Data Validation in Google Sheets: Dropdowns, Limits and Custom Rules
Data validation controls what can be entered in a cell. You can offer a dropdown, limit numbers and dates, or block entries that break a rule. It keeps your data clean so formulas like SUMIF and COUNTIF do not break on typos such as "Done" versus "done ".
How to add a rule
- Select the cells, for example C2:C100.
- Click Data > Data validation.
- Click Add rule.
- Choose a Criteria type (see below).
- Open Advanced options and choose what happens on bad data: Show a warning (the entry is allowed but flagged) or Reject the input (the entry is blocked).
- Optionally tick Show validation help text and write a short message.
- Click Done.
Example 1: A dropdown list typed into the rule
Criteria: Dropdown, then type the options: Open, In Progress, Closed.
Each cell in the range now shows an arrow with those three choices. Anything else is flagged or rejected, depending on your setting.
Example 2: A dropdown from a range
Put your options in a list, for example Lists!A2:A5 containing Pending, In Progress, Completed, On Hold.
Criteria: Dropdown (from a range), then enter Lists!A2:A5.
If you later change the cells in that list, the dropdown updates. Keep the list free of blank cells, because blanks show up as empty options.
Example 3: Limit a number
Criteria: Number > between > 0 and 10000.
Entering 12000 or -5 is blocked (with Reject the input). Text such as abc is also blocked, because the criteria require a number.
Example 4: Limit a date
Criteria: Date > is on or after > pick a date, or choose today.
The date rule accepts real dates only. A cell holding the text "Oct 12" typed as text may be flagged, so use a normal date format.
Custom formula rules
Choose Custom formula is and enter a formula that returns TRUE for valid entries. Write it for the first cell of your selected range, using normal relative references. Google Sheets adjusts it for the other cells.
| Goal | Range | Formula |
|---|---|---|
| No duplicates in a column | A2:A100 | =COUNTIF($A$2:$A$100, A2)=1 |
| End date must not be before start date | B2:B100 | =B2>=A2 |
| Only email addresses ending in @example.com | A2:A100 | =REGEXMATCH(A2, "^[A-Za-z0-9._%+-]+@example\.com$") |
| Whole numbers from 1 to 5 only | D2:D100 | =AND(ISNUMBER(D2), D2=INT(D2), D2>=1, D2<=5) |
| Text exactly 5 characters long | E2:E100 | =LEN(E2)=5 |
Example: block duplicate IDs
| A (ID) |
|---|
| 101 |
| 102 |
| 103 |
Select A2:A100, choose Custom formula is, enter =COUNTIF($A$2:$A$100, A2)=1, and choose Reject the input.
Typing 102 in A5 now fails, because COUNTIF finds two 102 values and returns 2, which is not equal to 1. Typing 104 works.
Copy or remove a rule
- Copy a rule to other cells: copy a validated cell, select the target cells, then use Edit > Paste special > Data validation only.
- Remove a rule: select the cells, open Data > Data validation, and click Remove rule (or Remove all in the side panel).
Common problems
| Problem | Cause | Fix |
|---|---|---|
| Pasted values ignore the rule | Pasting a normal cell also pastes its (empty) validation, overwriting yours | Paste with Edit > Paste special > Values only |
| A dropdown shows blank options | The source range includes empty cells | Shrink the range to the filled cells only |
| Custom formula blocks everything | The formula refers to the wrong first cell | Write it for the top-left cell of the selected range |
| Warning triangle but the entry is accepted | The rule is set to Show a warning | Switch to Reject the input in Advanced options |
| Old bad values stay in the sheet | Validation checks new entries only, not existing data | Fix existing cells manually or filter for them with a formula |
| Dropdown missing in exported CSV | CSV stores values only | Share the sheet itself, or download as .xlsx |
FAQ
How do I create a dropdown list in Google Sheets?
Select the cells, click Data > Data validation > Add rule, choose Dropdown and type your options (or choose Dropdown (from a range)), then click Done.
How do I stop duplicate entries in a column?
Use Custom formula is with =COUNTIF($A$2:$A$100, A2)=1 on the range A2:A100, and set the rule to Reject the input.
What is the difference between Show a warning and Reject the input?
With a warning, the cell accepts the value but marks it as invalid. With Reject the input, Google Sheets blocks the entry and shows your help text.
How do I remove data validation from a range?
Select the range, open Data > Data validation, and remove the rule. The values in the cells stay as they are.
Can data validation limit a number to a range?
Yes. Choose Number > between and enter the minimum and maximum values, then set the rule to Reject the input.