How to do color formatting of cell in excel based on multiple conditions

Question

I have two columns. One column has the tier as Prime,essential,maintenance and Consumption. Other column has values in either % or as NA. I want to format all cells which are having tier as Prime and essential and value is >=90 as green else Red. Similarly if tier is maintenance or Consumption and value is >= 96 then green else Red .

How do i do it. Please help

Solution

1. Select the range that you will work with;

2. Click the Home > Conditional Formatting > New Rule….

3. In the Format Only Cells with section, specify the conditions that you need.

4. Click the Format button.

5. In the Format Cells dialog box, set the background color under Fill tab.

More details here

For your conditional formatting just select a column go to:

• conditional formatting>highlight cells rules>less than at the value you put 90 and at highlight you choose red

  then again:

• conditional formatting>highlight cells rules>greater than and at the value you put 89 and at color green

UPDATE:

For more conditions go to new rule and choose use a formula. The formula will look like this for the green color: =AND(A1="Prime",B1>90) then you do the same steps for the other conditions and colors.

Comments

[Admin]

Hi Thanks for your Response. However it would be great if you write the formula as well. I am little new to this formatting and knows only Basic rules :(

[Alexandru Cimpanu]

@Ratna No problem, it was useful for me two. Please accept the answer to close the question.

[Alexandru Cimpanu]

@Ratna now it would be more easy for you.

[Admin]

But what about the Tier condition as Prime Essential n etc.. ?

[Alexandru Cimpanu]

can you put a sketch of the table?

[Alexandru Cimpanu]

I have updated the response, this should be enough for you to continue.