Nuraida Kashmin
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.
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.