We can use the conditional formatting formula with IF in Excel for an easy calculative and attractive worksheet. In this article, we are going to learn how to do that with some beautiful examples and explanations.

**Table of Contents**hide

**Practice Workbook**

Download the following workbook and exercise.

## 4 Quick Methods of Excel Conditional Formatting Formula IF

### 1. Conditional Formatting Formula with IF in Excel

We know that the Excel **IF function** helps us to run a logical test as well as returns **TRUE **for one value and **FALSE **for another one and Excel Conditional Formatting helps us apply specific formatting to a range. Assuming we have a dataset (**B4:E9**) of Products with their purchase and sold amounts. We are going to calculate the loss or profit of products in cell range **E5:E9** and apply conditional formatting to highlight the values.

**STEP 1:**

- First, select
**Cell E5**. - Next type the formula:

`=IF(D5>C5,"Profit","Loss")`

- Hit
**Enter**and use the**Fill Handle**to autofill the next cells.

This will return “**Profit**” if cell **D5** is greater than **C5**. Otherwise, this will return “**Loss**”.

**STEP 2:**

- Now select the required cells and go to the
**Home**tab. From the**Conditional Formatting**drop-down, select**New Rule**.

**STEP 3:**

- Click on the “
**Use a formula to determine which cells to format**” option. - In the formula box, type the formula:

`=E5=”Profit”`

- Select the
**Format**option.

**STEP 4:**

- Then from the
**Format Cells**window, go to the**Fill**section. - Choose any background color. We can see the sample of color in the sample box.
- Click on
**OK**.

**STEP 5:**

- Again click on
**OK**. - Finally, we can see that the “
**Profit**” cells are highlighted with color.

We can do the same process for highlighting the “**Loss**” cells.

**Read more: Conditional Formatting If Cell is Not Blank**

### 2. Excel Conditional Formatting Formula with Multiple IF Statements

Suppose, we have a dataset (**B4:D9**) of student names and their marks. We are going to use multiple **IF** statements to find out the student’s grade and conditional formatting to highlight the cells based on grade.

**STEP 1:**

- In the beginning, select
**Cell D5**. - Now type the formula:

`=IF(C5<40,"F",IF(C5<70,"B","A"))`

**STEP 2:**

- Hit
**Enter**and use the**Fill Handle**tool to the next cells.

🔎 **How Does the Formula Work?**

**IF(C5<70,”B”,”A”):**This will return “**B**” if the marks are less than**70**otherwise “**A**”.**IF(C5<40,”F”,IF(C5<70,”B”,”A”)):**This will return “**F**” if the mark is less than**40**otherwise the result of the above procedure.

**STEP 3:**

- Next, go to the
**Home**tab >**Conditional Formatting**drop-down >**New Rule**.

**STEP 4:**

- In the
**New Formatting Rule**window, select the “**Format only cells that contain**” option. - Select
**Specific Text**option from the drop-down of the**Format only cells with**box. Also, type “**F**”. - Click on the
**Format**option.

**STEP 5:**

- The
**Format Cells**window pops up. - Go to the
**Fill**section and select the background color. We can see the color sample in the**Sample**box. - Select
**OK**.

**STEP 6:**

- Select
**OK**again. - In the end, we can see the cell containing “
**F**” is colored.

**STEP 7:**

- We can also select different colors for the different texts in the same procedure.

**Read More:** **How to Do Conditional Formatting with Multiple Criteria (11 Ways)**

**Similar Readings**

**How to Do Conditional Formatting for Multiple Conditions (8 Ways)****Conditional Formatting Multiple Text Values in Excel (4 Easy Ways)****Conditional Formatting with INDEX-MATCH in Excel (4 Easy Formulas)****How to Use Conditional Formatting Based on VLOOKUP in Excel****Excel Conditional Formatting Based on Date Range**

### 3. Excel Formula with IF & COUNTA Functions in Conditional Formatting

Here we have a dataset in which range **B5:B9** contains the name of the products and range **C5:C9 **contains their delivery status for **Day 1**. We are going to see that if the count of the “**Delivered**” in range **C5:C9 **is the same as the count of the products in range **B5:B9**, then **Cell C11 **containing **DONE** text will appear in color. We will use the Excel **COUNTA function** wrapped in the **IF function** with the **Conditional Formatting**.

**STEP 1:**

- First, select
**Cell C11**and go to the**Home**tab. - Click on the
**Conditional Formatting**drop-down. - Next select
**New Rule**.

**STEP 2:**

- Select the “
**Use a formula to determine which cells to format**” option from the “**New Formatting Rule**” window. - In the formula box, type the formula:

`=IF(COUNTA($C$5:$C$9)=COUNTA($B$5:$B$9),TRUE,FALSE)`

- From the
**Format**option, select the specific color as we have seen in the above procedures. - Now click
**OK**.

🔎 **How Does the Formula Work?**

**COUNTA($C$5:$C$9):**Excel**COUNTA function**will count the number of cells in the**C5:C9**range that contain values.**COUNTA($B$5:$B$9):**Excel**COUNTA function**will count the number of cells in the**B5:B9**range that contain values.**IF(COUNTA($C$5:$C$9)=COUNTA($B$5:$B$9),TRUE,FALSE):**Excel**IF function**will return**TRUE**if the two ranges (**B5:B9**&**C5:C9**) are equal, otherwise**FALSE**.

**STEP 3:**

- Finally, when in cell
**C9**we type “**Delivered**”, cell**C11**becomes colorful.

**Read More:** **Excel Conditional Formatting Formula**

### 4. Excel Conditional Formatting Formula with IF & AND Functions Combination

Let’s say, we have a dataset (**B4:C9**) of products and their purchase amounts. We are going to use Excel **IF** & **AND functions** with **Conditional Formatting** to see which products are in the **1200-2800** amount range.

**STEP 1:**

- Select the range of cells
**C5:C9**at first. - Now go to the
**Home**tab. - Select the
**Conditional Formatting**drop-down. - Click on the
**New Rule**option.

**STEP 2:**

- From the
**New Formatting RuleB window,**select the “**Use a formula to determine which cells to format**” option. - In the formula box, type the formula:

`=IF(AND(C5>1200,C5<2800),TRUE,FALSE)`

- Select the specific color as we have seen in the above procedures from the
**Format**option. - Next click
**OK**.

🔎 **How Does the Formula Work?**

**AND(C5>1200,C5<2800):**This will return**TRUE**if cell**C5**is greater than**1200**or less than**2800**.**IF(AND(C5>1200,C5<2800),TRUE,FALSE):**This will return**TRUE**if cell**C5**is in the**1200-2800**range, otherwise**FALSE**.

**STEP 3:**

- Finally, we can see the cells are highlighted.

**Read More:** **How to Use Conditional Formatting in Excel [Ultimate Guide]**

## Conclusion

These are the quick methods of** Conditional Formatting formulas with IF **in Excel. There is a practice workbook added. Go ahead and give it a try. Feel free to ask anything or suggest any new methods.

I am trying to apply conditional formatting to a column of values that are generated from an equation. I don’t any cell with a formula to display a value unless it was calculated by the formula. So I use an equation to make that happen. The problem is, if I try to conditional format the cells to highlight based on whether or not the returned value is greater than zero, Excel highlights the sells that are not returning any values (blank cells) because Excel considers the equation itself as greater than zero. Any ideas on how to workaround this scenario?

Hi Jon,

You can try this path:

Select cell range > Click on Conditional Formatting > Select New Rules > Go to ‘Format only cells that contain’ option > From the Edit drop-down, select ‘No Blanks’ > Select Fill color > Press OK.