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

You can construct a conditional formula to conduct a computation when a cell contains a particular value. If the condition is true, a conditional formula provides one value; if the condition is false, a different value is returned. There are numerous ways to create a conditional formula in Excel. In this article, we’ll demonstrate 5 different and easy methods to create a conditional formula in Excel. So, let’s go through the article to explore them one by one.


Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.


5 Methods to Create a Conditional Formula in Excel

For ease of understanding, we are going to use a Score List of Math’s of some students of a particular institution. This dataset contains the ID, Student Name, and their corresponding Marks in columns B, C, and D respectively.

how to create a conditional formula in excel

Now, we’ll show you 5 easy approaches to create a conditional formula in Excel. So, let’s look at them one by one.
Here, we have used Microsoft Excel 365 version, you may use any other version according to your convenience.


1. Using IF Function

In our first method, we’ll use the IF function to create a conditional formula. Here, we’ll check whether a student passed or failed the exam. So, let’s get into it.

📌 Steps:

  • At the very beginning, 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.

  • Then, press the ENTER key.

Using IF Function

  • At this time, bring the cursor to the right-bottom corner of cell E5 and it’ll look like a plus (+) sign. Actually, it’s the Fill Handle tool.
  • Now, double-click on it.

Using Fill Handle Tool

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

Using IF Function to create a conditional formula in excel

Read More: How to Create a Formula in Excel (5 Ways)


2. Inserting AND, IF, and OR Functions

In the second method, we’ll use the AND, IF, and OR functions in one formula. Here, we’ll imply multiple conditions to pass the exam. For this method, we’ll add marks for another subject: Geography.

Inserting AND, IF, OR Functions

If a student gets above 50 in both subjects, he will pass the exam. Or, he has to obtain marks above 40 in Maths and marks above 60 in Geography to pass the exam. Otherwise, he’ll get a “failed” result. Now, we’ll do it in Excel for these 10 students. So, let’s see it in action.

📌 Steps:

  • First of all, 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”.
  • Secondly, press ENTER.

Inserting AND, IF, OR Functions to create a conditional formula in excel

In this example, Jonas got 54 and 44 in Maths and Geography respectively. So, he gets “Failed” in the exam as per the formula.

Read More: How to Create a Custom Formula in Excel (A Step-by-Step Guideline)


3. Applying IF and SUM Functions

Now, we’ll add the SUM function with the IF function to show something new. Allow me to demonstrate the process below.

📌 Steps:

  • At first, select cell F5 and write down the following formula.
=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, then it gives the output “Excellent”. If it’s greater than 100, then the output is “Good”. On the other hand, the output is “Not Satisfactory”.

  • After that, hit ENTER.

Applying IF and SUM Functions to create a conditional formula in excel

Jonas gets a total of 98 which is less than 100. So, his result is “Not Satisfactory”.

Read More: How to Apply Formula to Entire Column Without Dragging in Excel


Similar Readings


4. Implementing IF, MAX, and MIN Functions

In this example, we’ll find out who has the highest and lowest mark in the class. So, without further delay, let’s dive in!

📌 Steps:

  • Firstly, we’ve to find out the total number of each student.
  • So, move to cell F5 and paste the following formula.
=SUM(D5:E5)
  • Then, hit the ENTER key.

Implementing IF, MAX and MIN Functions

  • After that, 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.

  • Following this, tap ENTER.

  • Therefore, double-click on the Fill Handle tool to get results for the remaining cells.

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

Implementing IF, MAX and MIN Functions to create a conditional formula in excel

Read More: How to Apply Formula to Entire Column Using Excel VBA


5. Utilizing COUNTIF Function

This time, we’ll show the conditional formula for counting anything in Excel using the COUNTIF function. In the following dataset, we get the respective Gender of each student.

Utilizing COUNTIF Function

We want to count the total number of female students. Excited? Let’s see the process in detail.

📌 Steps:

To do this, create a new output range in the B16:D16 range.

  • Then, go to cell D16 and put down the following formula.
=COUNTIF(D5:D14,"Female")
  • Later, tap the ENTER key.

Utilizing COUNTIF Function to create a conditional formula in excel

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.

Read More: How to Create a Formula in Excel without Using a Function (6 Approaches)


Creating Conditional Formatting Formula in Excel

We can use the conditional formatting formula with the IF function in Excel for an easy calculative and attractive worksheet. It’s simple and easy; just follow along.

📌 Steps:

  • Initially, we get the Results in Column E using Method 1.

Conditional Formatting Formula in Excel

  • Then, go to the Home tab.
  • After that, click on the Conditional Formatting drop-down on the Styles group of commands.
  • Next, select New Rule from the dropdown list.

Immediately, the New Formatting Rule dialog box appears before us.

  • Here, select Use a formula to determine which cells to format under the Select a Rule Type section.
  • Following this, write down the following formula in the Format values where this formula is true box.
=E5=“Passed”
  • Later, click on the Format button.

Entering Conditional Formatting Formula

Consequently, the Format Cells wizard pops up.

  • Then, go to the Fill tab.
  • Moreover, choose Light Green as the Background Color.
  • As usual, click OK.

Choosing Background Color

Again, it returns us to the New Formatting Rule dialog box.

  • Then, click OK.

Eventually, it highlights the cells containing the text Passed.

  • Currently, repeat the above steps for the cells containing Failed. But the difference is the use of Red as the background color in this case.

Conditional Formatting Formula in Excel

Read More: How to Create a Complex Formula in Excel (with Easy Steps)


Applying Conditional Formatting Formula Based on Another Cell in Excel

While dealing with a large database, you may want to format some specific cells based on other cells or values to identify them quickly. In such a case, you can use conditional formatting to create a formatting formula. Here, we want to apply conditional formatting in the B5:E14 range based on cell C16. The whole row will get formatted and have the “Passed” status.

Applying Conditional Formatting Formula Based on Another Cell in Excel

To do this effectively using this method, you may follow the below steps.

📌 Steps:

  • Primarily, get the New Formatting Rule dialog box like before.
  • Again, select Use a formula to determine which cells to format.
  • In the Format values where this formula is true box, write down the following formula.
=$E5=$C$16
  • Then, click Format.

Working on New Formatting Rule Dialog box

  • After that, choose the same color as before.
  • Also, click OK.

  • Lastly, click OK in the New Formatting Rule dialog box.

Presently, we can see the whole row with the preferred fill color, which contains the text Passed.

Applying Conditional Formatting Formula Based on Another Cell in Excel

Read More: How to Create a Formula in Excel for Multiple Cells (9 Methods)


Practice Section

For doing practice by yourself, we have provided a Practice section like the one below on each sheet on the right side. Please do it by yourself.

Practice Section


Conclusion

This article explains how to use a continuous probability distribution in Excel in a simple and concise manner. Don’t forget to download the Practice file. Thank you for reading this article. We hope this was helpful. Please let us know in the comment section if you have any queries or suggestions. Please visit our website, Exceldemy, a one-stop Excel solution provider, to explore more.


Related Articles

Shahriar Abrar Rafid

Shahriar Abrar Rafid

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc in Engineering from the Bangladesh University of Engineering & Technology. I am a Naval Architecture and Marine Engineering graduate with a great interest in research and development. I love reading books & traveling. Always try to gather knowledge from various sources and implement them effectively in my work.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo