How to Use MAX in Excel
Returns the largest number in a range — highest sale, top score, latest date or most recent invoice.
What is MAX in Microsoft Excel?
MAX scans a range and returns the biggest numeric value. Because Excel stores dates as numbers, MAX also works on date columns to find the latest date. Use MIN for the smallest value, or MAXIFS when you need the largest value that meets a condition.
Formula Syntax
Arguments Breakdown
| Argument | Requirement | Purpose & Details |
|---|---|---|
| number1 | Required | The first number, cell or range to evaluate. |
| number2 | Optional | Additional numbers or ranges. Up to 255 arguments are allowed. |
When to Use MAX (Real-World Business Scenarios)
- 1
Finding the highest sale amount, top exam score or peak monthly revenue.
- 2
Getting the latest transaction date for a customer with =MAX(range of dates).
- 3
Setting a chart axis or a conditional formatting rule based on the biggest value.
Step-by-Step Practical Business Example
Suppose you have the following dataset in Excel cells A1:D4:
| Salesperson | Deal Value |
|---|---|
| Rahul | 125000 |
| Pooja | 98000 |
| Aman | 142000 |
How it works: Compares the three deal values and returns the largest one, 142,000, which belongs to Aman.
Common Errors & How to Fix Them
Why it happens: The range contains no numbers, only text. MAX returns 0 instead of an error when nothing numeric is found.
Fix: Check the column's number format and convert text-formatted numbers with VALUE or Text to Columns.
Why it happens: Some dates were typed as text, so MAX ignored them and returned an older real date.
Fix: Select the column and use Data > Text to Columns to force a real date format, then re-run MAX.
Try MAX Live in Your Browser
No need to launch desktop Excel. Practice formulas with real-time feedback in our HyperFormula sandbox.
Frequently Asked Questions (MAX)
What does the MAX formula do in Excel?
MAX scans a range and returns the biggest numeric value. Because Excel stores dates as numbers, MAX also works on date columns to find the latest date. Use MIN for the smallest value, or MAXIFS when you need the largest value that meets a condition.
What is the syntax for MAX?
What are common mistakes when using MAX?
Returns 0: The range contains no numbers, only text. MAX returns 0 instead of an error when nothing numeric is found. (Check the column's number format and convert text-formatted numbers with VALUE or Text to Columns.) Latest date looks wrong: Some dates were typed as text, so MAX ignored them and returned an older real date. (Select the column and use Data > Text to Columns to force a real date format, then re-run MAX.)
Related Excel Formulas
Keep building your formula toolkit with these math & stats functions.
- SUMIFSSums numerical values across multiple conditions (e.g. Sales in North Region for Q1).
- COUNTIFSCounts the number of rows that satisfy multiple simultaneous criteria.
- SUMAdds numbers across cells, ranges and entire columns — the first formula most people ever type in Excel.
- SUMIFAdds only the numbers that match one condition — the fastest way to total a region, product or status without a Pivot Table.
- COUNTIFCounts how many cells match a condition — duplicates, blanks, specific text, or values above a limit.
- AVERAGEReturns the arithmetic mean of a range while automatically ignoring blanks and text.
Master MAX in Basic Excel
Learn MAX along with our formula library, practice files, and verifiable certificates in our complete curriculum.