Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to highlight cells greater than a value in Excel and Google Sheets
Topic:Conditional formattingExcel & Google Sheets
=A2>100✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Value |
| 80 |
| 150 |
| 100 |
| 210 |
| 55 |
=A2>100→FALSE
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Select the range you want to format, e.g. A2:A100.
- 2Excel: Home → Conditional Formatting → Highlight Cells Rules → Greater Than. Google Sheets: Format → Conditional formatting → "Format cells if…" → Greater than.
- 3Type the threshold (e.g. 100), pick a fill colour, and confirm. Every cell above the threshold is highlighted, live.
- 4For a dynamic threshold, keep the cutoff in a cell (say E1) and use a custom formula rule instead: =A2>$E$1 — change E1 and the highlighting updates everywhere at once.
- 5For greater-than-or-equal, use the custom formula =A2>=100 — the built-in rule is strictly greater-than.
- 6To highlight the entire row when one column exceeds the threshold, select the full-width range (A2:D100) and use =$B2>100 — the $ locks the check to column B while the rule paints all columns.
Tips
- Rule order matters when rules overlap: in Excel manage priority under Conditional Formatting → Manage Rules; in Sheets drag rules up or down — the first matching rule usually wins the fill.
- Two-colour scheme: add one rule for >100 (red) and a second for <50 (green); values in between stay unformatted.
- Highlight values greater than the column average with =A2>AVERAGE($A$2:$A$100) as a custom formula.
- For dates "greater than" means "after": =A2>TODAY() highlights future dates.
- If you want the top N values rather than a fixed threshold, use =A2>=LARGE($A$2:$A$100,3) to light up the top three.
- Blank cells never satisfy A2>100, so they are left alone automatically — no special handling needed.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
How do I highlight the whole row, not just the one cell?
Select the entire data range (all columns), then use a custom formula with the column locked: =$B2>100. Each row is painted when its column-B value exceeds the threshold.
Can the threshold come from a cell instead of being typed into the rule?
Yes — use a custom formula rule like =A2>$E$1 with the threshold in E1. In Google Sheets you can also write =A2>INDIRECT("E1") if the rule complains about direct references.
How do I do greater than or equal to?
The built-in rule is strictly greater-than, so switch to a custom formula: =A2>=100.
Can I highlight cells greater than a value only when another condition holds?
Combine tests with AND in a custom formula: =AND(A2>100,$C2="Active") highlights values over 100 only on rows whose status column reads Active.
More on Conditional formatting
See all →How-to
How to highlight the lowest value
Highlight the smallest number in a range with Conditional Formatting and a MIN-based custom formula in Excel and Google Sheets — including how to ignore blanks and highlight the lowest N.
How-toHow to highlight duplicate values
Highlight duplicate cells automatically with Conditional Formatting using a COUNTIF rule — no formulas to copy manually.
How-toHow to highlight the highest value in a range
Color the maximum value in a range automatically using a Conditional Formatting rule based on the MAX function.
How-toHow to highlight blank cells
Use conditional formatting with ISBLANK to automatically highlight empty cells in your spreadsheet for easy visual identification.
How-toHow to highlight cells that contain specific text
Use conditional formatting with SEARCH or ISNUMBER(SEARCH()) to highlight any cell containing a specific word or phrase automatically.
How-toHow to shade every other row
Use conditional formatting with MOD and ROW to automatically apply alternating row colors for a banded, readable table layout.