Data validation
Limit a column to a list, a range, a length or a format like GSTIN, and refuse or flag values that break it.
Data validation keeps a column clean: only the values on a list, only numbers in a range, only valid GSTINs. A value that breaks the rule is refused, or kept and flagged — your choice.
Before you start: you need Can edit on Sheets.
Add a rule
- Right-click a column header, or hover it and click its ⋯.
- Click Data validation….
- In Data validation — , choose what to Allow:
| Allow | You set |
|---|---|
| Value from a list | Allowed values, one per line. The cell gets a dropdown. |
| Number between | Minimum and/or Maximum. |
| Date between | On or after and/or On or before. |
| Text length | Min characters and/or Max characters. |
| Email address, Phone number, Web address | Nothing more. Phone accepts international numbers (+91…) or 10-digit Indian mobiles. |
| Indian ID (GSTIN, PAN, IFSC, PIN code) | Format: GSTIN, PAN, IFSC or PIN code. GSTINs are checked down to the last digit. |
| Matches a pattern | A Regular expression, such as ^[A-Z]{3}-\d+$. |
- Optionally write a Message when invalid (optional). It replaces the built-in message.
- Tick Allow invalid values, just flag them to keep a breaking value instead of refusing it.
- Click Save.
To change or remove the rule, open Edit data validation… in the same menu. Remove rule removes it.
What happens when you type
- Refuse (the default) — a typed or pasted value that breaks the rule is not saved, and a message names the cell, for example "A2 must be one of the configured options".
- Flag — the value is saved, and the cell gets a red corner. Hover it to read why.
- Blank cells always pass. Formulas are never refused; a formula result that breaks the rule is flagged.
- Values already in the column when you add the rule are flagged, not changed.
- Fill, paste, find and replace, templates and Ask Axis all follow the same rule.
Dropdowns
A Value from a list rule (and a Select column) shows a chevron on the selected cell. Click it, or press Enter, to pick a value.
Good to know
- Rules apply to a whole column.
- On a sheet linked to records, values that come in from the records are flagged, never refused.
- Teammates with the workbook open see a new rule straight away.