MAX function
MAX returns the largest number from a list of values or ranges: the top sale, the latest date, the highest temperature. 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
Last reviewed October 2026. Every example result on this page is calculated live by the Spreadsheet Practice formula engine.