Function guides · Statistical
MAX function
Returns the largest number in a set. MIN returns the smallest.
Syntax
=MAX(number1, [number2], ...)
| Argument | What it is |
|---|---|
number1 | A number, cell reference, or range. |
number2, ... | Optional. Up to 255 more. |
What MAX does
MAX returns the largest number in the values you give it. MIN, covered on its own page, returns the smallest.
Because dates are numbers, MAX also finds the latest date in a column, and MIN the earliest. That is the quickest way to answer “when was the last order”.
Text, blanks and logical values inside a range are skipped. An empty range returns 0.
When a test asks for the highest value, check whether it also wants context. “The highest sale” is MAX. “Who made the highest sale” is a lookup built on MAX. “The highest sale in the West” needs a condition. Reading the noun after the word “highest” tells you which of the three it is.
MAX can take several ranges at once, as in =MAX(B2:B10,D2:D10), which is handy when the data sits in separate blocks.
MAX examples
1. Highest daily sales
| A | B | |
|---|---|---|
| 1 | Day | Sales |
| 2 | Mon | 860 |
| 3 | Tue | 1240 |
| 4 | Wed | 910 |
| 5 | Thu | 1105 |
| 6 | Fri | 990 |
| 7 | 1240 |
MAX scans the range and returns the single largest number, 1,240 here. It does not tell you which day. For that, pair it with INDEX and MATCH.
2. Find who had the maximum
| A | B | |
|---|---|---|
| 1 | Rep | Units |
| 2 | Dana | 410 |
| 3 | Luis | 295 |
| 4 | Mei | 520 |
| 5 | Omar | 180 |
| 6 | ||
| 7 | Mei |
MATCH finds the position of the biggest number and INDEX reads the name at that position. Together they answer "who sold the most", which is the question tests actually ask.
3. Largest value that meets a condition
| A | B | |
|---|---|---|
| 1 | Region | Sale |
| 2 | West | 450 |
| 3 | East | 900 |
| 4 | West | 620 |
| 5 | East | 310 |
| 6 | West | 275 |
| 7 | ||
| 8 | 620 |
MAXIFS adds criteria. This returns the highest sale in the West region only, ignoring the larger 900 in the East. It is available in Excel 2019 and Microsoft 365.
Common MAX mistakes
- Expecting MAX to find the row. It returns only the value. To get the name next to it, use INDEX and MATCH, or XLOOKUP with MAX as the lookup value.
- Text numbers. A value stored as text is ignored. If MAX returns a number smaller than one you can see, convert the text cells.
- Forgetting that MAX includes hidden rows. A filtered-out row still counts. Use AGGREGATE(4, 5, range) to ignore hidden rows.
MAX on Excel job tests
"Find the highest sale in column C" is the easy version. The follow-ups are where the time goes: who made that sale, or what was the highest sale in one region.
For the name, put MAX inside MATCH and hand the position to INDEX, or use XLOOKUP. For the highest within a group, MAXIFS works in Excel 2019 and Microsoft 365. If the test mentions Excel 2016, MAXIFS does not exist there, so use MAX(IF(...)) confirmed with Ctrl+Shift+Enter.
Give back what was asked. If the cell should hold a name, a number there is wrong even when it is correct for a different question.
Related functions
MAX questions
- How do I find the second-largest value?
- Use LARGE(range, 2). MAX only returns the top value, while LARGE and SMALL take a rank.
- How do I get the maximum with a condition?
- MAXIFS(max_range, criteria_range, criteria) in Excel 2019 or later. In older versions, use an array formula such as MAX(IF(...)) confirmed with Ctrl+Shift+Enter.
- Does MAX ignore text?
- Inside a range, yes. Text, blanks and logical values are skipped. Typed directly as an argument, TRUE counts as 1.
- What does MAX return for dates?
- The latest date, because dates are numbers. MIN returns the earliest.
Practice MAX
Try it in a working grid: the free lesson MIN and MAX (module 4 of Excel from Zero) has checked MAX exercises.
Practice this function