Loading...
Loading...
Guides
Ensure the quality, consistency, and usability of data entered in Google Sheets through appropriate validation rules.
Good data structure starts with controlled entry. Validation is essential to:
Define structure and data types before entry.
Use the most restrictive and appropriate rule.
Apply the same rules for similar data.
Use the "Show help text" to guide users.
Ideal use: Very short, fixed lists (e.g., "Yes", "No", "N/A").
Implementation: Enter items directly into the rule.
Difficult to maintain if the list changes. Use sparingly.
Ideal use: Categories, statuses, types, teams, regions... Predefined and potentially evolving lists.
Implementation: Create the list in a dedicated column/sheet (e.g., Lists!A:A) and reference this range.
Easy to update, ensures uniformity, perfect for analysis.
Ideal use: Text fields with specific formats (emails, URLs) or controlled content.
contains / does not contain / is exactly: Presence/absence of keywords.is valid email / is valid URL: Structure check.Useful for emails, URLs, and specific formats.
Ideal use: All date columns (order, due date, birth...).
is a valid date: Basic validation.before / after / between: Logical constraints (end date > start date).Crucial for duration calculations, sorting, and time analysis. Ensures unique format.
Ideal use: Quantities, prices, percentages, scores, numerical IDs.
between / not between: Defined ranges (score 0-20).greater than / greater than or equal to: Minimums (quantity > 0).less than / less than or equal to: Maximums.equal to / not equal to: Specific values.Prevents text entry, ensures respect for logical limits.
Ideal use: Binary data (True/False, Yes/No, Done/Not done).
Implementation: Apply the "Checkbox" rule. Customize values if needed (default: TRUE/FALSE).
Quick entry, unambiguous, easy to analyze.
Ideal use: Complex validations not covered by other rules.
REGEXMATCH(A1,"^[A-Z]{2}[0-9]{4}$") (e.g., Product ID)=COUNTIF($A:$A,$A1)=1 (applied to Column A)=B1{'>='}A1Implementation: The formula must return TRUE if valid, FALSE otherwise. Pay attention to relative/absolute references ($).
Very powerful and flexible.
More complex to set up and debug.
Allows non-compliant input but flags it (red triangle). Useful for transitions or noted exceptions.
Less restrictive but requires vigilance. Avoid if possible.
Prevents entry of non-compliant data. Strictest option to ensure compliance from the start.