The MAX function returns the largest number from a set of values or a range. It ignores text, logical values, and empty cells, making it ideal for finding the highest score, sale, or date.
=MAX(number1, [number2], …)What does MAX do?
The MAX function is one of Excel's most straightforward statistical tools. You give it a group of numbers—either typed directly into the formula or stored in cells—and it tells you which one is the largest. It is like asking Excel to scan a list and point to the biggest value.
Why does this matter? In everyday work, you often need to know the highest sale of the month, the top score on a test, or the latest date in a project log. Doing that by eye is slow and error-prone. MAX does it instantly and updates automatically when your data changes.
Imagine you are a sales manager reviewing regional performance. You have a column of monthly sales figures and need to report the best-performing region. Instead of sorting or scanning, you type =MAX(B2:B10) and get the answer in a second. That is the kind of practical help MAX provides.
When to use MAX
- Finding the highest sales figure across regions or products.
- Identifying the top score in a class or competition.
- Determining the most recent date in a list of transactions.
- Comparing budget vs. actual and spotting the largest overspend.
- Quickly checking the maximum value in a dataset before creating a chart.
MAX syntax
=MAX(number1, [number2], …)| Argument | Required? | What to enter |
|---|---|---|
number1 | Required | The first number, cell reference, or range to evaluate. Example: B2:B10 or 100. |
number2 | Optional | Additional numbers, references, or ranges (up to 255). Example: C2:C10 or 200. |
What it returns: A number: the largest numeric value among the supplied arguments. If no numeric values are found, MAX returns 0.
MAX examples
1 Basic MAX with a range
You have a list of monthly sales in column B and want the highest sale. The formula =MAX(B2:B6) tells Excel to look at cells B2 through B6 and return the largest number. It ignores any text or blanks. The result is 1500, the highest value in the range.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Month | Sales | |||
| 2 | Jan | 1200 | 1500 | ||
| 3 | Feb | 1500 | |||
| 4 | Mar | 900 | |||
| 5 | Apr | 1100 | |||
| 6 | May | 1300 | |||
| 7 |
2 MAX with multiple ranges
Suppose you have two columns of scores—one for the first half of the year and one for the second. You want the highest score across both. The formula =MAX(B2:B4, C2:C4) checks all numbers in B2:B4 and C2:C4 and returns the largest. Here, the maximum is 95 from cell C3.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Student | Score 1 | Score 2 | |||
| 2 | Alice | 85 | 90 | 95 | ||
| 3 | Bob | 78 | 95 | |||
| 4 | Carol | 92 | 88 | |||
| 5 |
3 MAX with a condition using IF
You want the highest sale for the North region only. Since MAX alone cannot filter, you combine it with IF in an array formula: =MAX(IF(A2:A6="North", B2:B6)). In modern Excel (365, 2021), this works normally; in older versions, press Ctrl+Shift+Enter. The formula checks each row: if the region is North, include the sales value; otherwise ignore it. Then MAX returns the largest among those, which is 1400.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Sales | |||
| 2 | North | 1200 | 1400 | ||
| 3 | South | 1500 | |||
| 4 | North | 1400 | |||
| 5 | East | 1100 | |||
| 6 | North | 1300 | |||
| 7 |
4 MAX with text and blanks
Real data often contains text like "N/A" or empty cells. MAX ignores both. Here, column B has numbers, a text entry, and a blank. The formula =MAX(B2:B6) looks only at numeric values (100, 200, 50) and returns 200. This makes MAX robust for messy datasets.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Item | Value | |||
| 2 | Item1 | 100 | 200 | ||
| 3 | Item2 | 200 | |||
| 4 | Item3 | N/A | |||
| 5 | Item4 | 50 | |||
| 6 | Item5 | ||||
| 7 |
Common MAX errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #VALUE! | Occurs when an argument is text that cannot be converted to a number, and you are not using a range. For example, =MAX("abc", 10). | Ensure direct arguments are numbers or cell references containing numbers. Use ranges to let MAX ignore text automatically. |
| #NUM! | Rare, but can happen if the range contains an error value like #NUM! or if the result is too large. | Check the source data for errors and correct them. Use IFERROR to handle unexpected errors. |
| 0 (unexpected) | MAX returns 0 when no numeric values are found in the arguments. | Verify that the range contains numbers. If you expect a blank, use =IF(COUNT(B2:B10)=0, "", MAX(B2:B10)). |
| Wrong result with logical values | MAX ignores logical values (TRUE/FALSE) in ranges, but includes them if typed directly as arguments. | Avoid mixing logical values; convert them to numbers if needed. |
Tips for using MAX
- Use MAX with a range (e.g.,
B2:B100) rather than listing individual cells—it is faster and easier to maintain. - To find the highest value with criteria, combine MAX with IF as an array formula, or use MAXIFS in Excel 2019/365.
- Format the result cell as Date if you are finding the latest date; otherwise you may see a serial number.
- Remember that MAX ignores text and blanks, so it is safe for messy data—but double-check that no numbers are stored as text.
- Use
=MAX(0, B2:B10)to ensure the result is never negative (useful for financial data). - Combine MAX with CELL or INDEX/MATCH to retrieve the name associated with the highest value.
Frequently asked questions
What is the difference between MAX and MAXA?
MAX ignores logical values and text in ranges, while MAXA includes them (treating TRUE as 1, FALSE as 0, and text as 0). Use MAX for pure numbers, MAXA if your range contains logicals you want counted.
Can MAX handle dates?
Yes. Excel stores dates as numbers, so MAX returns the latest date. Just format the result cell as a date to display it correctly.
Why does MAX return 0 instead of the highest number?
This happens when the range contains no numeric values—perhaps all entries are text or the range is empty. Check that your numbers are not stored as text (they may be left-aligned).
How do I find the maximum value with a condition?
Use MAXIFS (Excel 2019+) or an array formula like =MAX(IF(criteria_range=criteria, max_range)). In older Excel, enter the array formula with Ctrl+Shift+Enter.
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. MAX reference