Excel MAX Function

MAX returns the largest number in a set of values you give it. It works on a single range, several ranges, or a list of typed numbers, and it ignores blank cells, text, and TRUE/FALSE values along the way, as long as those values live inside a cell reference. Type a logical value directly into the formula, though, and MAX treats it differently. More on that below, because it's the one behavior that trips up even experienced users.

Syntax

=MAX(number1, [number2], ...)

Parameter	Required	Description
`number1`	Yes	A number, cell reference, or range to evaluate. Can also be a typed value like `12` or `TRUE`.
`number2, ...`	No	Additional numbers or ranges. MAX accepts up to 255 total arguments.

Basic Example

You track monthly revenue for a five-person sales team in a column named monthly_sales, covering January through December. To find the best month:

=MAX(monthly_sales)

// monthly_sales = B2:B13, one total per month

MAX scans all 12 values and returns the single highest number, say $84,200 for August. It doesn't tell you which month that is, just the value. If you need the month label too, see the MATCH and INDEX pattern under Common Use Cases below.

How MAX Works

MAX ignores text, blanks, and logical values inside a range

If a range includes a blank cell, a word like "N/A", or a TRUE/FALSE value, MAX skips them and evaluates only the actual numbers. A column of exam scores with a few blank rows for absent students still returns the correct top score. This is quiet, useful, and rarely causes problems.

MAX does not ignore logical values typed directly as arguments

Type TRUE or FALSE directly into a MAX formula, and MAX treats it as a number: TRUE equals 1, FALSE equals 0. So =MAX(TRUE, 5, FALSE) returns 5, but =MAX(A1, 5) where A1 contains TRUE ignores A1 entirely if A1 is a cell reference within a range argument, not a standalone value. This inconsistency is one of the most common sources of confused MAX results, and most articles gloss over it.

MAX accepts multiple ranges and mixed arguments in one formula

You can combine ranges, individual cells, and typed numbers in a single call:

=MAX(Q1_sales, Q2_sales, 18750)

This returns the highest value across two named ranges plus the number 18750, useful when you want to compare actual data against a target or benchmark baked into the formula.

AutoSum builds a MAX formula for you

Select a contiguous column or row of numbers, go to the Home tab, click the arrow next to AutoSum, and choose Max. Excel inserts =MAX(...) with the range already filled in below your selection. It's a fast way to check a single column without typing anything.

Common Use Cases

Find the top score in a class

A teacher tracks exam results in a range named scores and wants the highest grade.

=MAX(scores)   // returns the single highest score

Find the max value that meets one condition

MAX has no built-in criteria argument, so a single-condition max needs an array formula or MAXIFS. Here's the array version, useful when you're on an older workbook that still needs to work in legacy Excel:

=MAX(IF(department="Sales", salary))

// department = the range of department labels
// salary = the range of salary values
// Enter with Ctrl+Shift+Enter in older Excel, or normally in Excel 365

If you're on Excel 365 or 2021, skip the array syntax entirely and use MAXIFS instead. It's cleaner and doesn't require special entry.

Find which row contains the max value

Combine MAX with MATCH to locate the row, then INDEX to pull the label from that row.

=INDEX(employees, MATCH(MAX(sales), sales, 0))

// sales = the range of sales totals
// employees = the range of employee names, same row order as sales

MAX finds the top number. MATCH locates its position in the sales range. INDEX uses that position to return the matching employee name instead of just the number.

Compare performance against a moving benchmark

A commission tracker checks the higher of a rep's actual sales or a guaranteed minimum.

=MAX(C2, 27500)   // pays out whichever is higher: actual sales or the $27,500 floor

Handling Errors

MAX doesn't generate its own errors under normal use, but it propagates any error already present in the range. If one cell in your data contains #DIV/0!, #N/A, or #REF!, MAX returns that same error instead of a number.

Common causes:

A formula elsewhere in the range is broken (a divide-by-zero, a bad lookup, a deleted reference)
The range includes a cell pulling from a closed or missing external workbook
A helper column used inside the MAX range has its own calculation error

=IFERROR(MAX(sales_data), "Check range for errors")

✓

If you want the correct maximum even when the range has errors mixed in, skip IFERROR and use =AGGREGATE(4, 6, sales_data) instead. Function number 4 tells AGGREGATE to act like MAX, and option 6 tells it to ignore error values while calculating, so you get a real answer instead of a blank fallback.

Notes & Gotchas

Why does MAX return 0 instead of an error when the range has no numbers?

MAX returns 0 if every cell in the range is text, blank, or otherwise non-numeric. It doesn't throw an error, which means a broken lookup or an entirely text-based column can silently return 0 instead of alerting you to the problem. Always check that your range actually contains numbers before trusting a MAX result of exactly 0.

Why does MAX return an error like #DIV/0!?

MAX passes through whatever error already exists in the range rather than generating a new one. Fix the underlying formula causing the error, or wrap the range in AGGREGATE with option 6 to ignore errors and calculate the max from the remaining valid numbers.

Does MAX work with dates?

Yes. Dates are stored as serial numbers, so MAX on a date range returns the latest date as a number. Format the cell containing the formula as a date, or the result displays as a five-digit number like 45983 instead of a readable date.

Does MAX evaluate TRUE and FALSE values?

It depends on how they're entered. MAX ignores logical values inside a range reference, but treats logical values typed directly as arguments as 1 (TRUE) or 0 (FALSE). This is the single most misunderstood behavior of MAX, since the same value can be treated two different ways depending on whether it's a reference or a literal.

What's the difference between MAX and MAXIFS?

MAX returns the largest value in a range with no conditions attached. MAXIFS returns the largest value that meets one or more criteria, like the highest sale made specifically by the East region in Q3. If you need any kind of filtering, MAXIFS is almost always simpler than wrapping MAX in an array formula.

How do I find the row or label associated with the max value?

MAX alone only returns the number. Pair it with MATCH to find the row position of that number, then wrap the result in INDEX to pull a related label, like an employee name or product ID, from the same row. See the Common Use Cases section above for the full formula.

Related Functions

Function	Use this when...
`MAXIFS`	You need the largest value that meets one or more conditions, instead of the unconditional max.
`MATCH`	You need the position of the max value in its range, typically before feeding that position into INDEX.
`INDEX`	You need to pull a related label, like a name or ID, from the same row as the max value.
`AGGREGATE`	Your range has errors mixed in and you need MAX to ignore them instead of propagating the error.