MS Excel: MAX function, full understanding

The MAX function is one of the most commonly used Excel functions when working with numerical data. It helps identify the largest number in a set of values.

Beginner Level

The MAX function returns the largest numeric value from a range or list of numbers.

Syntax:

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

• number1, number2, ... — the numbers or cell ranges from which to find the maximum value.

Example:

=MAX(10, 20, 5, 35)

Result: 35

=MAX(A1:A5)

If A1:A5 = {12, 45, 8, 20, 30},Result: 45

Intermediate Level

Using MAX with Cell Ranges

You can pass multiple ranges or a mix of values and ranges:

=MAX(A1:A5, B1:B5)

Ignoring Text and Logical Values

The MAX function ignores text and logical values (TRUE/FALSE) unless they are typed directly:

=MAX(10, "Text", TRUE)

Result: 10 (Text is ignored, TRUE is treated as 1 if entered directly)

Advanced Level

Using MAX with Other Functions

MAX with IF (Array Formula)

Find the maximum score only for "Math" subject:

A	B
Subject	Score
Math	80
English	90
Math	95

=MAX(IF(A2:A4="Math", B2:B4))

This is an array formula, so in older Excel versions, press Ctrl + Shift + Enter.

MAX with FILTER (Dynamic Arrays - Excel 365/Excel 2019+)

=MAX(FILTER(B2:B4, A2:A4="Math"))

No need for array entry. Excel returns 95.

MAX vs. LARGE

• MAX(range) → returns the largest number

• LARGE(range, k) → returns the k-th largest number

Example:

=LARGE(A1:A5, 2)

Returns the 2nd largest value in A1:A5.

MAX with Conditions (Using MAXIFS)

If you use Excel 2019 or newer:

=MAXIFS(B2:B10, A2:A10, "Math")

Finds the maximum score where subject is Math.

Summary

Scenario	Formula Example	Description
Find max in numbers	=MAX(10, 20, 30)	Returns 30
Max in a range	=MAX(A1:A10)	Max value from A1 to A10
Max with condition (IF)	=MAX(IF(A1:A5="Yes", B1:B5))	Max B1:B5 where A1:A5 = Yes
Max with condition (MAXIFS)	=MAXIFS(B1:B5, A1:A5, "Yes")	Same as above (new Excel only)
Max of filtered data	=MAX(FILTER(B1:B5, A1:A5="Yes"))	Works in Excel 365/2019+

The MAX function is powerful and easy to use, ideal for data analysis, finding top scores, or summarizing numeric results. As you move from beginner to advanced levels, combining MAX with other Excel functions like IF, FILTER, or MAXIFS unlocks even greater possibilities.