Excel Formula for High and Low Values: MIN and MAX

In Excel, use =MIN(B2:B10) to find the lowest number in a range and =MAX(B2:B10) to find the highest. Replace B2:B10 with your own cell range; both formulas return a single value and update when the numbers change.

What MIN and MAX do

MIN and MAX are the standard Excel functions for finding the low and high ends of a set of numbers. MIN returns the smallest numeric value it finds; MAX returns the largest. They are useful for checking sales, temperatures, scores, dates, inventory counts, and many other numeric lists. You do not need to sort the data first.

For example, suppose B2:B7 contains 125, 98, 143, 98, 110, and 160. The lowest value is 98 and the highest is 160. In formulas, a cell range is written with a colon between its first and last cell. The dollar signs are optional for a one-time calculation, but can keep a range fixed when you copy a formula.

Basic formulas and a worked example

With the six example values in B2:B7, enter the minimum formula in any empty cell:

With the example numbers in B2:B7, this formula returns 98: =MIN(B2:B7)

With the same numbers in B2:B7, this formula returns 160: =MAX(B2:B7)

Excel formulas can refer to cells outside the data column, so you might put the low result in D2 and the high result in D3, for example. If you add another number below the range, update the reference to include it, or convert the data to an Excel table and use a structured reference. A table’s range expands when you add rows, which can make summary formulas easier to maintain.

If the values are in separate areas rather than one continuous block, list each range as an argument. With numbers in B2:B7 and D2:D7, the minimum across both areas is calculated with =MIN(B2:B7,D2:D7), and the maximum with =MAX(B2:B7,D2:D7). The comma separates arguments in standard English-language Excel settings. Some regional settings use a semicolon as the formula argument separator.

Find the spread between the high and low

Sometimes the useful result is not either endpoint, but the distance between them. Subtract the minimum from the maximum to get the range, also called the spread in many everyday contexts. This is a simple measure of how far apart the two extremes are; it does not describe how values are distributed between them.

With values 125, 98, 143, 98, 110, and 160 in B2:B7, this formula returns 62: =MAX(B2:B7)-MIN(B2:B7). The arithmetic is 160 minus 98, which equals 62. A large spread can signal a wide variation, but it does not by itself explain why the values differ or whether any particular value is unusual.

For dates stored as real Excel dates, MIN and MAX can find the earliest and latest date because Excel stores dates as serial numbers. Format the result cell as a date if Excel displays a number instead. This only works as expected when the entries are actual date values, not text that merely looks like a date.

Find high and low values that meet a condition

To find an extreme within a category, use MAXIFS or MINIFS. These functions apply a condition to one range and return the highest or lowest value from a corresponding range. They are available in Microsoft 365 and Excel 2019 or later; older Excel versions may not recognize them.

Suppose categories are in A2:A7 and sales amounts are in B2:B7. The rows are East 125, West 98, East 143, North 98, West 110, and East 160. With categories in A2:A7 and sales in B2:B7, this formula returns 160, the highest East sale: =MAXIFS(B2:B7,A2:A7,"East")

With the same category and sales layout, this formula returns 125, the lowest East sale: =MINIFS(B2:B7,A2:A7,"East"). The first argument is the range of values to evaluate; the following range-and-criteria pair identifies which rows qualify. Text criteria need quotation marks. A criterion can also refer to a cell, such as a cell containing the category name, which is useful when users need to change the category without editing the formula.

MINIFS and MAXIFS can accept more than one condition. For instance, a report might look for the highest sale in East during a specified period, using a category range and a date range as separate criteria ranges. Each criteria range must have the same dimensions as the values range. If no cells meet the criteria, these functions return 0, so check whether zero is a possible genuine result before treating it as evidence of a match.

Calculate extremes in a filtered list

MIN and MAX normally examine all the values in their referenced range, including rows hidden by a filter. If you want an extreme from only the rows currently shown after filtering, use SUBTOTAL instead. Its function number controls which calculation it performs: 5 means MIN and 4 means MAX. These versions exclude rows hidden by an AutoFilter.

With the example values in B2:B7 and some rows hidden by a filter, this formula returns the smallest value in the visible rows: =SUBTOTAL(5,B2:B7). With the same layout and filter, this formula returns the largest visible value: =SUBTOTAL(4,B2:B7). If the visible rows are the two West entries, 98 and 110, the results are 98 and 110.

