Excel guide · 3 min read

How to find the highest and lowest value in Excel

Q: How do I find the max value in a row instead of a column?

Same function: =MAX(B2:H2). MAX, MIN, LARGE and SMALL work on rows, columns or any block of cells.

Q: Can MAX look across several ranges?

Yes. Separate them with commas, for example =MAX(C2:C8,F2:F8). The same works for MIN.

Q: How do I find the latest or earliest date?

Dates are numbers in Excel, so =MAX(range) gives the latest date and =MIN(range) the earliest. Format the result cell as a date.

Quick answerUse =MAX(C2:C8) for the highest value and =MIN(C2:C8) for the lowest. To get the name next to that value, use =INDEX(A2:A8,MATCH(MAX(C2:C8),C2:C8,0)), or XLOOKUP in Excel 365. For the second or third highest use LARGE(range,2) or LARGE(range,3), and for the max or min within a category use MAXIFS or MINIFS (Excel 2019 and later).

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

	A	B	C	D
1	Product	Category	In stock	Price
2	Desk lamp	Lighting	140	34.99
3	Floor lamp	Lighting	32	89
4	Office chair	Seating	18	249
5	Bar stool	Seating	65	59.5
6	Bookshelf	Storage	22	129
7	Filing cabinet	Storage	0	179
8	Pendant light	Lighting	47	72

MAX and MIN

This inventory lists seven products. MAX returns the largest number in the range and MIN the smallest. Both ignore empty cells and text, so a header or a note in the range does no harm.

Watch the zero: the filing cabinet is out of stock, so =MIN(C2:C8) returns 0. That is correct, but it may not be what you were asking. The section on ignoring zeros covers that.

=MAX(C2:C8)

Result: 140

140: the desk lamp has the most stock.

Return the name, not just the number

The number alone rarely answers the question; you want to know which product. MATCH finds the row of the max value and INDEX returns the product name from that row. This works in every version of Excel.

In Excel 365 or 2021, =XLOOKUP(MAX(D2:D8),D2:D8,A2:A8) is shorter and does the same. If two products tie, both methods return the first one in the list.

=INDEX(A2:A8,MATCH(MAX(D2:D8),D2:D8,0))

Result: Office chair

Office chair, the most expensive item at 249.

Second, third, nth: LARGE and SMALL

LARGE(range, k) returns the k-th largest value, so LARGE(range,1) is the same as MAX. SMALL(range, k) does the same from the bottom. They are the building blocks of a top 3 or bottom 3 list.

Put 1, 2, 3 in helper cells and point k at them to build a small leaderboard that updates itself.

=LARGE(D2:D8,2)

Result: 179

179: the filing cabinet is the second most expensive.

Highest or lowest by condition

For the most expensive Lighting product, Excel 2019 and later have MAXIFS: =MAXIFS(D2:D8,B2:B8,"Lighting"), which returns 89. MINIFS works the same way for the lowest. Like SUMIFS, the range you want the answer from comes first.

In Excel 365 you can also filter first and then take the max. In Excel 2016 and older, which have neither, enter =MAX(IF(B2:B8="Lighting",D2:D8)) with Ctrl+Shift+Enter.

=MAX(FILTER(D2:D8,B2:B8="Lighting"))

Result: 89

89: the floor lamp is the priciest Lighting item.

Lowest value ignoring zeros

To find the lowest stock among items that are still in stock, leave out the zeros. MINIFS does it in Excel 2019 and later: =MINIFS(C2:C8,C2:C8,">0"). In Excel 365, filter the zeros out first.

Ignoring blanks needs nothing extra, because MIN already skips empty cells. Only real zeros need this treatment.

=MIN(FILTER(C2:C8,C2:C8>0))

Result: 18

18: office chairs are the lowest item still in stock.

Highlight the highest and lowest

If you only want to see the extremes, conditional formatting marks them without a formula and updates as the data changes.

Select C2:C8.
Go to Home > Conditional Formatting > Top/Bottom Rules > Top 10 Items.
Change 10 to 1, choose a format, and click OK.
Repeat with Bottom 10 Items set to 1 for the lowest value.

Common mistakes

MAX returns 0 on a column of numbers

The numbers are stored as text, which MAX ignores. Check with =COUNT(range); if it returns 0, convert the column with Data > Text to Columns > Finish.

MIN returning 0 you did not expect

Zeros count, blanks do not. Use MINIFS(range,range,">0") or MIN(FILTER(range,range>0)) to skip zeros.

Getting #NAME? from MAXIFS

MAXIFS and MINIFS do not exist in Excel 2016 or older. Use the MAX(IF(...)) array formula there, confirmed with Ctrl+Shift+Enter.

Ties hiding a second winner

INDEX MATCH and XLOOKUP return only the first match. In Excel 365, =FILTER(A2:A8,C2:C8=MAX(C2:C8)) lists every product tied for the top.

FAQ

How do I find the max value in a row instead of a column?

Same function: =MAX(B2:H2). MAX, MIN, LARGE and SMALL work on rows, columns or any block of cells.

Can MAX look across several ranges?

Yes. Separate them with commas, for example =MAX(C2:C8,F2:F8). The same works for MIN.

How do I find the latest or earliest date?

Dates are numbers in Excel, so =MAX(range) gives the latest date and =MIN(range) the earliest. Format the result cell as a date.

What does MAX return if the range is empty?

0. If that could be confused with a real result, check COUNT(range) first, for example =IF(COUNT(C2:C8)=0,"",MAX(C2:C8)).

Functions used

Related guides

Updated November 2, 2026