MAX vs LARGE in Excel

MAX vs LARGE in Excel is simple once you see it: MAX returns the single highest number, and LARGE returns the number at any position from the top. =LARGE(B2:B8,1) is the same as =MAX(B2:B8), but only LARGE can give you the second highest, a top 3 or the tenth best result. This guide is for anyone who reports on sales, scores or salaries and keeps getting asked "and who came second?"

MAX vs LARGE at a glance#

MAX is the shortcut for "first place"; LARGE handles every place.

How MAX works#

MAX looks at every number you give it and returns the biggest one. You can pass ranges, single cells and typed numbers, up to 255 of them:

=MAX(B2:B8)
=MAX(B2:B8,D2:D8)
=MAX(B2:B8,90000)

In a range, MAX skips empty cells, text and TRUE/FALSE values. If a range holds no numbers at all, MAX returns 0, not an error. MIN is its mirror image for the smallest number. Our guide to AVERAGE, MIN and MAX covers all three, and the AVERAGE function guide explains how the same "skip blanks and text" rule affects averages.

How LARGE works#

LARGE sorts the numbers in your range from largest to smallest in its head, then returns the one at position k:

=LARGE(array, k)

• array: the range of numbers.
• k: the position from the top. 1 is the largest, 2 the second largest, and so on.

k must be between 1 and the count of numbers in the range. Ask for position 8 in a range of 7 numbers and LARGE returns #NUM!. SMALL works the same way from the bottom: =SMALL(range,2) is the second lowest. Our guide on finding the top 3 with LARGE and SMALL has more examples.

MAX and LARGE side by side#

Here is one sales table, with two reps tied on 72,300:

Notice rows 3 and 4 of the results. Dev and Femi both sold 72,300, so that value fills positions 2 and 3. LARGE counts every value, including repeats. In real Excel you can also write the top-3 total as =SUM(LARGE(B2:B8,{1,2,3})).

How to handle ties#

Ties are the most common surprise with LARGE, and interviewers like asking about them. If you want the second-highest different value, there are two good options:

=MAXIFS(B2:B8,B2:B8,"<"&MAX(B2:B8))
=LARGE(UNIQUE(B2:B8),3)

The first finds the largest value that is less than the top value: 72,300. It works in Excel 2019 and later; see MAXIFS and MINIFS for how the criteria work. The second removes duplicates before ranking, so position 3 is now 64,200. UNIQUE needs Excel 2021, Excel 2024 or Microsoft 365.

To get the name of a top rep, look the value up: =XLOOKUP(LARGE(B2:B8,2),B2:B8,A2:A8) returns Dev. With a tie, it always returns the first match, so Femi never appears. For a full ranked list with names, =SORT(A2:B8,2,-1) is easier; our comparison of the Sort button vs the SORT function explains it.

What about the highest value with a condition?#

Neither MAX nor LARGE takes a condition on its own, but each has a partner that does. Suppose column C holds each rep's region:

=MAXIFS(B2:B8,C2:C8,"North")
=LARGE(FILTER(B2:B8,C2:C8="North"),2)

The first returns the top North sale and works in Excel 2019 and later. The second filters the sales down to North first, then takes the second largest of what's left; it needs Excel 2021, Excel 2024 or Microsoft 365, because FILTER does. In older versions, a helper column that shows the sale only for North rows (and leaves others blank) lets plain LARGE do the same job: =IF(C2="North",B2,""), filled down, then =LARGE(D2:D8,2) on that column. LARGE skips the empty text results, just as it skips blanks.

When to use MAX#

Use MAX when you only need the single highest value. It is the better choice when:

• You want the top figure, such as the best month or the highest score.
• The numbers sit in several places, such as =MAX(B2:B8,D2:D8) across two columns.
• You cap a value, for example =MIN(MAX(A2,0),100) keeps a score between 0 and 100.
• You find a spread, such as =MAX(B2:B8)-MIN(B2:B8) for the gap between best and worst.
• You want the latest date, such as the most recent order. Excel stores dates as numbers, so =MAX(C2:C50) returns the newest date and =MIN(C2:C50) the oldest. Format the result cell as a date. The same trick works with LARGE: =LARGE(C2:C50,2) is the second most recent date.

When to use LARGE#

Use LARGE whenever the question is about a position other than first. It is the better choice when:

• You need the second or third highest, a classic interview question.
• You build a top-N table, with k in a helper column (1, 2, 3…) so one formula fills down: =LARGE($B$2:$B$8,D2).
• You sum or average the top few, such as the best 3 test scores out of 5.
• You want the same formula style for every place, including first, so a ranking table reads consistently.

If you're choosing between two "summary" functions more broadly, our comparison of AVERAGE vs MEDIAN explains which one describes a typical value better. For time-based summaries, see DAYS vs DATEDIF and NETWORKDAYS vs WORKDAY.

Common mistakes with MAX and LARGE#

A few slips cause most wrong answers:

1. Forgetting k. =LARGE(B2:B8) is incomplete; Excel won't accept it without the second argument.
2. Expecting LARGE to skip ties. It doesn't. Use MAXIFS or UNIQUE as shown above.
3. k bigger than the count. Returns #NUM!. Check with =COUNT(B2:B8).
4. Numbers stored as text. Both functions ignore them, so the "highest" value may be missing. Look for green triangles in the corner of the cells.
5. Using MAX for a conditional maximum. "Highest sale in the North" needs MAXIFS, not MAX.
6. Not locking the range in a top-N table. Use $B$2:$B$8 so the range doesn't slide when you fill down.

Our recommendation#

Use MAX for the top value and LARGE for everything else. MAX is shorter and clearer when first place is all you need, and it can scan several ranges at once. LARGE is the right tool for "second highest", top-N lists and sums of the best few, as long as you remember that tied values each take a position.

In short: MAX for first place, LARGE for any place.

Practise it#

Try the second-highest exercise on this page, then open the LARGE and SMALL lesson to change a value into a tie and watch the results move, add up a top 3 and find the second-lowest sale. The AVERAGE, MIN and MAX lesson covers the basics, including the gap between the most and the fewest.