MAX Function in Excel

MAX ignores text, empty cells, and logical values inside a range. MAXA treats text as 0, FALSE as 0, and TRUE as 1. When all your data is negative, a single TRUE inside a MAXA range silently becomes the maximum (because 1 > every negative number).

Rule: use MAX. MAXA only makes sense in very specific TRUE-as-1 scenarios, which is a design pattern almost nobody uses on purpose.

Pair MAX with INDEX/MATCH: =INDEX(A2:A100, MATCH(MAX(B2:B100), B2:B100, 0)). This looks up the position of the max in column B, then returns the label from the same row in column A.

In Excel 365, XLOOKUP is cleaner: =XLOOKUP(MAX(B2:B100), B2:B100, A2:A100). Same result, easier to read.

Use MAXIFS (Excel 2019+): =MAXIFS(value_range, criteria_range, criterion). Example: =MAXIFS(B2:B100, C2:C100, "North") returns the max B where C is "North".

For Excel 2016 and earlier: =MAX(IF(C2:C100="North", B2:B100)) entered with Ctrl+Shift+Enter.

MAX returns the single largest value. LARGE returns the k-th largest — you specify the position. =LARGE(range, 1) equals =MAX(range), but =LARGE(range, 2) gives you the runner-up.

LARGE is the essential building block for Top-N reporting. Combine with SUM or AVERAGE and an array constant: =SUM(LARGE(range, {1,2,3})) for the top-3 sum.

Yes. MAX returns the mathematically largest value. For a range of all negatives like {-50, -10, -80}, MAX returns -10 (the one closest to zero, i.e. the largest).

This is where the MAXA trap becomes obvious — a lone TRUE in the same range would make MAXA return 1, misrepresenting the whole picture.

Excel stores dates as serial numbers, so MAX naturally returns the latest date. Format the result cell as a date to display it. Text-formatted dates fail — convert with DATEVALUE() first.

MAX on a range of dates is how you find "the most recent transaction" or "the latest deadline" in a list.

Use the LARGE-array pattern: =SUM(LARGE(A2:A100, {1,2,3})). The array constant {1,2,3} asks LARGE for three values simultaneously; SUM adds them.

In Excel 365 and 2021, plain Enter works. In Excel 2016 and earlier, use Ctrl+Shift+Enter. For dynamic N, use SEQUENCE: =SUM(LARGE(A2:A100, SEQUENCE(E1))) where E1 holds the top-N value.

Yes. Zero is a valid number and gets considered. In a range like {-5, 0, -3}, MAX returns 0 (the largest).

To find the largest non-zero value, use =MAX(IF(range<>0, range)) as an array formula.

MAX propagates errors — any cell error in the range returns that error. Two fixes: (1) fix at the source with IFERROR in the input formulas; (2) use AGGREGATE, which has built-in error handling: =AGGREGATE(4, 6, range). The "4" means MAX and the "6" means "ignore errors." Works in Excel 2010+.

Yes. Highlight the largest value with formula =A2=MAX($A$2:$A$100). Only the cell(s) matching the max get highlighted.

For Top-3 highlighting, use LARGE: =A2>=LARGE($A$2:$A$100, 3). This highlights the top three values because "greater-than-or-equal-to the third-largest" means top three.

Yes. Use a 3D reference: =MAX(Sheet1:Sheet4!B2:B10) returns the max across the same range on four adjacent sheets. Or specify each range: =MAX(Sheet1!B2:B10, Sheet2!B2:B10).

The 3D syntax requires the sheets to be adjacent in the tab order. Rearranging tabs can silently break these formulas.

Yes, noticeably. MAXIFS was introduced in Excel 2019 specifically to replace slow array-formula versions. For a range of 100,000+ cells with criteria, MAXIFS is 3–10× faster.

If you're on Excel 2016 or earlier, the array pattern still works — it's just slower on large data. On modern Excel, always prefer MAXIFS.