Skip to content
Book a demo Try the demo MacWindows

Data validation and dropdowns

Limit what can go in a range of cells: offer a dropdown list of choices, or allow only numbers in a range, and flag or reject entries that do not fit.

All plansUpdated

Data validation keeps a column tidy. Give a Status column a dropdown of Open, In progress and Done, so everyone picks from the same three words. Or allow only numbers between 1 and 5 in a Rating column. Entries that do not fit are flagged, and can be refused as they are typed.

Before you start

  • Plans. Data validation is on every plan.
  • Select the cells first. A rule applies to the range you have selected when you add it, such as C2:C100.

Add a dropdown list

  1. Select the cells, such as the Status column.
  2. Click Data validation for the selection in the toolbar, or choose Data > Data validation. The Data validation window opens and shows the range it applies to.
  3. Leave the first list on List of items (dropdown).
  4. Type the choices, separated by commas, for example Open, In progress, Done.
  5. Leave Reject invalid entries ticked to refuse anything else, or untick it to allow other values but flag them.
  6. Click Add rule.

Each cell in the range now has a small arrow at its bottom right corner. Click it and choose a value from the list.

Allow only numbers

  1. Select the cells.
  2. Open Data validation as above.
  3. Choose Number in the first list.
  4. Choose a condition: Greater than, Less than, Greater or equal, Less or equal, Equal to or Between.
  5. Type the value. For Between, type the lower value and then the upper one in and.
  6. Choose whether to Reject invalid entries, then click Add rule.

What happens to entries that do not fit

  • A small red corner at the top right of a cell marks a value that does not fit its rule. Rest the pointer on it to see Value does not match this cell's data validation.
  • With Reject invalid entries ticked, a value typed into the cell that does not fit is refused. The cell keeps what it had, and a line above the grid says the value is not allowed in that cell.
  • Empty cells are always allowed. Validation controls what goes in a cell, not whether it is filled.

Values that were already in the cells are never changed by a rule; they are only flagged.

See and remove rules

Open Data validation again. Under the form, each rule on the sheet is listed with its range and what it allows, marked (strict) if it rejects invalid entries. Click the bin (Remove rule) beside a rule to delete it.

If two rules cover the same cell, the one added last applies.

Common questions

The dropdown arrow does not appear. The arrow appears on cells covered by a List of items (dropdown) rule, and is hidden while you are typing in the cell. Check the rule's range in the Data validation window.

Can I change a rule? Remove it and add a new one with the changes.

Can I colour cells by their value instead? Yes. Use conditional formatting. See Conditional formatting.

Still need help? Contact support at [email protected].