How to Use MIN and MAX in Excel

Introduction

Your monthly electric bills are all over the place and you need the lowest and highest amounts for a budget range. MIN and MAX return the smallest and largest values in a range. Simple functions, but they come up constantly.

The Dataset

Here are monthly electric bills for a small office:

	A	B
3	Month	Bill
4	Jan	340
5	Feb	310
6	Mar	280
7	Apr	240
8	May	260
9	Jun	350
10	Jul	410
11	Aug	430
12	Sep	370
13	Oct	290
14	Nov	300
15	Dec	330

The office manager needs to set next year's monthly budget. Too low and they'll run over. Too high and they're locking up cash that could go elsewhere.

MIN

MIN returns the smallest value in a range. The syntax is:

=MIN(number1, [number2, ...])

• number1: A range or set of values.

=MIN(B4:B15)

The result is $240, April's bill. Spring is the cheapest stretch because neither heating nor cooling is running hard. That's the floor for budgeting, but planning around the best month is a good way to come up short.

MAX

MAX returns the largest value. The syntax is identical:

=MAX(number1, [number2, ...])

=MAX(B4:B15)

The result is $430, August's bill. If the budget can handle the worst month, every other month takes care of itself.

Measure	Formula	Result
Lowest	=MIN(B4:B15)	$240
Highest	=MAX(B4:B15)	$430
Spread	=MAX(B4:B15)-MIN(B4:B15)	$190

The $190 gap between the cheapest and most expensive months is the swing the budget has to cover. A flat monthly budget needs to land in that range, and closer to the top if you want a margin.

Key takeaway: MIN and MAX find the extremes. Subtracting one from the other gives you the range, which tells you how much your data swings from best case to worst case.

Multiple Ranges

MIN and MAX accept more than one range or argument:

=MIN(B4:B15, D4:D15)

If the office has two locations and the bills are in separate columns, this returns the single smallest value across both. Same logic applies to MAX.

You can also use them to cap or floor a single value. =MAX(B4, 300) returns whichever is larger: the bill or 300, so the result never drops below your minimum. =MIN(B4, 500) does the opposite, capping the result at 500.

What MIN and MAX Skip

MIN and MAX ignore text and empty cells. If someone typed "pending" instead of a number in one of the months, they'd skip right over it.

They don't ignore errors. If any cell contains #VALUE! or #REF!, the whole formula returns that error. Wrap it in IFERROR if bad data is a possibility:

=IFERROR(MIN(B4:B15), "Check data")

Conclusion

MIN and MAX give you the extremes. Subtract one from the other and you have the spread, which is often more useful than either number alone. For finding the 2nd or 3rd highest value instead of just the top, see our guide on LARGE, SMALL, and RANK. When you need the minimum or maximum based on a condition, check out our guide on MAXIFS and MINIFS.