Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting
Hi! Is there a way (even a long way) to apply conditional formatting based on order, say: I want to paint bold blue the 5 highest values in a row? Thanks. -- Zubbus ------------------------------------------------------------------------ Zubbus's Profile: http://www.excelforum.com/member.php...o&userid=29875 View this thread: http://www.excelforum.com/showthread...hreadid=495792 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting
Select A1, then Format ConditionalFormatting
Set Condition 1 to Formula is.......=A1=LARGE(a:a,5), and set font to bold blue and get out Then select A1 and click on the FormatPainterBrush in the tool bar and sleect A2 and drag it down column A as far as you wish........ hth Vaya con Dios, Chuck, CABGx3 "Zubbus" wrote: Hi! Is there a way (even a long way) to apply conditional formatting based on order, say: I want to paint bold blue the 5 highest values in a row? Thanks. -- Zubbus ------------------------------------------------------------------------ Zubbus's Profile: http://www.excelforum.com/member.php...o&userid=29875 View this thread: http://www.excelforum.com/showthread...hreadid=495792 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting
Hi Zubbus! I'm not one of these Excel whizzes, but I played around with
this, and found a solution for you. I'm sure there are more "elegant" ones around, but this one does not involve macros or programming....and it's actually quite simple (don't let the length of this answer fool you - it's VERY simple). OK, here it goes:. FOR THIS EXAMPLE, ASSUME THE FOLLOWING: Row 5 is the row where you want the 5 highest values highlighted) Cells A1, B1, C1, D1, E1 is where we're going to "hide" five simple formulas that return the five highest values (you can write these anywhere on your worksheet, and lock them up) FORMULAS FOR RETURNING THE FIVE HIGHEST VALUES: In cell A1 write in the following formula: =LARGE(5:5,1) In cell B1 write in the following formula: =LARGE(5:5,2) In cell C1 write in the following formula: =LARGE(5:5,3) In cell D1 write in the following formula: =LARGE(5:5,4) In cell E1 write in the following formula: =LARGE(5:5,5) Each of the formulas above return the first highest value in row five, the first highest, the second highest, the third...etc. Make sure you protect these cells to ensure that these formulas are not accidentally erased or modified. NOW FOR THE CONDITIONAL FORMATTING: Select cell A5 Go to the Conditional Formatting menu, and select "formula is" in the first box In the formula space write the following: =OR(A5=$A$1,A5=$B$1,A5=$C$1,A5=$D$1,A5=$E$1) which means, "if this cell value is equal to any of the five largest values) IMPORTANT: make sure there are NO "$" in front of the A (we want to be able to copy this formatting to the whole row and have it adjust to each cell), conversely make sure there ARE "$" in front of the references to the cells that contain the formulas. NEXT: select the formatting you want if the cell contains any of the five highest values. EXTENDING THE CONDITIONAL FORMATTING TO THE WHOLE ROW: OK, pretty simple now.. just copy the formatting with the format painter, select the whole row, and paste! Any cell that matches the five highest values will now change to your chosen format, and will change according to changes in the data. Stilla "Zubbus" wrote: Hi! Is there a way (even a long way) to apply conditional formatting based on order, say: I want to paint bold blue the 5 highest values in a row? Thanks. -- Zubbus ------------------------------------------------------------------------ Zubbus's Profile: http://www.excelforum.com/member.php...o&userid=29875 View this thread: http://www.excelforum.com/showthread...hreadid=495792 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting when inserting a row | Excel Worksheet Functions | |||
Conditional Formatting Error | Excel Worksheet Functions | |||
difficulty with conditional formatting | Excel Discussion (Misc queries) | |||
Determine cells that drive conditional formatting? | Excel Discussion (Misc queries) | |||
Conditional formatting not available in Excel | Excel Discussion (Misc queries) |