Input and Validation Standard¶
Data validation on input cells¶
Input cells should use Excel data validation (dropdown lists, numeric range limits, date range limits, custom formula validation) wherever a value has a known valid domain. Validation should prevent, or at minimum clearly flag, values that would break downstream calculations (e.g., a negative age, a percentage over 100% where that's not meaningful, a date before a required minimum).
Clear error messaging¶
Where validation catches an invalid entry, the customer should get a clear, specific
message about what's wrong and what's expected — not a generic Excel error. Use
data validation's input/error message fields, and where a calculation could still
produce an error despite validation (e.g., a division by a value that is technically
valid but produces an edge case), surface a readable message via formula (e.g.,
IFERROR with an explanatory string) rather than letting #DIV/0! or #N/A appear
unexplained.
Sensible defaults¶
Where a reasonable default exists, input cells should be pre-populated with it (e.g., today's date, a common assumption rate) rather than left blank, provided the default is clearly editable and clearly marked as a default/example rather than the customer's real data. Defaults should never be mistaken for the customer's actual input in a way that could mislead the resulting calculation.
Required vs. optional fields marked¶
Every input field should be visibly marked as required or optional. Products should use a consistent visual or textual marker (e.g., an asterisk, a "(optional)" label) so a customer can tell at a glance which fields they must complete for the tool to function versus which fields refine the result.
Interaction with calculations¶
Validation is a first line of defense, not a substitute for the calculation layer handling unexpected values gracefully. See calculation-and-formula-standard.md for how calculations should handle nulls, zeros, and errors even when validation is in place.