How to Use Data Validation in Google Sheets (Restrict Input)

By Gerard Fernandez · Updated · 2 min read

Data validation controls what people can type into a cell. You can allow only scores from 0 to 100, only dates, only email addresses, or anything a formula can check. It's the best way to keep shared sheets clean.

Add a validation rule

  1. Select the cells to protect, for example B2:B5.
  2. Go to Data → Data validation and click Add rule.
  3. Open the Criteria list and choose a rule. Sheets suggests a Drop-down by default; change it to what you need. (In our test it even pre-filled the drop-down with the scores already in the cells.)
  4. Fill in the values, for example Is between 0 and 100.
  5. Click Done.
Data validation rules panel for B2:B10 with Criteria Is between 0 and 100, and Advanced options open showing Show help text, and If the data is invalid: Show a warning selected, Reject the input
Real test: an Is between 0 and 100 rule for a column of scores, with Advanced options open.

Available criteria

GroupExamples
Drop-downA fixed list, or items from a range. See drop-down lists.
TextText contains, Text is exactly, Text is valid email, Text is valid URL
DatesIs valid date, Date is before / after / between
NumbersGreater than, Less than, Is between, Is equal to
OtherCheckbox (Tick box in UK English), Custom formula is

Warning or reject?

Click Advanced options to choose what happens when someone enters something invalid:

  • Show a warning (default for this kind of rule): the value is kept but the cell gets a red triangle.
  • Reject the input: the value is refused and Sheets shows an error. For drop-down lists, this one was the default in our test.
Score 150 typed in B7 is kept with a red triangle; hovering shows Invalid: Input must be a number between 0 and 100
Real test: with Show a warning, typing 150 kept the value and showed "Invalid: Input must be a number between 0 and 100" on hover.

Tick Show help text for a selected cell to tell people what's allowed before they type.

Custom formula examples

Allow only…Custom formula (for a range starting at A2)
Values not already used in the column=COUNTIF($A$2:$A, A2)=1
Weekdays only=WEEKDAY(A2, 2)<6
Text up to 20 characters=LEN(A2)<=20

Frequently asked questions

Does validation check values that were already there?

Existing invalid values are flagged with a red triangle but not removed.

Can people get around it by pasting?

Pasted values are checked too, but anyone with edit access can delete the rule. To stop that, also protect the range.