Excel MIN and MAX: find the smallest and largest values
MIN returns the smallest number in a range and MAX returns the largest. =MIN(B2:B7) returns 475 and =MAX(B2:B7) returns 2,100. Subtract them to get the spread.
The examples use the same sales table as the practice box below.
| A Rep | B Sales | E Region | H Hired | |
|---|---|---|---|---|
| 2 | Dana | 1,250 | East | 3/12/2018 |
| 3 | Luis | 830 | West | 7/1/2021 |
| 4 | Priya | 2,100 | East | 11/20/2016 |
| 5 | Marcus | 475 | North | 2/15/2023 |
| 6 | Jen | 990 | West | 9/3/2019 |
| 7 | Omar | 1,600 | East | 1/27/2020 |
Syntax
=MIN(number1, [number2], ...)
=MAX(number1, [number2], ...)Examples
=MIN(B2:B7)returns 475, Marcus's sale.=MAX(B2:B7)returns 2,100, Priya's sale.=MAX(B2:B7)-MIN(B2:B7)returns 1,625, the gap between best and worst.
Second highest, third lowest
=LARGE(B2:B7,2) returns the second highest value, 1,600. =SMALL(B2:B7,3) returns the third smallest, 990.
MIN and MAX with a condition
=MAXIFS(B2:B7,E2:E7,"West") returns the best West sale, 990. MINIFS works the same way. Both need Excel 2019 or later, or Google Sheets.
Finding who had the max
MAX gives you the number, not the name. To get the name, combine it with a lookup: =INDEX(A2:A7,MATCH(MAX(B2:B7),B2:B7,0)) returns Priya. See INDEX and MATCH.
Common mistakes
- Expecting MIN to skip zeros. A 0 counts as the smallest value. Blank cells are skipped.
- Using MAX on dates stored as text, which returns 0.
Try it: Spread between best and worst
In B8, show the gap between the largest and smallest sale.
Graded live, like a real Excel test. Press New numbers to prove you didn't memorize the answer. More practice
Questions people ask
How do I find the minimum without zeros?
Use =MINIFS(B2:B7,B2:B7,">0") in Excel 2019 or later.
Do MIN and MAX work on dates?
Yes. Dates are numbers in Excel, so MIN finds the earliest date and MAX finds the latest.
Practice on a real spreadsheet
CellReady grades your formula the moment you press Enter and tells you why a wrong answer is wrong. The first 7 lessons are free, with no sign-up to start.
Related guides
How AVERAGE works in Excel, how it treats blanks and zeros, how to round the result, and a live exercise to practice.
How INDEX and MATCH work together in Excel, why analysts use it instead of VLOOKUP, the 0 that makes it exact, and a live exercise.
How to round in Excel with ROUND, ROUNDUP and ROUNDDOWN, rounding to cents or hundreds, why formatting isn't rounding, and a live exercise.