MAX function
Quick answer
MAX returns the largest number from a list of values or ranges: the top sale, the latest date, the highest temperature.
- Syntax
=MAX(number1, [number2], ...)- Most common use
=MAX(B2:B5)returns 340 (The item with the most units on hand)
With a 0 as one of its arguments it also acts as a floor, which is the standard way to stop a calculation from going negative.
- Available in
- Every current version
- Category
- Statistical functions
- Returns
- The largest number, or 0 if the ranges contain no numbers.
- Google Sheets
- Also available in Google Sheets.
Syntax
=MAX(number1, [number2], ...)
| Argument | Required? | What it is |
|---|---|---|
number1 | Required | The first number, cell or range to check. |
number2 | Optional | More numbers, cells or ranges, up to 255 in total. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| F2 | =MAX(B2:B5) | 340 | The item with the most units on hand |
| F3 | =MAX(C2:C5) | 150 | The highest reorder point |
| F4 | =MAX(0,C3-B3) | 4 | Units of toner to order, never below zero |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Item | On hand | Reorder point | Check | Result | |
| Paper | 120 | 80 | Most stock | 340 | |
| Toner | 6 | 10 | Highest reorder point | 150 | |
| Pens | 340 | 150 | Toner to order | 4 | |
| Folders | 45 | 60 |
Practice MAX
From lesson 2, SUM, AVERAGE, MIN, MAX & COUNT. Graded instantly, then re-checked on changed data.
Average, best and worst
E3 (average daily sales), E4 (best day) and E5 (worst day), each from B2:B8.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| MAX returns 0 | There are no numbers in the range, often because they were imported as text. |
| A negative result where you wanted zero | Wrap the calculation as MAX(0, calculation) so anything below zero becomes 0. |
| The result ignores a filter | MAX looks at hidden rows too. Use AGGREGATE or SUBTOTAL if you need the max of visible rows only. |
Tips
- MAXIFS finds the largest value that meets one or more conditions.
- MAX on a date column returns the most recent date.
Questions
How do I get the second-highest value?
Use =LARGE(B2:B5,2). MAX only returns the single largest.
How do I return the name next to the max value?
Combine with a lookup: =XLOOKUP(MAX(B2:B5),B2:B5,A2:A5).
Lessons that use MAX
Answers that use MAX
In other languages
MAX is MAX in Spanish Excel and MAX in German Excel. Each page has the syntax with semicolons, the examples and the exercise in that language. For other functions and languages, use the Function Translator.
Updated Checked by running: every formula on this page was run by our formula engine on .