conditional formatting

Question

hi,

Cell values are L1, L2, L3, L4, L5, L6 I want to fill color in these cells according to its ranking. SO that i get different color in different cells automatically. Can be this done with conditional formatting ? please let me some methods to achieve the same.

Please help.

Answer 1

Thanks for your replies. i dont have numbers in the range. The values are L1 , L2,.....Ln. (it is a text). basically i work for a procurement team and L1 indicates the lowest quote from a vendor so want to highlight in one particular color and same for L2,L3 ..etc.

Answer 2

Thanks for your replies. i dont have numbers in the range. The values are L1 , L2,.....Ln. (it is a text). basically i work for a procurement team and L1 indicates the lowest quote from a vendor so want to highlight in one particular color and same for L2,L3 ..etc.

Answer 3

OK so to format the cells dependant of their text value yoju will need to create a rule for each instance.

Select the range of cells to have the conditional formatting applied

from the ribbon from the Styles section of the Home tab

click Conditional Formatting > Highlight Cell Rules > Equal To...

A form will pop up, enter L1 in the text field, select an option from the drop down to the right.

Choose custom format, then select the fill tab... Click OK back to spreadsheet.

repeat as necessary for L2, L3...Ln

Hope this is what you were after.

---

Rik_UK Please mark the message or messages that answer your question as the "Answer" or vote if a reply has been helpful.

Answer 4

Hi,

select cells L1:L6, home tab, Conditional Formatting, New Rule, rule type: Use a formula to determine... , formula is: =RANK(L1,$L$1:$L$6)=1, choose a format, ok.

You must set 5 additional conditional formattings for the same range but with other format colors. The general formula is: =RANK(L1,$L$1:$L$6)=X, where X is the rank number (1 to 6).

Regards,

Frank

---

If this post answers your question, please mark it for all readers as the Answer.

Answer 5

sandeep_p_prabhu

First, I assume you will have numbers in the range L1:L6

select L1:L6

in the home tab, go to conditional formatting and click on it

Select  new rule

select   Use a formula to determine which cells to format

put in a formula like this

=RANK(L1,$L$1:$L$6,1)=1

then select a fill color.

Repeat that for the other ranks.

ex:  =RANK(L1,$L$1:$L$6,1)=2     for rank of 2

this sees the lowest number as rank 1.   use

=RANK(L1,$L$1:$L$6,0)=1

if you want the rank of 1 to be assigned to the highest number.

--

Regards,

Tom Ogilvy