Mastering the MAX Formula in Excel
Learn how =MAX() finds peak performance and upper boundaries, how to rank 2nd and 3rd highest values with LARGE(), and how to avoid common analyst mistakes.
📈 What is the MAX Formula?
The MAX formula returns the highest numerical value in a range or set of numbers. Data Analysts use it daily to determine:
- Who was our top sales representative this month?
- What was our peak website traffic day?
- What was the highest revenue transaction?
B2:B10.⚖️ MAX vs LARGE() vs MAXA
=MAX()
Ignores text & blank cells. Finds the absolute peak number in the range.
=LARGE()
Finds the k-th largest value. Passing 2 returns 2nd highest, 3 returns 3rd highest!
=MAXA()
Evaluates TRUE as 1, and FALSE or text as 0.
🎮 Interactive MAX & LARGE Simulator — Live Spreadsheet
Change any sales rep revenue or select different formulas to see live cell highlighting & step-by-step ranking breakdown!
=MAX(B2:B6)⚠️ Key Analyst Rules for MAX
Negative Numbers (-5 is LARGER than -20)
When working with loss figures or negative variances, remember that -5 is larger than -20 (closest to zero).
Text-Formatted Numbers Ignored
If numbers are stored as text (e.g. "1000"), standard =MAX() will skip them. Convert text numbers to real numbers.
Status Bar Quick Check
Right-click the Excel Status Bar (bottom right) and check Maximum. Select any column to instantly see peak values live!
Test Your MAX Mastery
10 interview-level questions on MAX, LARGE(array, k), negative values, and status bar shortcuts