Highlighting the top three finishers in different colors (gold, silver, bronze?)

[JenniferMurphy]

I am aware of the Conditional Formatting option that will highlight the top N values in a range.

Is there an easy way to highlight each of the top 3 in a different color? Can I set the background fill to gold, silver, and bronze for 1st, 2nd, and 3rd?

I tried creating three rules, one for the top three, one for the top 2, and one for the top 1. It sorta worked, but the colors kinda merged.

 

[JenniferMurphy]

Earlier in this thread, I posted an outline showing how to make conditional formatting rules work. There was an error in step 1. Here are the corrected steps (I believe):

1. Use absolute references (with the $ signs: $B$3:$C$25) in the Applies to section.
2. Use absolute references almost everywhere in the rules section. The exception is tha target cell. In my case, I was trying to highlight the 3 top (largest) values in a column (B3:B5). Using the RANK function, I coded the rule for top (largest) value as

   =rank($B3,$B$3:$B$5,0)=1

   . Note that in the target cell ($B3), the colmn is absolute, but the row isn't. This is because I want the target cell to float over the range (column).
3. Select the entire range that the rules will apply to, if possible. It's way better to have one set of rules for the entire range, than separate sets for each column.
4. If the entire range cannot be selected all at once, select what you can, but then make sure to manually add the other pieces in the Applies to section. Separate non-contiguous sections with commas ($B$3:$B$25,$D$3:$F$25).
5. Make sure to arrange the cells in order, if that matters.

n.b.: Using the format painter is very dangerous if there are conditional formatting (CF) rules. Any CF rules in the source cells will be added to any that are already there -- even if they are the same. I once found as many as 15 copies of the same or similar rules.

 

[VBABEGINER]

Thanks for sharing the answer...This helps me.

Highlight the cells ( in the example B2:B15).
In the conditional formatting go to highlight cell rules and select equal to.
Then put the following formulas(from the =sign and adjust ranges to suit) in the box (obviously a separate formula & rule for each color).
You don't say much about what your data looks like (that's a little bit important) and so you might need to change the LARGE function to SMALL if your data goes 1,2,3 etc.
Sheet2

*	B
1	VALUE
2	141
3	104
4	110
5	83
6	102
7	66
8	66
9	100
10	147
11	66
12	144
13	68
14	64
15	102

<tbody>
</tbody>

Conditional formatting

Cell Nr.: / Condition Format B2 1. / Cell Value equal to =LARGE($B$2:$B$15,3) Abc B2 2. / Cell Value equal to =LARGE($B$2:$B$15,2) Abc B2 3. / Cell Value equal to =LARGE($B$2:$B$15,1) Abc <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4