# Conditional formatting to find

Folks,
I have a worksheet with a table of different numeric value. I am using conditional formatting with the Top/Bottom Rule choosing the top three. As I understand this the three largest values in a range will be found and shaded regardless of how many time they appear. From the image below I'm not getting the expected results
I've also attached the file
Conditional-formatting.xlsm
###### Who is Participating?

Commented:
I only did it that way so that you can see what happens with different values in the table but you can simply apply the conditional formatting formula to your data - see this version attached

That's your data with my suggested CF formula as before - see that all values that are 88, 90 or 99 are formatted - if you change values the formatting will change

regards, barry
Conditional-formattingV2.xlsm
0

IT ConsultantCommented:
Your conditional formatting should highlight TOP 3 (3 highest) values in your marked area - as I see from the printscreen 90, 90 and 99 are the highest values in your table
0

Author Commented:
I see the same thing, however, I'm led to believe that three largest values in a range will be found and shaded regardless of how many time they appear. I was expecting to see 99, 90, and 88
0

Author Commented:
I suspect I need the to incorporate the LARGE function but I'm guessing. If correct, how?
0

Commented:
Assuming your values in A2:E10 then you can apply conditional formatting using the "use a formula to determine which cells to format" option with this formula

=SUM(IF(FREQUENCY(IF(\$A\$2:\$E\$10>A2,\$A\$2:\$E\$10),\$A\$2:\$E\$10),1))<3

See attached example where I have entered random values in that range - press F9 to re-generate random values.

Top 3 values will be highlighted no matter how often they occur

regards, barry
conditional-formatting-top3.xlsx
0

Author Commented:
Close - Barry
Now your code re-writes the table. Let's make an assumption that a value in an existing table was incorrectly entered. When the error was correct so that it became a top 3 the table should remain except it would have an additional cell highlight. How is that coded, please?
0

Author Commented:
Barry,
That's what I needed - great job
0

Author Commented:
thanks and nice job
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.