What it does
MAX takes one or more ranges and returns the largest number in them, skipping empty cells, text and TRUE/FALSE values. On a date column it returns the latest date: =MAX(A2:A100) is the date of the last order.
If you need the owner of the largest value rather than the value itself, combine MAX with INDEX and MATCH: =INDEX(A2:A20,MATCH(MAX(C2:C20),C2:C20,0)) returns the name of the rep with the highest sale. In Microsoft 365, XLOOKUP does the same.
Picking the larger of two values is also a way to set a floor: =MAX(0,C2-B2) returns 0 when the difference would be negative. For the largest value that meets a condition use MAXIFS; for the second or third largest, LARGE.
Syntax
=MAX(number1, [number2], ...)=MAK(sayı1; [sayı2]; ...)Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
number1 | Required | The first number, cell or range to search for the largest value. |
number2 | Optional | Optional further numbers or ranges, up to 255 arguments. |
Examples
1Find the highest sale and who made it
E2 finds the month's highest sale. F2 uses MATCH to find the row of that value and INDEX to return the rep's name from the same row.
=MAX(C2:C7)| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rep | Region | Sales | Highest | Rep | |
| 2 | Emma | West | 48,500 | 61,200 | Liam | |
| 3 | Liam | North | 61,200 | |||
| 4 | Olivia | West | 52,300 | |||
| 5 | Noah | South | 29,750 | |||
| 6 | Ava | North | 57,800 | |||
| 7 | Mia | South | 44,100 |
- E2
=MAX(C2:C7) - F2
=INDEX(A2:A7,MATCH(E2,C2:C7,0))
The month's highest sale is 61,200, made by Liam.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2Find the latest order date
In Excel dates are numbers, and a later date is a larger number. So MAX returns the latest date in the column; the result is given a date format.
=MAX(A2:A6)| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Date | Order | Customer | Last Order | |
| 2 | 2-Oct-2025 | S-1001 | Brown & Sons | 21-Oct-2025 | |
| 3 | 14-Oct-2025 | S-1002 | Northgate Ltd | ||
| 4 | 28-Sep-2025 | S-1003 | Harbour Trade | ||
| 5 | 21-Oct-2025 | S-1004 | Star Market | ||
| 6 | 9-Oct-2025 | S-1005 | Brown & Sons |
Even though the list is not sorted, the last order date is found: 21-Oct-2025.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
3Keep an order quantity from going negative
The order quantity is the target stock minus the current stock. When stock is above target the difference is negative; MAX(0,…) returns 0 in that case.
=MAX(0,C2-B2)| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Current | Target | Order Qty |
| 2 | Grounded socket | 140 | 250 | 110 |
| 3 | Circuit breaker | 320 | 300 | 0 |
| 4 | Light switch | 60 | 200 | 140 |
| 5 | Cable duct | 95 | 80 | 0 |
- D2 (filled down to D5)
=MAX(0,C2-B2)
110 sockets and 140 switches are ordered. For the breaker and the cable duct, which are above target, the result is 0.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- Result is 0
Cause: There are no numbers in the range; MAX returns 0, not an error, for an empty range. The numbers may also be stored as text.
Fix: Check with
=COUNT(C2:C20)whether the range holds numbers; convert text numbers with VALUE.- #N/A
Cause: One of the cells in the range holds an error, and MAX passes it on.
Fix: Fix the error at its source, or use AGGREGATE, which can skip errors:
=AGGREGATE(4,6,C2:C20).
Good to know
- To stop a calculation going below zero, wrap it in MAX:
=MAX(0,C2-B2). - If the largest value appears on several rows, INDEX and MATCH return only the first; to list them all in Microsoft 365, use FILTER.
In Nexell
Get Nexell to do it from a sentence
Add the month's highest sale and the name of the rep who made it under the sales table
Convert to tableBring your sales table into the tool and type this. Nexell finds the highest sale and the rep who made it and shows the result in a preview first; nothing changes until you approve.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
How do I find who has the highest value in Excel?
Use =INDEX(A2:A20,MATCH(MAX(C2:C20),C2:C20,0)): MATCH finds the row of the largest value and INDEX returns the name on that row.
What is the difference between MAX and LARGE?
MAX returns only the largest value. LARGE asks which largest you want: =LARGE(C2:C20,2) returns the second largest, and =LARGE(C2:C20,1) equals MAX.