How can I use conditional formatting to highlight 3 highest (or lowest) values within a range of values?
How can I use conditional formatting to highlight 3 highest (or lowest) values within a range of values?
Last edited by mcmuney; 05-23-2011 at 06:38 PM.
Use the Large Function 3 times, maybe. Or the Small Function?
Highest? Lowest? Range of values? What version of Excel? Mac or Windows?
Do you not think it would be helpful to provide a little more information and/or a sample workbook?
Regards
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Try using for Cells that contain - greater than or equal to - =LARGE($A$1:$A$10,3)
for the 3 largest values. (modify the range in the formula)
The large formula would work, but only if I wanted to show the number in a different cell. But I want the actual cells highlighted. Here's an example (I'm using Excel 2007):
In this example, I'd want the 3 lowest values highlighted (cells a1, a2 and a6 should be highlighted as a result)
a1 -5
a2 -1
a3 3
a4 0
a5 2
a6 -7
Please post a sample workbook indicating the cells with the values and the cells that need to be highlighted.
Regards
See attached sample. Please note that the highlights need to happen on the actual values and not in a different using the LARGE formula. Thanks.
The condition is:
=OR($C2=SMALL($C$2:$C$12,1),$C2=SMALL($C$2:$C$12,2),$C2=SMALL($C$2:$C$12,3))
Much easier when you can see it ;-)
Regards
Last edited by TMS; 05-23-2011 at 05:23 PM.
Hello Mcmuney.
Its very easy to do it in Conditiona Formatting.here is what you do;
at home ribbon>con.Form.>Top/Bottom Rules>Top 10 or Bottom 10 then change 10 to req.number in your case "3" choose which colour then click OK.Done.
I hope this solve your problem.see attachment.
Sem.
@sem: I've just got to try out all these different options! ;-)
Wow! Multiple solutions.
Thanks guys!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks