Excel Tutorials
MIN and MAX are two of the simplest functions in Excel and two of the most useful, once you go past the raw number and start returning the name attached to it. This lesson covers both functions, how blanks and a stray zero can quietly ruin a minimum, finding the second or third largest value, and adding conditions with MINIFS and MAXIFS. It uses the Event Planner workbook from Excel Foundations, free to download below.
Follow along in the same file used in the video. No email required.
MAX returns the largest number in a range and MIN returns the smallest. =MAX(D2:D20) and =MIN(D2:D20) is the entire syntax, and they are usually the first two functions people learn after SUM and AVERAGE.
Both ignore blank cells, text and logical values, exactly as AVERAGE does. That is normally helpful, but it produces one genuinely dangerous failure: if the largest number in your column arrived from an import as text rather than a number, MAX skips it and returns the second largest instead, with no error and nothing on screen to suggest anything is wrong.
The other trap is zeros. A blank is ignored, but a real 0 is a real value, so MIN returns 0 on any column containing one. If zero means "nothing submitted yet" rather than a genuine measurement, your minimum is describing a gap in the data instead of the data. Both problems, and how to work around them, are covered below.
Step-by-step, matching the video above.
=MAX(D2:D20) returns the biggest number in the range. Like AVERAGE, it ignores blank cells, text and logical values entirely rather than treating them as zero.
=MIN(D2:D20) returns the lowest number. The syntax is identical, which is why the two are always taught together.
On the Home tab far right, or the Formulas tab, click the arrow beside AutoSum and pick Max or Min. Check the range Excel guessed before you press Enter, since it assumes the block directly above your cursor.
=MAX(D2:D10,F2:F10,25) works fine. Arguments can be ranges, individual cells, or literal numbers mixed together, and MIN behaves the same way.
Select a range and the status bar can display Min and Max alongside Average, Count and Sum. They are not shown by default, so right-click the status bar and tick Minimum and Maximum once, and they stay on from then on.
The largest value is rarely the whole question. =XLOOKUP(MAX(D2:D20),D2:D20,A2:A20) returns who or what that maximum belongs to, pulling the name from column A. On older Excel use =INDEX(A2:A20,MATCH(MAX(D2:D20),D2:D20,0)).
=LARGE(D2:D20,2) gives the second highest value and =SMALL(D2:D20,3) the third lowest. LARGE(range,1) is identical to MAX, so these are the general form of both functions.
=MAXIFS(D2:D20,B2:B20,"Gold") returns the highest value among Gold-tier rows only. The value range comes first, then pairs of criteria range and criteria. Both need Excel 2019 or Microsoft 365.
The same blanks-and-text behaviour as AVERAGE, with one consequence that catches people out badly.
An empty cell has no effect on either function. A column of positive numbers with gaps still returns the smallest actual number, not 0.
If any cell genuinely contains 0, MIN returns 0. On a column where zero means "not submitted yet" rather than a true measurement, this makes the minimum meaningless. Use =MINIFS(D2:D20,D2:D20,">0") to exclude zeros.
Numbers stored as text are invisible to both functions. If the true maximum arrived from an import as text, MAX will confidently return the second-largest value with no error at all, which is the most dangerous failure mode here.
A formula ending ,"") produces text, so MIN and MAX skip it. Useful, but only if you know it, since it looks identical to an empty cell on screen.
MINA and MAXA treat text as 0 and FALSE as 0, TRUE as 1. MINA on a column containing any text will therefore return 0, which is almost never the intention.
On a column of profit and loss figures containing both negatives and a stray zero, MIN returns the most negative number correctly. But on a column of all-positive values, one zero silently becomes the minimum.
| Formula | What It Asks | Result |
|---|---|---|
| =MAX(D2:D6) | Largest spend | 620.00 |
| =MIN(D2:D6) | Smallest spend | 150.00 |
| =LARGE(D2:D6,2) | Second largest | 480.00 |
| =SMALL(D2:D6,2) | Second smallest | 210.00 |
| =XLOOKUP(MAX(D2:D6),D2:D6,A2:A6) | Who spent the most | Northeast |
| =MAXIFS(D2:D6,B2:B6,"Gold") | Highest Gold-tier spend | 620.00 |
| =MAX(D2:D6)-MIN(D2:D6) | The spread across the group | 470.00 |
Row five is the one that changes how useful these functions are. MAX on its own tells you the number 620 exists somewhere; wrapping it in XLOOKUP tells you it belongs to the Northeast, which is the thing anyone reading the report actually wanted to know. The last row is the other habit worth forming: max minus min gives you the range, and a wide spread often says more about your data than either endpoint alone.
Where MIN and MAX earn their place inside larger formulas.
=MIN(D2,1000) caps a figure at 1000 no matter how large it gets, and =MAX(D2,0) stops a result going negative. Nesting them, =MIN(MAX(D2,0),1000), constrains a value to a range in one line, which is far cleaner than a nested IF.
Dates are numbers underneath, so =MIN(C2:C20) gives the earliest date and =MAX(C2:C20) the latest. Format the result cell as a date, or it will display as a five-digit serial number.
Home > Conditional Formatting > Top/Bottom Rules colours the highest or lowest values without any formula. For full control, use a formula rule of =D2=MAX($D$2:$D$20).
Count, min, max, average and median stacked in five cells beside a data set describes the whole distribution in about thirty seconds, and reveals problems, such as a minimum of zero, that a single number would hide.
Three routes, same result.
Home tab far right, or Formulas > AutoSum. Click the arrow rather than the button itself and choose Max or Min from the list.
Formulas > More Functions > Statistical holds MIN, MAX, MINA, MAXA, MINIFS, MAXIFS, LARGE and SMALL together in one alphabetical list.
Right-click the bar at the bottom of the Excel window and tick Minimum and Maximum. They then appear for every range you select, with no formula involved.
A cell holding 0 as a stand-in for missing data becomes the minimum on any all-positive column. Use =MINIFS(D2:D20,D2:D20,">0") to exclude zeros, or leave those cells genuinely blank.
Numbers imported as text are invisible to MAX, so it quietly returns the second-largest value with no error. Compare the status bar Count against your row count, and convert with Text to Columns if they differ.
Harmless with text headers, which are ignored, but a header like the year 2026 is a number and will be returned as the maximum. Start the range at the first data row.
Putting =MAX(D2:D20) inside D20 creates a circular reference and returns 0 once Excel gives up on it.
The maximum value alone rarely answers the question. Wrap it in XLOOKUP or INDEX/MATCH to return which row it belongs to, since that is what people actually want to know.
If two rows share the highest value, INDEX/MATCH and XLOOKUP return only the first one found. On data where ties matter, that silently hides a result.
They look like harmless variants but count text as zero, so MINA on any column containing a text entry returns 0. Stick to MIN and MAX unless you specifically need the other behaviour.
MIN and MAX on a date column return the correct date as a serial number like 46023. Format the cell as a date and it displays properly.
If you are writing a formula to find the worst number, something should have told you about it already.
Free 14-day trial. No credit card required.
Use =MAX(range), for example =MAX(D2:D20). It returns the largest number in that range, ignoring blanks, text and logical values.
Use =MIN(range), such as =MIN(D2:D20). The syntax matches MAX exactly and it returns the smallest number.
There is a real zero somewhere in the range, and zero is a valid value rather than a blank. Use MINIFS with a criteria of greater than 0 to exclude it.
Yes. Blanks, text and logical values are all skipped, so only genuine numbers are considered.
Wrap MAX in a lookup: =XLOOKUP(MAX(D2:D20),D2:D20,A2:A20) returns the label from column A. On older Excel use INDEX with MATCH instead.
Use =LARGE(range,2). LARGE with a 1 is the same as MAX, and SMALL works the same way from the bottom.
Use =MAXIFS(D2:D20,B2:B20,"Gold"). The value range comes first, followed by pairs of criteria range and criteria. It needs Excel 2019 or Microsoft 365.
MAX only looks at numbers. MAXA also counts text as 0 and logical values as 0 or 1, which usually gives a different and unhelpful answer.
Yes. Dates are stored as numbers, so MIN gives the earliest and MAX the latest. Format the result cell as a date or it shows a serial number.
Use =MIN(D2,1000) to cap at 1000, or =MAX(D2,0) to stop a value going below zero. Nest them to constrain a value between two bounds.
Yes, but they are off by default. Right-click the status bar, tick Minimum and Maximum, and they appear for any range you select.
Click the arrow beside AutoSum on the Home or Formulas tab and choose Max or Min. They are also under Formulas, More Functions, Statistical.
Every business starts with a spreadsheet. Updoot is where you scale past it, records link themselves automatically.
Start Your Free Trial →