Conditional formatting for Top 5 numbers (Excel 2003)

Topic

New Reply

Can I use conditional formatting to shade the top 5 values of a column bright green or does it have to be a macro? thanks for the help.

Reply | Quote

Replies

Let’s say that the values are in A1:A100.
Select this range.
Select Format | Conditional Formatting…
The first dropdown box should say “Cell Value Is” (the default setting).
The second dropdown box should say “greater than or equal to”.
Enter the following formula in the box next to it:

=LARGE($A$1:$A$100,5)

Click Format… and specify the formatting that you want to apply to the top 5.
Click OK twice.

Reply | Quote

ah, yes.. thanks very much

Reply | Quote

Would this have worked? If the column contained duplicate numbers, would one way be preferable to another?

Reply | Quote

That would have worked too. RANK and LARGE treat duplicate values the same way – if there are two cells with rank 5, both will be colored.

Reply | Quote

What if I don’t want to include 100% in the test. I tried =if(B5=”100%”,b5,large(b6:b21))

but it didn’t work

Reply | Quote

Let’s say again that the range is A1:A100.
Select A1:A100, A1 is the active cell within the selection.
Select Format | Conditional Formatting…
Select Formula Is from the first dropdown.
Enter the following formula in the box next to it:

=AND(A1>=LARGE($A$1:$A$100,5),A1100%))

Click Format… to specify the desired formatting.

Reply | Quote

Hi There

You could do it with a hidden column, let’s assume you have the numbers in cells A1:A7. In Cell B1 type =RANK(A1,$A$1:$A$7) and copy down to B7

Highlight A1:A7 and use the following formula is =B1<=5, choose your formatting.

I have attached an example of above

Reply | Quote