Excel - find the two highest figures and highlight them in different colours

Sorry you need to add the $ signs in front of the column values as well ie. =LARGE($A$1:G$1,1)
if you want it to work for the rows (the first example is for columns)

You will need to set the rule for each row though.

After testing you can set the conditional formatting for the entire cell array you want to by using only 1 rule.

IE. if your data goes from A1 to D4

the highest in each row can be highlighted by setting the rule equals to:
=LARGE($A1:$D4,1)
and the second highest
=LARGE($A1:$D4,2)

if you want the highest in each column
=LARGE(A$1:D$4,1)
and 2nd
=LARGE(A$1:D$4,1)

pfff i am really not awake yet sorry. this only gets the ones in the first and last row.

or try this:

assuming values are in B3:B30
place in B1 the formula For Highest: =LARGE(B3:B30,1) where B1 till H1 is the range where to look
place in B2 the formulaFor second Highest: =LARGE(B3:B30,2) where B1 till H1 is the range where to look
then do conditional formating Format>>Conditional formating
select here the option formula and for cell B3 you enter the next value =B3=$B$1 and select the format color or fontype as you like. this will make the highest  number visible

select here the option formula and for cell B3 you enter the next value =B3=$B$2 and select the format color or fontype as you like. this makes the second highest value visible

copie these format over the other cells of the range B43:B30 using pastespecial>>format

regards,

Jeroen

This has the same approach as the above but doesn't use any calculation cells and uses only one conditional formula. Not exactly what you wanted for this one but I thought it worthwhile for future reference as you can colour a range of values with this approach

If the data was in B2:AK2 then

Formula Is  = SUM(IF(D2=LARGE($B$2:$AK$2,ROW(INDIRECT("1:2"))),1,0))

will highlight any cells equal to the largest two values.
Change the "1:2" part to change the number of large values searched for

A detailed explanation of this method http://oldlook.experts-exchange.com:8080/Applications/MS_Office/Q_20646066.htm

Cheers
Dave

I respectfully suggest the question is now closed & points/grading awarded accordingly.

Thank you.

BFN,

fp.

No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

SPLIT: roos01 {http:#10503659}, crxmail {http:#10503764}

Please leave any comments here within the next four days.

MalicUK
EE Cleanup Volunteer

Hi MalicUK,

Was my suggestion (03/03/2004 12:17PM GMT) not considered suitable?

BFN,

fp.

Hey fp,

I agree that your solution works, but the first two would be the best solutions to this problem, as well as the formula being alot simpler. crxmail suggested conditional formatting first. I'm always willing to listen to other points of view though.

MUK

Hello MUK,
Looking to fp coment using the INDIRECT option I think this is also an approach to this solution and by accepting also fp answer this answer will also pointed as acceptable answer. So they have 3 option to choose at.
(Just my opinion)

regards,
Jeroen

roos01,

No problem mate. Like I said I'm always happy to reconsider if necessary.

Admin, please change my recommendation for this Q to:

SPLIT: roos01 {http:#10503659}, crxmail {http:#10503764}, fanpages {http:#10503813}

Cheers,
MalicUK
EE Cleanup Volunteer.