The MAX function in Excel
MAX finds the largest number: the most expensive month, the best score, the latest date. Text and empty cells are ignored.
Like MIN, it also works as a guard: =MAX(value,0) never returns less than zero.
Syntax
=MAX(number1, [number2], …)
| Argument | What it means |
|---|---|
| number1 | The first number, cell or range. |
| number2, …optional | More numbers, cells or ranges. Up to 255. |
Examples
1. Largest value in a column
| A | B | |
|---|---|---|
| 1 | Month | Expenses |
| 2 | Jan | 820 |
| 3 | Feb | 760 |
| 4 | Mar | 905 |
| 5 | Apr | 690 |
| 6 | May | 845 |
| 7 | Highest | 905 |
March was the most expensive month, with 905.
2. Text in the range is skipped
| A | B | |
|---|---|---|
| 1 | Student | Score |
| 2 | Noa | 72 |
| 3 | Liam | absent |
| 4 | Maya | 91 |
| 5 | Dan | |
| 6 | Eli | 85 |
| 7 | Best | 91 |
"absent" and the empty cell don't disturb the result: Maya's 91 is the best score.
3. Never below zero
| A | B | C | |
|---|---|---|---|
| 1 | Product | Profit | Commission base |
| 2 | Desk | -120 | 0 |
A loss of -120 shouldn't create a negative commission. MAX returns whichever is larger: -120 or 0.
Common mistakes
Numbers stored as text are ignored, so the result seems too low.
Fix: Convert them to real numbers first.
Expecting the name, not the value. MAX returns the number, not who or what it belongs to.
Fix: Combine it with a lookup:
=XLOOKUP(MAX(B2:B6),B2:B6,A2:A6)returns the month with the highest value.
Frequently asked questions
How do I find the largest value that meets a condition?
Use MAXIFS, e.g. the biggest order from the North region (Excel 2019 and newer).
How do I get the top 3 values?
=LARGE(B2:B6,1), =LARGE(B2:B6,2) and =LARGE(B2:B6,3). LARGE returns the k-th largest value.
Related functions
- MINMIN returns the smallest number in a range or list of values.
- AVERAGEAVERAGE returns the arithmetic mean of numbers or a range, skipping text and empty cells.
- XLOOKUPXLOOKUP searches one column or row and returns the matching value from another, in any direction, with a built-in 'not found' message.