There is an important distinction between filtering rows and hiding them manually. The function numbers 5 and 4 include manually hidden rows, while 105 and 104 exclude both filtered-out and manually hidden rows. For example, with values in B2:B7, use =SUBTOTAL(105,B2:B7) for the minimum excluding manually hidden rows as well. SUBTOTAL also ignores other SUBTOTAL results within its reference, which helps avoid double-counting in subtotaled lists.

Show the high and low values in the data

A formula can return the extreme value, but sometimes you want Excel to visually mark every cell containing it. Conditional formatting can do this without changing the data. Select B2:B7, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Set the format you want and create a rule for the minimum.

With the selected cells B2:B7 and the first selected cell at B2, use this rule formula to highlight every cell tied for the minimum: =B2=MIN($B$2:$B$7). Create a second rule for the maximum with =B2=MAX($B$2:$B$7). The B2 reference is relative so Excel checks each cell in the selection; the dollar signs keep the full comparison range fixed. If the data range changes, adjust the rule’s range and formula accordingly.

Repeated values are not an error. In the example list, 98 appears twice, so a minimum-highlighting rule marks both cells. MIN and MAX return the value, not the address of a cell, and do not select just one occurrence when there is a tie. If you need to identify which row contains an extreme, use a separate lookup approach; decide how ties should be handled before choosing one row to display.

Common problems and how to fix them

When a result looks wrong, first check the referenced range and the underlying cell values. MIN and MAX ignore blank cells and text in referenced cells, but a numeric value stored as text is not reliably treated as a number by these range calculations. Convert such entries to real numbers if they should count. A zero is a number, not a blank, so it can become the minimum even when it represents a missing measurement in your process.

Unexpected zero: Check for actual zero values, formulas returning zero, or a MINIFS calculation with no matches. Do not replace zero with blank unless that matches what the data means.
Text that looks numeric: Look for numbers imported as text, often indicated by a warning marker or inconsistent alignment. Use Excel’s conversion options or correct the source data.
An error result: Check cells in the referenced range for errors such as #N/A or #VALUE!. MIN and MAX do not silently repair error values in the range.
Wrong filtered result: Use SUBTOTAL rather than MIN or MAX when the calculation should respond to a filter. Confirm whether manually hidden rows should count.
Formula not accepted: Check that the range addresses are valid and that your regional Excel settings use the right argument separator.

Also consider whether the data contains outliers. A single unusually large or small number determines MAX or MIN, but that does not establish that the entry is a mistake. Verify the source before changing or excluding it. If the goal is to describe typical values, the median or another summary may be more informative than the extremes.

Choosing the right Excel method

Use the simplest method that matches the question. MIN and MAX are right for the smallest and largest values across a full range. MINIFS and MAXIFS are appropriate when values must satisfy criteria. SUBTOTAL is the practical choice when the answer should update with filtered visibility. Conditional formatting is useful when the goal is to spot the extreme cells, while a subtraction of MAX and MIN gives the overall spread.

In Microsoft 365 for Windows, the formulas and Conditional Formatting path described here work as written. The formulas are also available in Excel for Mac and Excel for the web, though ribbon placement can vary slightly by platform and updates. On a Mac, use the Home tab’s Conditional Formatting controls to create the rule. In localized Excel installations, function names and separators can differ from the English examples.

Frequently Asked Questions

What is the formula for the highest value in Excel?
Use MAX with the range you want to check. For values in B2:B10, enter =MAX(B2:B10).

What formula finds the lowest number in Excel?
Use MIN. For values in B2:B10, enter =MIN(B2:B10). It returns the lowest numeric value in that range.

Do MIN and MAX include blank cells?
No. Blank cells in a referenced range do not affect the result. Numeric zeroes do count, and text entries are not treated as numeric values in the range.

How do I find the highest value after filtering?
Use =SUBTOTAL(4,B2:B10) for the largest value among filtered visible rows. Use function number 104 instead of 4 if manually hidden rows should also be excluded.

Can Excel highlight every tied high or low value?
Yes. Apply Conditional Formatting rules using a comparison with MAX or MIN. Because the rule checks each cell, all cells matching the extreme value can be highlighted, including ties.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.