Use data validation

With data validation, you can:

  • Restrict the possible values for cells that contain numbers, dates, and text
  • Show people a warning if they enter invalid data — or prevent them from entering invalid data in the first place
  • Allow people to select a cell's contents from a list

Follow these steps to start using data validation:

  1. Click Data > Validation. Or, right-click a range of cells and select Data validation.
  2. Select your validation criteria.
  3. Check "Show help" if you'd like users to see help text when they hover over the cell. Sheets automatically generates the help text that's shown. To reset the help text to a default value using the criteria you've selected, click the "Reset" link.
  4. If you want to allow collaborators to be able to enter data that does not match the validation rules, select "Show warning" next to the "On invalid data" option. If you want to be more strict and allow only data that matches the validation rules, then select "Reject input."
  5. If you're using list-based validation and you'd like to let people select a cell's contents from a drop-down menu, check the "Display in-cell button to show list" option. An arrow button will appear in the cell, which people can click to reveal the drop-down menu.
  6. Click Save.

To remove data validation, select a range of cells, click Data > Validation Remove validation. To remove validation from the entire worksheet, select the whole sheet by clicking the upper left corner of the sheet. Then, click Data > Remove validation.

A few things to keep in mind:

  • If data validation is applied to cells containing data, rules won't be applied until the data is modified.
  • Collaborators can change validation rules.
  • Cells with data validation are skipped in List view and spreadsheets populated by a form.

Mary is a Docs & Drive expert and author of this help page. Leave her feedback below about the page.

Was this article helpful?