How do I highlight the 2nd and 3rd highest values in a ROW in Excel?

ttt637

New Member
Joined
May 7, 2022
Messages
7
Office Version
  1. 365
Platform
  1. Windows
I know how to find the min and max, but for the life of me I need to conditionally format some data output for work and can't for the life of me find out how to make the 2nd highest value AMBER and the 3rd highest ORANGE.

Example
1651922699388.png
============ and I want to look like this====
1651922783152.png
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
What exactly did you try?
 
Upvote 0
I had already tried the google search result that was posted. Did not work.
 
Upvote 0
What was the formula that you tried?
 
Upvote 0
Assuming your data is in range K4:N5.
  • Highlight the range K4:N5
  • Go to Conditional Formatting
  • New Rule
  • For 2nd highest enter the formula =K4=LARGE($K4:$N4,2)
  • Choose your color and press OK
You will need to repeat the steps for the 3rd highest number changing the formula to: =K4=LARGE($K4:$N4,3)

1651945221553.png


Book1
KLMN
1
2
3
410203040
55040152
Sheet2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
K4:N5Expression=K4=LARGE($K4:$N4,2)textNO
K4:N5Expression=K4=LARGE($K4:$N4,3)textNO
 
Upvote 0
1st off thank you for the help and responses. I thought I could do this with the example above but I can't figure out the $'s and ":" separators.

Here is what I am looking at, I manualy filled in colors for demo, but since days are ranked differrent that nights was not sure how to seperate the cells in the formula


1652062568714.png
 
Upvote 0
Book3
ABCDEFGHI
1daynightdaynightdaynightdaynight
2DIG216181225233268270271215
3
4
5
6
7
8
9
10
11
12
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:I200Expression=AND(B2>0,AGGREGATE(14,6,$B2:$I2/($B$1:$I$1=B$1),4)=B2)textNO
B2:I200Expression=AND(B2>0,AGGREGATE(14,6,$B2:$I2/($B$1:$I$1=B$1),3)=B2)textNO
B2:I200Expression=AND(B2>0,AGGREGATE(14,6,$B2:$I2/($B$1:$I$1=B$1),2)=B2)textNO
B2:I200Expression=AND(B2>0,AGGREGATE(14,6,$B2:$I2/($B$1:$I$1=B$1),1)=B2)textNO


1652064418443.png
 
Upvote 0
WOW, you guys are SMART!! I would never in a millon years firgure this one out. I will give it a try when I get home. TY
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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
Back
Top