How to flag the highest value in a list in Excel
Formula (Excel and Google Sheets)
=IF(B2=MAX($B$2:$B$7),"Top store","")Compare each value with the maximum of the list: =IF(B2=MAX($B$2:$B$7),"Top store","").
- Result on the example
- one result per row in
C2, filled down toC7 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in C2, filled down to C7. Change any input and the result updates, or edit the formula to experiment.
How it works
MAX finds the highest sales in the locked range, and each row checks whether its own value equals it. Only the Mall matches. If you change any figure, the flag moves on its own.
=IF(B2=MAX($B$2:$B$7),"Top store","")Result""
B2$182,400The value in B2.
$B$2:$B$76 rows × 1 col: $182,400, $96,750, $210,300, $154,900, …A range: 6 cells in one column.
MAX($B$2:$B$7)210300MAX: Largest number.- number1
$B$2:$B$76 rows × 1 col: $182,400, $96,750, $210,300, $154,900, …
- number1
B2=MAX($B$2:$B$7)FALSEThe = operator tests whether the two sides are equal.
IF(B2=MAX($B$2:$B$7),"Top store","")""IF: Returns one value if a test is true, another if false.- logical_test
B2=MAX($B$2:$B$7)FALSE - value_if_true
"Top store"not used - value_if_false
""
- logical_test
In Google Sheets
The same formula works in Google Sheets: =IF(B2=MAX($B$2:$B$7),"Top store",""). We ran it in Google Sheets mode on the same data and it returns the same results.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =IF(RANK.EQ(B2,$B$2:$B$7)=1,"Top store","") | , , Top store… | RANK.EQ gives each store its position. Change =1 to <=3 to flag the top three. |
| =IF(B2>=LARGE($B$2:$B$7,3),"Top 3","") | Top 3, , Top 3…Different result | Flags every store at or above the third-highest value. Flags Mall, Lakeside and Downtown. |
Common mistakes
- Lock the range with $, or each row compares against a different slice of the list.
- Ties are all flagged, since each of them equals the maximum.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Flag the highest value in a list
C2, show "Top store" next to the highest sales in B2:B7 and leave the other rows blank. Fill down to C7.Questions
How do I flag the lowest value?
Use MIN: =IF(B2=MIN($B$2:$B$7),"Lowest","").
How do I return the name of the top store?
=XLOOKUP(MAX(B2:B7),B2:B7,A2:A7).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.