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 |
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 LARGEMembers lesson
- 3Gap between highest and lowestMembers lesson
- 4Halfway between the extremesMembers lesson
- 5Gap between first and secondMembers lesson
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).
Related functions
Unlock every lesson
Every function has a free lesson. Members get all the lessons, fresh exercises every time and the weekly leaderboard. £7 a month or £49 a year, and you can cancel at any time.