MIN and MAX in Excel
Last updated
MAX returns the largest number in a range and MIN the smallest. For example, =MAX(B2:B9) gives the highest order and =MIN(B2:B7) the lowest quote. Both skip empty cells and text, so a supplier who hasn’t replied doesn’t count as 0.
MIN and MAX syntax
=MAX(number1, [number2], …)
- number1
- A range such as
B2:B9, a cell, a number or a calculation. A range can span several columns, likeB2:E6. - number2, …
- Optional. More ranges or values, separated by commas:
=MIN(B2*0.1,$F$1)keeps the smaller of two values. MIN takes the same arguments as MAX.
Practice MIN and MAX here
Type your formula straight into the sheet. It’s checked like real Excel, and you can write it with commas or semicolons.
Find the highest order
The amounts of this month’s orders are in B2:B9. In E2, return the highest amount with MAX.
| Order | Amount ($) | |||
| #4101 | 1,240 | Highest order | ||
| #4102 | 385 | |||
| #4103 | 2,760 | |||
| #4104 | 918 | |||
| #4105 | 1,575 | |||
| #4106 | 642 | |||
| #4107 | 2,190 | |||
| #4108 | 1,033 |
How to use MIN and MAX, step by step
- Click the cell where you want the result and type
=MAX(for the largest value or=MIN(for the smallest. - Select the range of numbers, for example
B2:B9. Include empty cells if there are any: they are ignored. - To compare a value with a limit, type a comma and the second value instead, such as
$F$1. - Close the parenthesis and press Enter. The result updates whenever a number in the range changes.
MIN and MAX examples
Highest value
=MAX(B2:B9)
Returns the largest order of the month: 2,760.
Lowest value
=MIN(B2:B7)
Returns the cheapest quote and ignores the supplier who hasn’t replied.
Spread between highest and lowest
=MAX(B2:B9)-MIN(B2:B9)
Shows how far prices moved: 5.45 − 4.60 = 0.85.
Cap a value
=MIN(B2*0.1,$F$1)
Pays 10% of the sales as a bonus, but never more than the cap in F1.
Common MIN and MAX problems and how to fix them
| Result | Cause | Fix |
|---|---|---|
| 0 | The numbers are stored as text, for example after an import (they sit on the left of the cell). MIN and MAX skip text in a range, so a range with no real numbers returns 0. | Convert the cells to real numbers, for example with Text to Columns, then the result updates. |
| MIN returns 0 | A cell holds 0 instead of being empty, for example a quote typed as 0 while waiting for the reply. | Leave missing values empty. To skip zeros on purpose, use MINIFS, covered in a later lesson. |
| #VALUE! | Text was typed straight into the formula as an argument, for example =MAX(B2,"n/a"). | Pass cells or ranges instead of text: text inside a range is ignored, text typed as an argument is not. |
| #NAME? | The function name is misspelled (=MAXX) or a localized name from another Excel language is used. | Type =MA and pick MAX from the list Excel suggests. |
MIN and MAX vs LARGE, SMALL and sorting
MIN and MAX return one extreme of a range. To get the second or third largest value, use LARGE and SMALL: =LARGE(B2:B9,2). Sorting the column also shows the extremes, but the answer goes stale as soon as the data changes, while a formula stays up to date. To take the smallest or largest value that meets a condition, such as the best month for one region, use MINIFS and MAXIFS, covered in later lessons.
Frequently asked questions
Does MIN ignore empty cells?
Yes. Empty cells and text inside a range are ignored. A cell that holds 0 does count, so =MIN returns 0 if one cell is 0.
How do I find the second highest value?
Use LARGE with the rank you want: =LARGE(B2:B9,2) returns the second highest value. SMALL works the same way from the bottom.
Can MIN and MAX use several ranges?
Yes. Separate them with commas: =MAX(B2:B9,D2:D9) returns the largest value across both ranges.
How do I find the smallest value that meets a condition?
Use MINIFS, or MAXIFS for the largest. They work like SUMIF and are covered in later lessons of the course.
Keep practising
All 5 MIN and MAX exercises, with explanations and progress tracking.
Open the MIN and MAX practice set