MIN and MAX in Excel: Find the Best and Worst Values
Use MIN and MAX in Excel to find the smallest and largest values, ignore zeros with MINIFS, find which row they're in, and cap values with MIN/MAX tricks.
By Ayoub Farid · · 2 min read
MIN returns the smallest number in a range and MAX the largest. They answer questions like "what was our best month?", "what's the cheapest supplier?" and "what's the lowest score in the class?".
The syntax
=MIN(number1, [number2], ...)
=MAX(number1, [number2], ...)Example
| A | B | |
|---|---|---|
| 1 | Month | Sales |
| 2 | Jan | 4,200 |
| 3 | Feb | 3,900 |
| 4 | Mar | 4,800 |
| 5 | Apr | 5,100 |
=MAX(B2:B5) → 5,100
=MIN(B2:B5) → 3,900The range between them: =MAX(B2:B5)-MIN(B2:B5) → 1,200.
Which month was it?
MAX gives the value, not the month. Pair it with a lookup:
=XLOOKUP(MAX(B2:B5),B2:B5,A2:A5)Result: Apr. In older Excel: =INDEX(A2:A5,MATCH(MAX(B2:B5),B2:B5,0)). See INDEX MATCH. If two months tie, you get the first one.
Ignore zeros or blanks
Blank cells are ignored, but zeros count, so MIN often returns 0 for unfinished data. Use MINIFS (Excel 2019+, Microsoft 365, Google Sheets):
=MINIFS(B2:B13,B2:B13,">0")MIN and MAX with conditions
Highest Coffee sale:
=MAXIFS(B2:B50,D2:D50,"Coffee")Cap a value
A neat trick: MIN and MAX can set limits.
- Bonus capped at 500:
=MIN(B2*10%,500) - Never below 0:
=MAX(B2-C2,0)
Second highest or lowest
=LARGE(B2:B13,2)
=SMALL(B2:B13,2)For full rankings, see RANK.
Common mistakes
- Numbers stored as text are ignored. If MAX returns 0, check the data.
- Including the total row makes MAX return the total. Keep ranges tight.
- Dates are numbers in Excel, so MAX of a date column returns the latest date. That's useful:
=MAX(C2:C50)is "most recent order".
Practise it
Cellio's "MIN & MAX" lesson has you find the best and worst month on a real sales sheet, then mixes them with other formulas in the next lessons. It's part of the Basics unit, live in the app today.
Frequently asked questions
How do I find the highest value in Excel?
Use =MAX(range), e.g. =MAX(B2:B13).
How do I find the lowest value excluding zero?
Use =MINIFS(B2:B13,B2:B13,">0").
How do I find which row has the max value?
Use XLOOKUP or INDEX/MATCH with MAX inside, e.g. =XLOOKUP(MAX(B2:B13),B2:B13,A2:A13).
Do MIN and MAX work with dates?
Yes. MIN returns the earliest date and MAX the latest.