Data Validation¶
Data Validation restricts what values can be entered into specific cells. When a user enters an invalid value, an error alert is shown. Validation rules also support dropdown lists and input messages.
Opening the Panel¶
Open via Data > Validation in the menu bar. The current selection is auto-filled as the cell range.
Validation Types¶
| Type | Description |
|---|---|
| List | Restricts input to a comma-separated set of values (e.g. Yes,No,Maybe) or a cell range reference (e.g. A1:A10). Displays a dropdown arrow in the cell. |
| Whole Number | Requires an integer value meeting the specified operator condition. |
| Decimal | Requires a numeric value (integer or decimal) meeting the condition. |
| Text Length | Validates the character length of the input. |
| Date | Requires a valid date string meeting the condition. |
| Custom Formula | Enter a formula starting with = that must evaluate to TRUE for the input to be accepted. |
Operators¶
For number, decimal, text length, and date types, choose an operator:
- Between, Not between
- Equal to, Not equal to
- Greater than, Less than
- Greater than or equal, Less than or equal
Options¶
- Allow blank: When checked, empty values pass validation regardless of the rule.
Input Message¶
Configure an optional title and message that appears when the user selects a cell with validation. This serves as a hint about what values are expected.
Error Alert¶
Configure the behavior when invalid data is entered:
| Style | Behavior |
|---|---|
| Stop | Rejects the input and shows an error message. |
| Warning | Shows a warning but allows the user to proceed. |
| Information | Shows an informational message. |
Set a custom error title and message, or leave them blank for defaults.
Dropdown Lists¶
When a cell has a List validation rule, a small dropdown arrow appears on the right side of the cell. Click the arrow to open a dropdown showing all valid options. Selecting an option fills the cell value automatically.
Managing Rules¶
Existing rules are listed at the bottom of the panel showing the range and type. Each rule has Edit and Delete buttons. Click Edit to load a rule back into the form for modification.
Serialization¶
Data validation rules are stored per sheet and preserved when saving or sharing the workbook.