Use data validation

With data validation, you can do the following:

  • Restrict what values are possible 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 on the Data menu and select Validation. Alternatively, 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. Clicking on the "Reset" link will reset the help text to a default value using the criteria you've selected.
  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 the Data menu, select Validation, then click the Remove validation button. If you'd like to remove validation from the entire worksheet, select the whole sheet by clicking the upper left corner of the sheet. Then, click the Data menu and select 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?