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.

 

[MARK858]

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

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:61px;"></colgroup><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

 

[ChrisR]

Hi,

You can do this with conditional formatting.

You need to enter three rules. Enter these three rules in one cell and then you can just copy and paste the formatting over the range.

Rule 1

=RANK(L15,$L$15:$L$21)=1

Change L15, to the cell reference you are checking (I.e. you first number) . Change L15:L21 to your list of numbers.

Change the formatting to Gold.

=RANK(L15,$L$15:$L$21)=2 (Formatting Silver)

=RANK(L15,$L$15:$L$21)=3 (Formatting Bronze)

 

[MARK858]

Just in case I wasn't clear over the use of SMALL...
But you might get an issue with draws (See Column C)

Excel Workbook
	B	C
2	9	9
3	7	6
4	5	5
5	3	2
6	1	1
7	8	8
8	2	2
9	4	4
10	6	3
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2	1. / Cell Value equal to =SMALL($B$2:$B$15,3)	Abc
B2	2. / Cell Value equal to =SMALL($B$2:$B$15,2)	Abc
B2	3. / Cell Value equal to =SMALL($B$2:$B$15,1)	Abc

 

[JenniferMurphy]

My data is in Y8:Y16. It currently looks like this:

[TABLE="width: 66"]
<tbody>[TR]
[TD]47.6[/TD]
[/TR]
[TR]
[TD]52.9[/TD]
[/TR]
[TR]
[TD]60.0[/TD]
[/TR]
[TR]
[TD]43.7[/TD]
[/TR]
[TR]
[TD]50.8[/TD]
[/TR]
[TR]
[TD]43.7[/TD]
[/TR]
[TR]
[TD]44.5[/TD]
[/TR]
[TR]
[TD]37.5[/TD]
[/TR]
[TR]
[TD]32.1[/TD]
[/TR]
</tbody>[/TABLE]

On my first try, I got the same results with both of your methods (Rank and Large). I started with one rule. It highlighted the entire range. I tried it several times. Then I noticed that in the Rank example, the target cell is relative (no $ signs). I tried it again removing the $ signs and it seems to be working.

I could not get the Large method to work.

 

[JenniferMurphy]

Mark858,

I can't get large or small to work. I'm not too concerned, because I got Rank to work.

Duplicates should not be a problem. If there are two second largest values, the next largest value will be the 4th.

 

[ChrisR]

Yes, the target cell does not have $ signs, otherwise the Rank formula won't work.

 

[JenniferMurphy]

You need to enter three rules. Enter these three rules in one cell and then you can just copy and paste the formatting over the range.

ChrisR,

I need to do this in 7-8 columns of data. I wold love to be able to set it up once and copy the formatting to the other columns. The problem is the other columns are not all formatted the same. Some have a background, they use different font sizes, and there are different border situations.

Is there any way to copy just the conditional formatting?

 

[ChrisR]

Is each column identical cell wise, but just different to other columns, or are there variations of formatting within the column?

 

[ChrisR]

Just to add to the above........... I don't think it's possible to preserve the formatting on the cells when pasting conditional formatting, not without VBA anyway........

It may be easier to just copy and paste the conditional formatting across (Watch your $ references, as they will need to move to the new column (Unless of course you are looking for the top 3 out of all the columns)

Then once this is done, just reformat the columns manually..............It's difficult for me to know if this is suitable, however, as I have no idea what/why you have different formats and the necessity of these..........