Excel Conditional Formatting Formula with IF

Method 1 – Simple Conditional Formatting Formula with IF in Excel

Consider a sample dataset of Products with their purchase and sold amounts. You can determine whether the products accrued a profit or loss in a single column with Conditional Formatting.

Steps:

Select Cell E5.
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, it will return “Loss”.

Select the E column and go to the Home tab. From the Conditional Formatting drop-down, select New Rule.

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.

In the Format Cells window, go to the Fill section.
Choose any background color.
Click on OK.

Select OK again to exit the New Formatting Rule window.
Note that the “Profit” cells are highlighted with color.

You can repeat the process for highlighting the “Loss” cells.

Method 2 – Conditional Formatting Formula with Multiple IF Statements in Excel

Consider a dataset (B4:D9) of student names and their marks. Let’s find the student’s grade and use conditional formatting to highlight the cells based on grade.

Steps:

Select Cell D5.
Input the formula:

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

Hit Enter and use the Fill Handle tool to apply the formula to other cells.

How Does the Formula Work?

IF(C5<70,”B”,”A”): This will return “B” if the marks are lower than 70. Otherwise, it returns “A”.

IF(C5<40,”F”,IF(C5<70,”B”,”A”)): This will return “F” if the mark is less than 40, or go to the next IF otherwise.

Go to the Home tab > Conditional Formatting drop-down > New Rule.

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, then type “F” in the box on the right.
Click on the Format option.

In the Format Cells window, go to the Fill section and select the background color. You can preview the color sample in the Sample box.
Select OK.

Select OK again to close the Formatting Rule box.
You can now see the cell containing “F” is colored.

Repeat the process for Conditional Formatting with other grade texts and choose different colors.

Method 3 – Using IF and COUNTA Functions in Excel Conditional Formatting

Here we have a dataset in which range B5:B9 contains product names 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, which will then format the cell C11 containing DONE to get a color.

Steps:

Select cell C11 and go to the Home tab.
Click on the Conditional Formatting drop-down.
Choose New Rule.

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 a fill color like in the previous methods.
Click on 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.

Results:

When we type “Delivered” in cell C9, cell C11 gets colored.

Method 4 – Combining IF and AND Functions to Apply Conditional Formatting in Excel

Consider a dataset (B4:C9) of products and their purchase amounts. Let’s color the products in the 1200-2800 purchase amount range.

Steps:

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

From the New Formatting Rule 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 (see previous methods) from the Format option.
Click on 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.