The MAX and MIN functions in Excel
MAX returns the largest number in a range. MIN returns the smallest.
What MAX / MIN does
They scan every number in the range for you, which is handy for finding the best sale, the hottest day or the cheapest quote. Empty cells and text are ignored.
Syntax
=MAX(number1, [number2], ...) =MIN(number1, [number2], ...)
- number1
- The first number, cell or range to check.
- number2, ...
- Optional. More numbers, cells or ranges.
Worked example
| A | B | |
|---|---|---|
| 1 | Day | High °C |
| 2 | Mon | 14 |
| 3 | Tue | 19 |
| 4 | Wed | 17 |
| 5 | Thu | 22 |
| 6 | Fri | 16 |
| 7 | Hottest | 22 |
| 8 | Coolest | 14 |
B7 =MAX(B2:B6)
B8 =MIN(B2:B6)
MAX returns 22, the highest high of the week. MIN returns 14, the lowest.
Now you try
A fresh exercise every time. Press Another exercise as often as you like.
Where you will use it
Sales & Ops
Find the best-selling week or the busiest day.
Finance
Spot the largest and smallest invoice.
Data cleanup
Check the range of a column for impossible values.
Common mistakes
- Expecting MAX to return a name. It returns the number only. To find who or what had the highest value, combine it with a lookup such as INDEX and MATCH.
- Forgetting that text is ignored. A cell reading n/a is not treated as a low value, it is skipped.
- Sorting the data instead. A sorted list goes stale as soon as the data changes, while a formula updates itself.
The MAX / MIN course
- 1Find the highest or lowestFree practice belowPractise
- 2Second-highest with LARGEComing soon
- 3Max by categoryComing soon
- 4Best and worst of the monthComing soon
- 5Track records over timeComing soon
Common questions
How do I find the second-largest value?
Use LARGE with a position, for example LARGE(B2:B6, 2). SMALL works the same way for the second-smallest.
Can MAX compare more than one range?
Yes. Separate the ranges with commas, for example MAX(B2:B6, D2:D6).