Conditional Formatting

mjmoon

Board Regular
Joined
Mar 1, 2005
Messages
249
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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

SteveO59L

Well-known Member
Joined
Apr 21, 2004
Messages
7,899
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

Board Regular
Joined
Feb 8, 2007
Messages
243
Highlight the area you wish to format (in this case B1..B20), and under conditional formatting, select formula is and copy this in :-

Code:
=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.
 

Forum statistics

Threads
1,181,410
Messages
5,929,774
Members
436,688
Latest member
sunnyBNH013

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top