Skip to content
Tutorials Center
My learning

Topics

Lesson 17 of 29 ยท Working with Data

Data Validation and Dropdowns

Control what people can type in Google Sheets: dropdown lists, checkboxes, number and date limits, custom formula rules and how to protect input.

Spreadsheets fill up with typos: "Pending", "pending", "Pendng". Every spelling becomes a different group when you sort, filter or count. Data validation prevents that by limiting what a cell accepts, and dropdown lists and checkboxes make correct input a single click.

In this lesson you will create dropdowns from a list and from a range, add checkboxes, restrict numbers and dates, write custom formula rules, and learn the limits of validation. The menu steps are real Google Sheets; the JavaScript blocks are stand-ins that show how a rule decides whether an entry is accepted.

Stop bad input early

Dropdown Pick from a fixed list.
Checkbox TRUE or FALSE in one click.
Number or date Keep values in a range.
Custom formula Any rule you can write.
1 / 2

Adding a rule

Select the cells and open Data โ†’ Data validation โ†’ Add rule. In the panel you choose:

  1. Apply to range: the cells the rule covers.
  2. Criteria: what counts as valid (see below).
  3. Advanced options: Show validation help text, and if the data is invalid, either Show a warning or Reject the input.

Cells with a warning show a small red triangle in the corner; hovering shows your help text. A rejected entry simply refuses to stay, with a message.

Google Sheets cannot run on this page, so the steps are described rather than interactive. The stand-ins below mimic the validation logic with ordinary JavaScript functions, which is also a handy way to think about each rule.

Choose Dropdown (from a list) or Dropdown from a range.

  • Dropdown with items typed in the panel, such as Open, In progress, Done. Each item can have a color, which makes status columns easy to scan.
  • Dropdown from a range, such as Lists!A2:A20, takes its items from cells. When you add a new item to that range the dropdown updates by itself, so keep your lists on a separate tab where you can edit them. See what is Google Sheets for how tabs work.

Once a dropdown is set, typing an item that is not on the list is flagged or refused, depending on the option you chose. Treat the listed spelling as the only correct one, and use it in later COUNTIF and IF tests.

JS Stand-in: a dropdown accepts only listed items

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Checkboxes

Insert โ†’ Checkbox (or Data validation โ†’ Checkbox) turns cells into checkboxes whose value is TRUE when ticked and FALSE when not. Checkboxes make to-do lists and attendance sheets: the value can feed formulas directly.

TEXT
=COUNTIF(B2:B20, TRUE)          how many are ticked
=IF(B2, "Done", "To do")        text based on one checkbox
=ARRAYFORMULA(IF(B2:B20, C2:C20, 0))     use a value only for ticked rows

In the validation panel you can also choose custom values, for example Yes when checked and No when unchecked. A conditional formatting rule such as =$B2=TRUE can strike through the whole row of a finished task; see Conditional Formatting.

Numbers, text and dates

The criteria list also offers ready-made tests:

Criteria Example use
Number: between, greater than, not equal... Scores from 0 to 100
Text: contains, is valid email, is valid URL A contact column
Date: is valid date, before, after, between A deadline that is not in the past
Checkbox / Dropdown Status and flags

For a number rule, a cell holding text like "abc" is invalid even though a text cell is not a number. An empty cell is always allowed; to force an entry you need a custom formula.

JS Stand-in: number, email and date rules

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Custom formula rules

Custom formula is accepts any formula that returns TRUE for valid input. Write it for the first cell of the range, just like conditional formatting, and lock parts with $ when needed (see Relative, Absolute and Mixed References).

TEXT
=COUNTIF($A$2:$A$100, A2) = 1           no duplicates in the column
=AND(ISNUMBER(B2), B2 = INT(B2))        whole numbers only
=LEN(C2) <= 40                          at most 40 characters
=B2 <= TODAY()                          no dates in the future
=OR(D2 = "", D2 >= C2)                  end date not before start date, blank allowed

The unique rule uses a fully locked range for the list and a relative A2 for the cell being checked. That pattern is the same as the duplicate highlighter in the previous lesson.

JS Stand-in: reject duplicates and bad ranges

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Dependent dropdowns

A second dropdown can depend on the first, for example City choices that change with the chosen Country. The usual approach is a Dropdown from a range whose range comes from a formula such as =FILTER(Lists!B:B, Lists!A:A = $A2), or a custom helper range on another sheet. Dropdowns from formulas are a little advanced: test them with a handful of values first.

Limits of validation

Validation is a guide, not a lock:

  • Pasting overrides it. Pasting cells from elsewhere usually replaces the validation rule, and the pasted values are not checked. Use Paste special โ†’ Values only to keep rules.
  • Existing data is not rechecked when you add a rule, but invalid cells are flagged.
  • Anyone with edit access can remove rules. To stop people changing certain cells, use Data โ†’ Protect sheets and ranges, which is covered with sharing and permissions.
  • Formulas can still return anything; validation only checks what a person types.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the main benefit of a dropdown from a range?

    The list reads its items from cells, so adding or renaming an item in that range changes the dropdown.
  2. 2. What values does a default checkbox hold?

    Checkboxes store TRUE or FALSE unless you choose custom values.
  3. 3. Which custom formula rule, applied to A2:A100, rejects duplicates?

    The locked range lists all values and A2 is the cell being checked; exactly one match means it is unique.
  4. 4. Why can validation fail to stop a wrong value?

    Pasting from elsewhere usually overwrites the rule and is not checked. Protect ranges when you need a firm lock.

Summary

  • Data โ†’ Data validation limits what a cell accepts; choose a warning or rejection and add help text.
  • Dropdowns (typed or from a range) keep spellings consistent; checkboxes store TRUE/FALSE.
  • Number, text and date criteria cover common limits; custom formulas handle the rest, written for the first cell of the range.
  • Pasting can bypass rules; use protected ranges for firm control.

Next: SUMIF, COUNTIF, AVERAGEIF and Friends