Catch data-entry errors by flagging any value outside an allowed band — a percentage over 100%, a negative quantity, an impossible date. One IF with an OR turns a column into a validation report.
The example
Allowed 0–100; 142 fails.
| A | B | |
|---|---|---|
| 1 | Value | Check |
| 2 | 85 | OK |
| 3 | 142 | Out of range |
The formula
The formula:
How it works
How it works:
OR(value < min, value > max)is TRUE when the value breaks either bound.- Wrap in
IFto return a flag — orMEDIAN(value, min, max)=valuefor a clamp-style test. - Add
NOT(ISNUMBER(A2))to also catch non-numeric entries. - Pair with conditional formatting to highlight the offending cells.
MEDIAN is a slick range test. MEDIAN(value, min, max) = value is TRUE only when the value sits within [min, max] — because MEDIAN returns the middle of the three, it equals the value only when the value isn’t the smallest or largest. It’s a compact alternative to the OR test and doubles as the clamp formula when you want to correct rather than flag.
Try it: interactive demo
Value with allowed min/max.
Variations
MEDIAN test
In range?:
Catch non-numbers
Also flag text:
Count out of range
How many:
Pitfalls & errors
Inclusive vs exclusive. Decide whether the boundary values pass — use </<= deliberately.
Non-numbers. Text passes a > test oddly — add an ISNUMBER guard.
Blanks. An empty cell may count as 0 — handle it explicitly if needed.
Practice workbook
Frequently asked questions
How do I flag out-of-range values in Excel?
How do I also catch text entries?
How do I count how many values are out of range?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class