Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to highlight the lowest value in Excel and Google Sheets
Topic:Conditional formattingExcel & Google Sheets
=A1=MIN($A$1:$A$100)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 evaluate, e.g. A1:A100.
- 2Open Conditional Formatting, then New Rule, then "Use a formula to determine which cells to format" in Excel — or Format, Conditional formatting, "Custom formula is" in Google Sheets.
- 3Enter =A1=MIN($A$1:$A$100). Note the mixed reference: the tested cell (A1) is relative, the MIN range is locked with $.
- 4Choose a fill colour and apply — the single smallest value in the range is highlighted and updates live as data changes.
Tips
- To ignore blank cells (which count as 0 and would steal the highlight), use =AND(A1<>"",A1=MIN($A$1:$A$100)).
- Highlight the lowest N values instead of just one with =A1<=SMALL($A$1:$A$100,3) for the bottom three.
- Ties are highlighted together — if two cells share the minimum, both light up, which is usually what you want.
- To highlight the lowest value only among positive numbers, use MINIFS: =A1=MINIFS($A$1:$A$100,$A$1:$A$100,">0").
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
What formula highlights the lowest value?
In a Conditional Formatting custom-formula rule, use =A1=MIN($A$1:$A$100). The tested cell is relative and the MIN range is absolute with $.
How do I stop blank cells being treated as the lowest?
Blanks evaluate as 0. Exclude them with =AND(A1<>"",A1=MIN($A$1:$A$100)) so only real numbers are considered.
How do I highlight the lowest 3 values, not just one?
Use SMALL: the custom formula =A1<=SMALL($A$1:$A$100,3) highlights the three smallest values.
Is this different in Excel and Google Sheets?
The custom formula is identical. Only the menu path differs — Conditional Formatting, New Rule in Excel; Format, Conditional formatting in Google Sheets.
More on Conditional formatting
See all →How-to
How to highlight cells greater than a value
Highlight cells above a threshold with Conditional Formatting in Excel and Google Sheets — built-in greater-than rules, custom formulas, whole-row highlighting, and dynamic thresholds.
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.