How to use conditional formatting with IF function in Microsoft Excel

In this article we will learn how to color rows based on text criteria we use the “Conditional Formatting” option. This option is available in the “Home Tab” in the “Styles” group in Microsoft Excel.

Conditional Formatting: -Conditional Formatting is used to highlight the important points with color in a report.

To use If Conditional Formatting formula in Excel , we use the IF condition in the data and then define the conditional formatting options.

IF: -IF condition allows us to use multiple conditions in a cell. It makes the function flexible. Excel allows upto a maximum of 64 conditions which can be checked in a cell.

 

Syntax of “IF” function: =if (logical test, [value_if_true], [value_if_false])

 

Example:Cells A2 and A3 contain the numbers 3 and 5 respectively. The condition is – if the number in the cell is 3, the formula should display “Yes,” otherwise “No”. =IF (A1=3,”Yes”,”No”)

 

img1

 

As per the conditions specified, we get Yes in B1 and No in B2.

Let’s take an example and understand how to format the conditional If statement in Excel.

We have scores in the range A2:B12, in which column A contains the Students Name and column B contains the scores. In column C we want to return the grades according to the scores. And the grade criteria is – if the score is less than and equal to 50 then the grade should be “C”, if the score is greater than 50 and less than 75 then the grade would be “B” and if the score is equal to and greater than 75 then the grade would be “A”.

 

img2

 

To return the grades follow the below given steps:-

  • Select the cell C2 and write the formula.
  • =IF (B2<=50,”C”, IF (AND (B2>50, B2<75),”B”,”A”)) and press enter on the keyboard.
  • The function will return the grade “C” as per the criteria.
  • To return the grade for the rest of the students copy the formula by pressing the key “CTRL+C” and paste it in the range C3:C12 by pressing the key “CTRL+V” on the keyboard.

 

img3

 

Now lets follow the below given steps toapply Conditional Formatting with the If statement:-

    • Select the range C2:C12.
    • Go to the “Home” tab in the “Styles” group click on “Conditional Formatting” and from the drop down list, select New Rule.

 

img4

 

      • The “New Formatting Rule” dialog box will appear.
      • In “Edit the Rule Description” select “Specific Text”, then select “Containing”.
      • Type A in the box next to it.
  • Click on “Format” tab,•the “Format cells” dialog box will appear.
  • In the fill tab select green color.
  • Click on ok in both dialog boxes.

 

 

img5

 

img6

 

To set the formatting for the rest of the grades follow the below given steps:-

      • Select the range C2:C12.
      • Go to the “Home” tab in the “Styles” group click on “Conditional Formatting” from the drop down list and select Manage Rules.
      • The “Conditional Formatting Rules Manager” dialog box will appear.

 

img7

 

      • Click on “New Rule” you will get the “New Formatting Rule” dialog box.
      • Follow the above mentioned steps for grade A to apply the color format for grade B. For grade C follow the same steps as grade A.

After putting the “Conditional Formatting” conditions for all the grades you will get the data as shown below.. Also if you update the scores, the grades will change automatically.

 

img8

Users are saying about us...

  1. What if Column C had numbers (they are weekly grades), and you wanted to show a trend (arrows) comparing progress of each student from the current week (column c) from the previous week (column b)

    • Patrick, most font sets have arrows in them. if you go into the start menu and in the search box, type “character map”. In the character map, you can copy characters and paste them into the formula. So in your case, the formula would look like:
      =IF(C2>B2,”?”,IF(C2=B2,””,”?”))

      • My arrows got converted to question marks, but where you see the question marks in the formula, you would copy and paste the up and down arrows from the character map.

  2. Hi, i was wondering if you can conditionally format under a number of conditions. For example.

    If cell a1 is less than 0.05 then format cell b2 if it is greater than 0.5, if cell a1 is 0.05-0.10, then format cell b2 if it is greater than 0.25, if cell a1 is between 0.1 and 0.5 the format cell b2 if it is greater than 0.15…..does this makes sense? Can you have multiple conditions on multiple cells for formatting on one cell?? Please help.

  3. I ran a conditional formatting rule to find duplicates in column A (=COUNTIF($A$1:$A$3556,$A1)>1) …. which gave me my duplicates :-)

    Now i want to run a second rule to search for duplicates in column B but but only against the highlighted rows from my conditional formatted search. —

    end result: i just want to check the duplicate rows to see if the info in column B is different.
    ( column A – addresses, column B first name column C last name)

    I don’t care about last name i need to see if there is duplicate first name record against the duplicate address result. Please help

    • Hi Imran,

      As you mentioned that you have got the duplicates value (highlighted by conditional formatting) in column A and now you want to lookup the corresponding value whether it is duplicate or not against the highlighted value?

      Here is the formula which you have to apply through conditional formatting in column B (assuming your data starts from row 2).

      =COUNTIFS($A:$A,$A2,$B:$B,$B2)>1

      The above formula will highlight the duplicate values in column B for the highlighted duplicates value in column A.

      Happy Learning,
      Site Admin

  4. I’m trying to use conditional formatting using an IF statement to change a cell to 0 with a strikethrough if the cell is less than or equal to 0. Would anyone know how to do that if possible? Thanks in advance!

    • Hi Brad,

      You can’t change any value using conditional formatting as it will only allow you to format the range/cell based on condition entered.

      In case you only want to strikenthrough the cell if negative value is entered, simply select the cell and open Conditional Formatting dialog box.
      Go to “Format only cell that contain” and select “Cell Value” as “Less than or equal to” and put 0 in the input box. After that, click on Format and click on “Strikenthrough” and then hit OK twice.

      You can test the condition now as it should work.

      For more Excel Queries, we request you to please login on Excel Forum and post your queries there to get the instant solution for the same.

      Regards,
      Site Admin

  5. Hi,
    I have data as follows:
    B1:F13 (5 random numbers in each row)
    And another set of numbers
    J1:Q2 (16 numbers)
    I used conditional formatting to highlight the duplicate values.

    I would appreciate if anyone can assist with an if statement that would return “1″ if only 3 numbers are highlighted, 4 if 4 numbers are highlited and 10 if 5 numbers are highlighted.
    Thank you

  6. hi,
    I have to enter value everyday for nest cell to last week
    now if that sale value is greater then last week green colour
    if not red colour
    please help

Leave a Reply to ash Cancel reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube