Conditional Formatting How to make the top 3?

I have my table set for scores, like this but it doesn't work quite like I want it to. I want the top three 100s and 90 and 97.


1 100
2 100
3 100
4 90
5 89
6 97
7 88
8 83


Thank you
G

=RANK(A7;$A$1:$A$8)<4
Try this formula in conditional format

It should be A1

Try this, assuming your data is in B1 down...

1. highlight the range you want to apply the conditional formatting to...B1:B8 in your sample data
2. on the home tab, styles, select CF
3. select new rule, select use formula
4. enter =B1>=LARGE($B$1:$B$8,3) format fill as required

Adjust ranges as needed

There is a coma not a ;

If you want the top 3, excluding duplicates, add a helper column (I uded C based on your data) and copy this down...
=IF(COUNTIF($B$1:B1,B1)>1,"",B1)

Then change my formula from post #7 to =C1>=LARGE($C$1:$C$8,3), still applied to B

My understanding is that in the sample you provided in post #1, you want all 3 100's plus 97 and 90 to be highlighted?

If that is correct, using my suggestion in post 9 will do that for you

Try this...

Assume your data is in the range A2:A9.

Enter this formula in B2 and copy down to B9. This will generate consecutive ranks for the data.

=SUMPRODUCT(COUNTIF(A2,"<"&A$2:A$9)/COUNTIF(A$2:A$9,A$2:A$9))+1

Then you can base the conditional formatting on B2:B9 being <=3.