The Excel MAX function, explained interactively.
MAX returns the largest number in a range. Its trick isn’t complexity — it’s what MAX silently ignores: text, empty cells, and booleans all get skipped without error. That’s usually what you want, occasionally a bug waiting to bite you.
How to use MAX
- Type
=MAX(and select the range of numbers — one contiguous range or a comma-separated list of ranges and individual numbers. - Close the parenthesis. MAX returns the largest numeric value — text, blanks, and booleans are ignored silently.
- For a conditional max (“highest sale in region X”), use
MAXIFSinstead. For the Nth-largest value, useLARGE(range, N). - To include booleans or text-as-zero, swap MAX for
MAXA— rarely needed, but useful for flag-style data.
In the demo, one cell is the text "N/A"— watch how MAX skips it without error and still returns the largest number in the range.
| A | B | D | |
|---|---|---|---|
| 1 | Rep | Sales | Max |
| 2 | Ava | 12,400 | 15,300 |
| 3 | Ben | 9,800 | |
| 4 | Chloe | 15,300 | |
| 5 | Dev | 8,700 | |
| 6 | Elena | N/A | |
| 7 | Farid | 14,100 | |
| 8 | Grace | 11,200 |
=MAX(B2:B8). MAX ignored the text cell ("N/A" at B6) and the largest numeric value is 15,300 (row 4, Chloe).MAX syntax and arguments
One required argument, up to 255 total. See Microsoft’s official MAX reference for the canonical specification.
MAX(TRUE, 5)) are included as 1/0. The common usage is a single range.MAX examples
Four patterns covering the overwhelming majority of real-world MAX use.
Example 1: MAX for a plain maximum
The canonical case — highest sales, top score, largest order.
Returns the largest numeric value in column B. Text, blanks, and booleans in that range are silently skipped. Returns 0 if the range has no numbers — not an error.
Example 2: MAX across multiple ranges
Compare the highest value across disjoint ranges, or between a range and a literal floor.
Takes the highest across B, D, and a literal floor of 0 — useful for clamping a computed result so it never goes negative. The literal 0 sets the minimum floor; swap for any baseline you want.
Example 3: MAXIFS for a conditional maximum
When you need the highest value that meets a criterion.
Returns the highest C value where A is “Food” AND B is “2026”. Same multi-criteria pattern as SUMIFS — aggregate range first, then range/criteria pairs.
Example 4: MAX combined with INDEX/MATCH
Find not just the maximum, but the row’s label (rep name, product, region).
MAX finds the largest sales number; MATCH finds its row; INDEX plucks the corresponding label. Classic INDEX + MATCH use case. If there are ties, MATCH returns the first occurrence.
Common MAX errors and fixes
Four failure modes, each with what to check and how to recover.
MAX returns 0 when numbers exist
Cause: the values look like numbers but are stored as text. MAX skips text silently and, finding no real numbers, returns 0.
Check with =ISNUMBER(B2). Coerce via Data → Text to Columns → Finish, or multiply by 1 in a helper column. Imports from CSVs and PDFs are common offenders.
MAX including hidden rows unexpectedly
Cause: MAX does not respect filter / hidden rows. Even hidden values participate.
Use SUBTOTAL(4, B2:B100) or AGGREGATE(4, 3, B2:B100) to respect visible rows only. Both skip hidden rows; AGGREGATE can also skip errors.
MAX returns #VALUE! on named arguments
Cause: one of the arguments passed as a literal is text (not in a range), so MAX tries to coerce and fails.
Cell-range references silently skip text; literal text arguments trigger #VALUE!. If you’re building MAX calls dynamically, wrap each piece to guarantee numeric or use IFERROR.
MAX choosing the wrong cell on ties
Cause: multiple rows share the maximum value. MAX returns the value, not a row — it’s MATCH or INDEX/MATCH that picks a row, and they return the first occurrence by default.
If you need the last tied row, use LOOKUP with a descending array or add a tie-breaker column (e.g. a row number) before MATCHing.
MAX vs MIN, MAXIFS & LARGE
Four aggregators. Pick based on which end of the distribution you need and whether the pick is conditional or ordinal.
| Function | Returns | Conditions | Typical use |
|---|---|---|---|
| MAX | Largest value | None | Top score, highest order, largest anything |
| MIN | Smallest value | None | Best / lowest price, earliest date, floor |
| MAXIFS | Largest matching value | One or many (AND) | Highest sale for a specific region + product |
| LARGE | Nth largest value | None | 2nd or 3rd largest — ranking, top-N lists |
Rule of thumb: MAX / MIN for single-ended extremes, MAXIFS / MINIFS when a condition is involved, LARGE / SMALL for the Nth rank. LARGE(range, 1) is equivalent to MAX but reads better when you also need the 2nd or 3rd largest in nearby cells.
MAX frequently asked questions
6.01Does MAX include text values or booleans?▸
No. MAX silently skips any cell whose value isn’t a number — text like "N/A", empty cells, and booleans are all ignored without error. If you need text-numbers counted, use MAXA instead, which coerces them (and TRUE = 1, FALSE = 0).
6.02How do I find the maximum that matches a condition?▸
Use MAXIFS: =MAXIFS(C2:C100, A2:A100, "Food") returns the maximum of C where A is “Food”. Introduced in Excel 2019 / 365. In older versions, use {=MAX(IF(A2:A100="Food", C2:C100))} as an array formula (Ctrl+Shift+Enter).
6.03Why does MAX return 0 when my cells contain numbers?▸
The numbers are stored as text — MAX sees no real numbers and falls back to 0. Select the column and run Data → Text to Columns → Finish to coerce them, or multiply by 1 in a helper column to force numeric conversion.
6.04How do I get the second-largest value (not just the max)?▸
Use LARGE: =LARGE(A2:A100, 2) returns the 2nd-largest. LARGE(range, 1) is equivalent to MAX; LARGE(range, 3) gives the 3rd-largest; and so on. Pairs neatly with SMALL for the Nth smallest.
6.05What’s the difference between MAX and MAXA?▸
MAX ignores text and booleans. MAXA treats TRUE as 1, FALSE as 0, and text as 0 — so an all-text range returns 0 (not blank). Use MAX for normal numeric columns; use MAXA only when you specifically need boolean or text-coerced behaviour.
Microsoft Excel is a registered trademark of Microsoft Corporation. Google Sheets is a trademark of Google LLC. Formula Gym is not affiliated with, endorsed by, or sponsored by either company.