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.

**Table of Contents**hide

## 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.

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.

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

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

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

**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**.

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**.

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**.

**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**

**How to Create a Formula in Excel to Change Date by 1 Year (3 Methods)****Create a Formula in Excel for Multiple Sheets (4 Methods)****How to Create a Formula to Calculate Percentage in Excel****How to Create a Formula in Excel That Will Place the Word Yes (7 Ways)**

### 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.

- 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**.

**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.

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.

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**.

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

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**.

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.

**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.

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**.

- 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**.

**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.

## 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.