When analyzing a large dataset in Microsoft Excel, you often need to find the absolute extremes of your data—the highest sales figure in a quarter, the lowest temperature recorded in a month, or the cheapest product in an inventory list.
Instead of sorting the data and scrolling to the top or bottom, you can instantly extract these exact numbers using two of the simplest and most common functions in Excel: MIN and MAX.
How to Use the MAX Function
The MAX function scans a selected range of cells and returns the single highest numerical value it finds. It ignores text, empty cells, and logical values.
Syntax: =MAX(number1, [number2], ...)
Example: Find the Highest Sale
Imagine you have a list of daily sales totals spanning from cell B2 down to B100. You want to know the highest single day of sales for the entire period.
- Click on the empty cell where you want the highest number to appear (e.g., cell D2).
- Type the formula:
=MAX(B2:B100) - Press Enter.
Excel will instantly display the largest number from that column.
How to Use the MIN Function
The MIN function works exactly like the MAX function, but in reverse. It scans a selected range of cells and returns the single lowest numerical value.
Syntax: =MIN(number1, [number2], ...)
Example: Find the Lowest Expense
If you have a column of monthly expenses in range C2:C12 and want to find the cheapest month:
- Click on an empty cell.
- Type the formula:
=MIN(C2:C12) - Press Enter.
Excel will display the smallest number found within that specific range.
Using MIN and MAX Across Multiple Ranges
You are not restricted to selecting a single, continuous column of data. You can feed multiple different ranges or individual cells into the functions by separating them with a comma.
For example, if you want to find the highest number in column B and the highest number in column D, you would write:
=MAX(B2:B100, D2:D100)
You can even add raw numbers directly into the formula. =MIN(A1:A10, 50) will return the lowest number in the range A1:A10, unless all those numbers are higher than 50. If they are, the formula will simply return 50.
Handling Dates and Times
Because Excel stores dates and times as serial numbers behind the scenes, the MIN and MAX functions work perfectly on date columns.
- If you use =MAX() on a list of dates, it will return the most recent (latest) date.
- If you use =MIN() on a list of dates, it will return the oldest (earliest) date.
Just ensure the cell containing your formula is properly formatted as a “Date” so the result doesn’t look like a random five-digit number!