Conditional Formatting

[mjmoon]

I have a worksheet containing a list of departments (A1..A20), along with the number of OT hours (B1..B20) per month. In (C1..C20) I also have the department's percent of total OT hours calculated for that month.

What I would like to do is have the top five be conditional formatted to change their format to red so they stick out when the report is distributed, but I don't know how to do this. Any suggestions?

Thank you for your consideration.

Marc

 

[SteveO59L]

In an adjacent column, use the rank() function to rank the depts. Then use the "formula is" option in conditional formatting for ranked value <=5

 

[Thorin]

Highlight the area you wish to format (in this case B1..B20), and under conditional formatting, select formula is and copy this in :-

=OR(A1=LARGE($B$1:$B$20,1),A1=LARGE($B$1:$B$20,2),A1=LARGE($B$1:$B$20,3),A1=LARGE($B$1:$B$20,4),A1=LARGE($B$1:$B$20,5))

This will give the 5 highest in that range.