Whenever I’m reviewing a set of numbers — sales figures, test scores, expenses — one of the first things I want to know is the highest and lowest values. Early on, I used to sort the entire dataset just to find these two numbers, which was clunky and risked accidentally rearranging data I didn’t want touched. MAX and MIN solved that problem instantly, letting me pull out the extremes without disturbing anything else in the sheet.
Let’s go through exactly how these two functions work, together and separately.
What Are the MAX and MIN Functions in Excel?
MAX returns the largest number in a given set of values, while MIN returns the smallest. Both functions scan through a range or list of numbers and instantly identify the extreme values, without you needing to sort, filter, or manually scan the data yourself.
They’re often used together to understand the range of a dataset — for instance, finding both the highest and lowest sales figures in a month to understand performance variability.
Required Data
To use MAX or MIN, you need:
- A range of numeric values, or individual numbers typed directly into the formula
- Optionally, multiple ranges combined together
Text and blank cells within the range are ignored automatically, similar to how AVERAGE handles them.
Basic Syntax
=MAX(number1, [number2], ...)
=MIN(number1, [number2], ...)
- number1 is required — your first number or range.
- number2 and beyond are optional additional numbers or ranges.
Step-by-Step Instructions
- Click the cell where you want the result to appear.
- Type
=MAX(or=MIN(depending on which value you need. - Select the range of cells containing your numbers.
- Close the parenthesis and press Enter.
Example:
=MAX(B2:B20)
=MIN(B2:B20)
The first formula returns the highest number in B2:B20, and the second returns the lowest.
Practical Examples
Example 1: Finding the Highest Sale of the Month
=MAX(C2:C31)
This scans a month’s worth of daily sales figures and returns the single highest value.
Example 2: Finding the Lowest Test Score
=MIN(D2:D30)
This returns the lowest score among a class of students, useful for identifying who might need extra support.
Example 3: Combining MAX and MIN to Find the Range
=MAX(C2:C31) - MIN(C2:C31)
Subtracting the minimum from the maximum gives you the spread, or range, of your data — a quick way to gauge variability.
Example 4: MAXIFS for Conditional Maximums
If you want the highest sale specifically within the “North” region:
=MAXIFS(C2:C50, A2:A50, "North")
Example 5: MINIFS for Conditional Minimums
Similarly, to find the lowest price for a specific product category:
=MINIFS(D2:D50, B2:B50, "Electronics")
Example 6: Using MAX to Highlight Top Performers
=IF(C2=MAX($C$2:$C$50), "Top Performer", "")
This formula checks whether the value in C2 matches the maximum in the entire range, and if so, labels it — handy combined with conditional formatting for quick visual identification.
Common Mistakes to Avoid
- Confusing MAX/MIN with LARGE/SMALL — MAX and MIN only return the single highest or lowest value. If you need the second-highest or third-lowest value, you need the LARGE or SMALL functions instead, like
=LARGE(C2:C50, 2)for the second-highest value. - Including header rows in the range — Accidentally including text headers usually isn’t a problem since MAX/MIN ignore text, but including totals or subtotal rows can badly skew your result.
- Using MAX/MIN when you need conditional logic — Plain MAX and MIN don’t filter by any category; if you need the highest value within a specific group, you need MAXIFS or MINIFS.
- Assuming MAX/MIN work on dates and times the same as numbers — They actually do work correctly on dates and times since Excel stores these as serial numbers under the hood, but the result needs to be formatted as a date or time afterward to display properly, otherwise you’ll just see a number.
- Locking ranges incorrectly in formulas that will be copied — If you’re copying a MAX or MIN formula across multiple rows or columns, forgetting to use absolute references (with $ signs) can cause the range to shift unexpectedly.
Troubleshooting Tips
- Result shows 0 unexpectedly: This can happen if your range is entirely blank or contains only text; MAX and MIN will return 0 in that case rather than an error.
- MAX result looks like a serial number instead of a date: Format the result cell as a Date if you’re finding the maximum or minimum among a set of date values.
- MAXIFS or MINIFS returns an error: These functions require Excel 2019 or later (or Microsoft 365); in older versions, you’d need an array formula alternative using MAX combined with IF.
- Result doesn’t match what you expected visually: Double-check for hidden rows or filtered data — MAX and MIN include hidden rows by default unless you use SUBTOTAL with function number 104 (MAX) or 105 (MIN) to only evaluate visible cells.
Real-World Use Cases
- Sales managers identifying the best and worst performing days, products, or representatives.
- Teachers finding the highest and lowest scores in a class to understand grade distribution.
- Finance teams tracking the highest expense in a category to flag unusual spending.
- Weather and environmental data analysis finding peak and lowest recorded temperatures.
- Project managers identifying the longest and shortest task durations in a schedule.
- Retail analysts finding the highest and lowest priced items in inventory.
Best Practices
- Combine MAX and MIN with conditional formatting to visually flag the highest and lowest values directly in your dataset without needing a separate formula cell.
- Use MAXIFS and MINIFS whenever you need the extreme value within a specific category rather than across your entire dataset.
- When copying formulas across a sheet, lock your range references with $ signs to prevent them from shifting incorrectly.
- Pair MAX/MIN with LARGE/SMALL when you need more than just the single top or bottom value, such as the top 3 performers.
- Always double check your selected range excludes summary rows like totals, which can distort your results without any visible error.
Frequently Asked Questions
What happens if MAX or MIN is applied to an empty range? Both functions return 0 when the range contains no numeric values at all, rather than producing an error. This can occasionally be misleading, since a true “0” and an “empty range” look identical in the result.
Can MAX or MIN ignore hidden or filtered rows? Not by default — both functions evaluate every cell in the specified range, whether it’s visible or hidden. To only evaluate visible cells, use SUBTOTAL with function number 104 for maximum or 105 for minimum, such as =SUBTOTAL(104, C2:C50).
How do I find the second-highest or second-lowest value? Use the LARGE function for the second, third, or nth highest value, like =LARGE(C2:C50, 2), or the SMALL function for the equivalent from the bottom, like =SMALL(C2:C50, 2).
Do MAX and MIN work with text values? No, both functions ignore text entirely and only evaluate numeric values, including dates and times since those are stored as numbers internally.
What’s the difference between MAXIFS and using MAX with an array formula? MAXIFS, available in modern Excel versions, is a direct, easy-to-read way to apply conditions to a maximum calculation. Older versions without MAXIFS require an array formula like {=MAX(IF(A2:A50="North", C2:C50))} entered with Ctrl+Shift+Enter, which accomplishes the same result but is harder to read and maintain.
Final Thoughts
MAX and MIN are two of the simplest functions in Excel, but they’re also some of the most immediately useful. They let you instantly understand the boundaries of your data without sorting, scrolling, or manual comparison. Once you’re comfortable with the basics, expanding into MAXIFS, MINIFS, LARGE, and SMALL gives you a much more complete toolkit for understanding not just the extremes of your data, but the full shape of it.
