How to Create a Conditional Formula in Excel (5 Easy Ways)

Let’s use a Score List of Math scores of some students. This dataset contains the ID, Student Name, and their corresponding Marks in columns B, C, and D respectively.

Method 1 – Using IF Function for a Single Condition

Here, we’ll check whether a student passed or failed the exam (scoring above 55 is a pass).

Steps:

Select cell E5 and enter the formula below:

=IF(D5>55,"Passed","Failed")

Here, we applied a logical statement. If the student gets a number above 55 then the formula will return Passed in cell E5. Otherwise, it will show Failed in that cell. In this case, Jonas got 54 which is less than 55. So, she gets Failed.

Press Enter.

Bring the cursor to the right-bottom corner of cell E5 and it’ll look like a plus (+) sign. This is the Fill Handle tool.
Double-click on it.

Excel copies the formula to the lower cells and gives the output of these cells automatically.

Method 2 – Inserting AND, IF, and OR Functions for Multiple Conditions

For this method, we’ll add marks for another subject: Geography. If a student gets above 50 in both subjects, he will pass the exam. Or, he has to obtain marks above 40 in Math and marks above 60 in Geography to pass the exam. Otherwise, he’ll get a “failed” result.

Steps:

Go to cell F5 and insert the formula below:

=IF(OR(AND(D5>50,E5>50),AND(D5>40,E5>60)),"Passed","Failed")

Formula Breakdown

AND(D5>40,E5>60): This part means the marks in Maths should be above 40 and the marks in Geography should be above 60.
AND(D5>50,E5>50): This part indicates that the number of both subjects has to be above 50.
OR(AND(D5>50,E5>50),AND(D5>40,E5>60)): Here, the OR function checks whether any of the two arguments are TRUE.
IF(OR(AND(D5>50,E5>50),AND(D5>40,E5>60)),”Passed”,”Failed”): If the OR function returns TRUE, then the IF function gives the output as “Passed”, otherwise it gives “Failed”.

Press Enter.

Method 3 – Applying IF and SUM Functions

Let’s score the student based on their combined scores in two subjects.

Steps:

Select cell F5 and copy the following formula into it:

=IF(SUM(D5:E5)>110,"Excellent",IF(SUM(D5:E5)>100,"Good","Not Satisfactory"))

Here, if the sum of numbers in cells D5 and E5 is greater than 110, the formula gives the output “Excellent”. If it’s greater than 100, then the output is “Good”. Otherwise, the output is “Not Satisfactory”.

Hit Enter.

Method 4 – Implementing IF, MAX, and MIN Functions

In this example, we’ll find out who has the highest and lowest mark in the class.

Steps:

Go to cell F5 and paste the following formula.

=SUM(D5:E5)

Hit the Enter key.

Proceed to cell G5 and apply the formula below:

=IF(F5=MAX($F$5:$F$14),"Highest Marks",IF(F5=MIN($F$5:$F$14),"Lowest Marks",""))

In this formula, the MAX function returns the largest value in the F5:F14 range. If the total in cell F5 is equal to the largest value, then it will give the output Highest Marks in cell G5. Then, the MIN function returns the lowest value from the same range. And, if F5 is equal to the smallest value, then it will return Lowest Marks in that cell.

Press Enter.

Double-click on the Fill Handle tool.

We can see that Harry gets the Highest Mark which is 133 and Nick gets the Lowest Mark which is 85.

Method 5 – Utilizing COUNTIF Function

In the following dataset, we have the respective Gender of each student. Let’s count the female students.

Steps:

Create a new output range in the B16:D16 range.
Go to cell D16 and put down the following formula.

=COUNTIF(D5:D14,"Female")

Press Enter.

The results show that there are a total of 4 female students in the dataset. You can verify it by counting manually, as the dataset is small enough.

Creating Conditional Formatting Formula in Excel

Steps:

Get the Results in Column E using Method 1.

Go to the Home tab.
Click on the Conditional Formatting drop-down on the Styles group of commands.
Select New Rule from the dropdown list.

The New Formatting Rule dialog box appears.
Select Use a formula to determine which cells to format under the Select a Rule Type section.
Copy the following formula in the Format values where this formula is true box:

=E5=“Passed”

Click on the Format button.

The Format Cells wizard pops up.
Go to the Fill tab.
Choose Light Green as the Background Color.
Click OK.

This returns to the New Formatting Rule dialog box.
Click OK.

Excel will highlight the cells containing the text Passed.

Repeat the above steps for the cells containing Failed. Use Red as the background color in this case.

Applying Conditional Formatting Formula Based on Another Cell in Excel

Let’s apply conditional formatting in the B5:E14 range based on cell C16. The whole row will get formatted and have the “Passed” status.

Steps:

Get to the New Formatting Rule dialog box like before.
Select Use a formula to determine which cells to format.
In the Format values where this formula is true box, copy the following formula:

=$E5=$C$16