Highlight everything above a threshold you control from a cell — change the cell and the highlights update. A formula rule comparing each value to a reference cell makes the threshold interactive.
The example
Values above the threshold in E1 (100) are flagged.
| A | B | |
|---|---|---|
| 1 | Value | Flag |
| 2 | 120 | above |
| 3 | 80 | — |
The formula
Compare each value to a cell:
How it works
An absolute reference makes the threshold live:
- Put the threshold in a cell (E1) so it’s easy to change.
- Select the data and add the rule
=A1 > $E$1— the reference is locked with$. - Every value above the threshold gets the format; change E1 and the whole range re-evaluates.
- Use
>=,<, or<>for other comparisons, or two rules for an above/below split.
Between two thresholds? =AND(A1>=$E$1, A1<=$E$2) highlights a band. A reference-cell threshold is the dashboard-friendly way to let users explore data without editing rules.
Try it: interactive demo
Set a threshold; values above it highlight.
Variations
Between two cells
A band:
Below a cell
Flag the low ones:
Above average cell
Reference a computed cell:
Pitfalls & errors
Lock the reference. Use $E$1 so every cell compares to the same threshold; a relative ref drifts.
Active-cell relative ref. The A1 must match the top-left of your selection.
Blank threshold = everything. An empty E1 counts as 0, so every positive value highlights. Guard if E1 may be blank.
Practice workbook
Frequently asked questions
How do I highlight cells above a value in a cell?
How do I highlight a band between two values?
Why does locking the reference matter?
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