Range validation is a data quality rule that checks whether a numeric or date value falls within an acceptable minimum and maximum — rejecting or flagging values that are outside the defined bounds.
It's one of the most straightforward and highest-impact validation rules you can apply. Once you define what valid ranges look like for your most important numeric and date fields, you catch a whole category of data entry errors automatically.
What Range Validation Catches
Data entry errors: A quantity of 10,000 when the actual value was 100. A year entered as 2204 instead of 2024. A discount showing 1500 when percentages should be 0–100.
Unit confusion: Price entered in cents when the system expects dollars. Weight in kilograms when pounds was expected.
Default or placeholder values: Fields auto-populated with 0, 9999, or -1 as placeholders that were never replaced with real values.
Designing Effective Range Rules
| Field | Minimum | Maximum | Notes | |---|---|---|---| | Unit price | 0 | 100,000 | Allow free (0) items | | Discount % | 0 | 100 | Cannot exceed 100 | | Quantity | 1 | 9,999 | Must be positive | | Employee hire year | 1950 | Current year | Cannot be in the future | | Customer age | 18 | 120 | Platform requires adults |
[IMAGE: Spreadsheet column of prices with two outliers highlighted — a value of -500 and a value of 5000000 — flagged by range validation]
Setting Thresholds That Actually Work
Set ranges based on business knowledge and historical data. If your average order size is $150 and the maximum ever was $8,000, a maximum of $50,000 gives meaningful protection without rejecting real orders.
For fields where you don't have intuition about the range, analyze your existing data first. Flag values beyond 3–4 standard deviations from the mean as high-confidence error candidates.
Frequently Asked Questions
Q: What is range validation in data quality? Range validation is a rule that checks whether a numeric or date value falls within defined minimum and maximum bounds. Values outside the range are flagged or rejected. It catches data entry errors, unit confusion, and placeholder values that were never replaced.
Q: What fields most benefit from range validation? Prices (must be positive and below a plausible maximum), percentages (must be 0–100), quantities (must be positive integers), dates (must be within a plausible historical range), ages (must be within a plausible human lifespan), and any numeric KPI with a known valid range.
Q: How do I set the minimum and maximum for a range validation rule? Start with business logic: what are the absolute physical limits? Then look at your historical data distribution and set the outer bounds to include 99%+ of legitimate values while excluding obvious outliers.
Q: Should range validation allow zero values? It depends on the field. Prices of zero may be valid for free products. Ages of zero may be invalid on a platform requiring adult users. Define this explicitly per field.
Q: What's the difference between range validation and completeness validation? Completeness checks whether a value exists at all. Range validation checks whether an existing value is within acceptable bounds. Both are typically needed for numeric fields.
Q: Can range validation handle date fields? Yes. Date ranges are one of the most common and valuable range validations. Hire dates must be within the company's operating history. Transaction dates must be within the fiscal year.
Q: How should I handle outliers that fail range validation but are actually correct? Build an exception process. Flag the record, route it for human review, and allow a data steward to confirm and override the validation for legitimate edge cases. Document the override with a reason.
Q: What is a soft range vs. a hard range in data validation? A hard range rejects values outside the defined bounds. A soft range flags the value for review but allows it to proceed. Hard ranges suit fields with absolute logical limits (percentage > 100 is always wrong). Soft ranges suit fields with unusual but possible edge cases.
Q: Can range validation replace outlier detection? They're complementary. Range validation enforces fixed rules you've explicitly defined. Outlier detection identifies statistically anomalous values relative to the distribution. Both are valuable; range validation is more precise where bounds are known.
Q: What tools support range validation without requiring code? Most data quality tools with rule builders support range checks as a built-in rule type. In spreadsheets, Data Validation in Excel and Google Sheets allows configuring minimum/maximum bounds on a column.
Range validation is one of the simplest and highest-value data quality rules you can implement. If you have numeric or date fields in your important datasets, defining acceptable ranges takes 10 minutes and catches errors that would otherwise reach your reports.
[INTERNAL LINK: What Is Data Validation? A Complete Guide] [INTERNAL LINK: How to Create Custom Data Validation Rules for Your Business]