Docs Help

Using data validation

Data validation helps you control what data you and your collaborators enter in your spreadsheets. Here's what you can do with data validation:

• Validate numbers, dates, and text.
• Prevent users from entering text that doesn't match your criteria, or show them a comment on the cell when the data doesn't match the validation rule.
• Add text to the cells you add validation to, to help your collaborators enter the right type of data.
  

Note: Data validation doesn't support boolean operators.

Follow these steps to start using data validation:

1. Go to Tools and select Data validation.

   

2. Highlight cells to select a range you want to apply data validation to. A new window appears.

   

3. Select your validation criteria. A help tip is generated as you type.

   

   You can edit the help text manually, but if you update your validation criteria after editing the help text, the tip won't get automatically updated. Use the Reset link that appears when you edit the help text to make sure the text gets automatically generated.

4. Select the level of strictness: strict or lenient. If you select the first level, people can enter data that matches the validation criteria only. If you select the second level, people can enter invalid data, but they'll see a warning whenever the data doesn't match the criteria.

   

5. Click Save, and you're done.

To remove data validation, simply select the range of cells and click Remove validation. 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, select Tools > Data validation, and click the Remove validation button.

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.
• When you import an .xls file containing data validation into Google Spreadsheets, validation rules will be ignored.

Similarly, when exporting a Google Spreadsheets file into an .xls or .ods, validation rules won't be exported.