Skip to content

Excel function · Statistics

Excel MAX function

MAX returns the largest of the numbers you give it: the month's highest sale, the most expensive product, the latest order date. Dates are numbers too, so MAX also finds the most recent date.

In Turkish Excel: MAK

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

English Excel=MAX(number1, [number2], ...)
Turkish Excel=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

ArgumentRequiredDescription
number1RequiredThe first number, cell or range to search for the largest value.
number2OptionalOptional 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.

E2fx=MAX(C2:C7)
Find the highest sale and who made it: example table
ABCDEF
1RepRegionSalesHighestRep
2EmmaWest48,50061,200Liam
3LiamNorth61,200
4OliviaWest52,300
5NoahSouth29,750
6AvaNorth57,800
7MiaSouth44,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.

E2fx=MAX(A2:A6)
Find the latest order date: example table
ABCDE
1DateOrderCustomerLast Order
22-Oct-2025S-1001Brown & Sons21-Oct-2025
314-Oct-2025S-1002Northgate Ltd
428-Sep-2025S-1003Harbour Trade
521-Oct-2025S-1004Star Market
69-Oct-2025S-1005Brown & 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.

D2fx=MAX(0,C2-B2)
Keep an order quantity from going negative: example table
ABCD
1ProductCurrentTargetOrder Qty
2Grounded socket140250110
3Circuit breaker3203000
4Light switch60200140
5Cable duct95800
  • 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

Type this in Nexell

Add the month's highest sale and the name of the rep who made it under the sales table

Convert to table

Bring 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.

Related functions

All statistics functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free