Established onExcel 365 and 2016 and later: three styles, circle invalid dataGoogle Sheets, web: rules, no circling featureBoth: pasting bypasses the rule
Refusing a bad value at the moment somebody types it is cheaper than finding it afterwards. Both products offer this, and both offer less of it than people assume.
What it can require
A value from a list, which produces a dropdown. A number within a range. A date within a range. A text length. And an arbitrary condition expressed as a formula, which is the general case and covers everything the others do not.
The list is the most used, and drawing it from a named table rather than typing the options means the list of permitted values has one home, in keeping with the fourth part of this site.
The limitation that matters
Validation applies to typing. Pasting into a validated cell is not checked, and in Excel pasting also replaces the cell's validation with whatever the source cell had, which is usually none.
So one paste can both introduce an invalid value and remove the rule that would have caught it, silently, in one action. Given how much data arrives in spreadsheets by pasting, this is close to saying that validation does not apply to the data that actually turns up.
What to do about that
Excel can circle every value in a validated range that violates its rule, which finds exactly the ones that arrived by other means. It is a menu item and it is the retrospective half of this feature.
Better, and available in both products: a check cell counting the values in the column that are not in the permitted set. That is the guarantee, because it examines what is there rather than what was typed.
The three styles
Excel offers stop, which refuses; warning, which asks; and information, which mentions. Only the first prevents anything, and the other two exist because a refusal is sometimes wrong.
Choosing warning because refusal feels harsh produces a rule that records a preference and enforces nothing, which is worth deciding deliberately.
What validation is not
Protection. It can be removed by anybody, it does not apply to values already present when it is added, and it is not enforced when the file is edited by a program. It is an aid at the keyboard, and treating it as a constraint on the data is the error this entry exists to prevent.
Where to validate
On the input region described in the fourth part, which is where typing happens. Validating a calculated column is meaningless, and validating an imported table is validating something that arrived by paste and was therefore not checked.
For imported data the equivalent step is in the import itself: declaring types, which the third part covers, and the count check above.
The dropdown that goes stale
A list typed into the validation dialogue is a constant inside a setting, with every problem the entry on constants describes and one more: it is not visible on any sheet.
Point it at a table instead. The permitted values are then a column somebody can see, extend and document, and every validated cell follows along.
What to validate first
The fields other things join on. A category that must match a lookup table, an identifier that must exist elsewhere, a code from a fixed set. Errors in those propagate into every figure downstream, and errors in a free-text note do not.
The message nobody writes
Both products let a rule carry an explanatory message shown when it refuses. Leaving it empty produces a refusal with no reason, which is how people learn to work around validation rather than with it.
One sentence saying what is expected and why converts an obstacle into an instruction.
What we cannot verify
The rule types, the three styles, the circling feature and the behaviour of pasting over a validated cell are documented by the vendor and were confirmed on a current build. Google Sheets validation behaviour on paste was established by testing; its documentation describes the rules rather than the interaction with pasting.
Validation as documentation
A rule saying a column takes one of four values is a statement about what that column means, sitting on the column itself, readable by anybody who clicks a cell.
That is documentation which cannot drift from the data, in the sense of the entry on writing things down, and it is a better reason to add validation than the enforcement, which this entry has just spent several paragraphs qualifying.
Validating dates
A rule requiring a date within a plausible range catches the parsing failure described in the third part at the moment it enters, which is the one place it is cheap to fix. A date in 1905 fails; a date in the wrong order within the same month does not, and nothing catches that except the written form.
In short
- Validation can require a list, a range, a length, or an arbitrary condition.
- It applies to typing, and pasting both bypasses it and can remove the rule.
- Circling invalid data finds the values that arrived by other means.
- A cell counting values outside the permitted set is the actual guarantee.
- Only the refusing style prevents anything; the other two record a preference.
- Point the list at a table so the permitted values are visible, extendable and documented.