Three ways to find maximum values in Excel — each answers a slightly different question.
=MAX(A2:A100)
-- Returns the single largest value in the range
-- Ignores text and blanksUse MAX when you want the top value from an entire range with no conditions.
=LARGE(array, k)
=LARGE(A2:A100, 1) -- largest (same as MAX)
=LARGE(A2:A100, 2) -- 2nd largest
=LARGE(A2:A100, 3) -- 3rd largest
=LARGE(A2:A100, 10) -- 10th largestUse LARGE when you need the top 3, top 5, or any ranked value — leaderboards, top performers, highest scores.
=MAXIFS(max_range, criteria_range1, criteria1, ...)
=MAXIFS(B2:B100, A2:A100, "North")
-- Largest value in B where A = "North"
=MAXIFS(C2:C100, A2:A100, "North", B2:B100, "Active")
-- Largest value in C where A="North" AND B="Active"Use MAXIFS when you need the maximum within a filtered subset — highest sale in a region, top score in a category.
| Question | Function |
|---|---|
| What is the highest value overall? | MAX |
| What is the 3rd highest value? | LARGE(range, 3) |
| What is the highest value in the North region? | MAXIFS |
| Show top 5 values in a list | LARGE with k=1,2,3,4,5 |
Everything above applies equally to MIN, SMALL, and MINIFS — just in the opposite direction. SMALL(range, 2) gives the 2nd smallest value. MINIFS gives the minimum matching conditions.
Practice this formula yourself — type it in a real spreadsheet and get instant feedback. Free, no download needed.
Start the Excel Basics track free →