When dealing with large amounts of data, formatting the data in Excel in specific ways can improve readability. If you are looking for some special tricks to make yes green and no red in Excel, you’ve come to the right place. There are numerous ways to make yes green and no red in Excel. This article will discuss eight suitable examples of making yes green and no red in Excel. Let’s follow the complete guide to learn all of this.

**Table of Contents**hide

## Download Practice Workbook

Download this practice workbook to exercise while you are reading this article. It contains all the datasets and methods in different spreadsheets for a clear understanding.

## 8 Suitable Examples to Make Yes Green and No Red in Excel

In Excel, we will use eight practical and tricky examples to make yes green and no red. This section provides extensive details on the eight ways. You can use either one for your purpose, they have a wide range of flexibility when it comes to customization. You should learn and apply all of these, as they improve your thinking capability and Excel knowledge. We use the **Microsoft Office 365** version here, but you can utilize any other version according to your preference.

### 1. Highlighting Cells Rules

Here, we are going to demonstrate how to make yes green and no red in Excel. Let us first introduce you to our Excel dataset so that you are able to understand what we are trying to accomplish with this article. We have a dataset showing students’ names and math scores. We will use **the IF Function** to illustrate passed and failed scores in the Passed column. Here we will use the ‘Text that Contains’ option of Conditional Formatting. Let’s walk through the following steps to make yes green and no red in Excel.

**📌 Steps:**

- First of all, we will use the following formula in cell
**D5**to illustrate passed and failed scores in the**Passed**column.

`=IF(C5>40,"Yes","No")`

This formula will check whether the value of cell **C5** is greater than **40** or not. If the condition is met, the function will return **Yes**. Else it returns** No**.

- Then, press
**Enter**.

- Next, drag the
**Fill Handle**icon to fill the other cells with the formula. - As a result, you will get the following
**Passed**column.

- Next, select the ranges of cells, go to the
**Home**tab and select**Conditional Formatting**. - Then, from the drop-down menu select
**Highlight Cells Rules**. - Afterward, from the drop-down menu select
**Text that Contains**.

- Then, the
**Text That Contains**dialog box will open up. - Write
**Yes**in the first box and select your desired formatting style (here,**Green File with Dark Green Text**style has been selected) in the second box. - Press
**OK**.

- Then, follow the above process, and the
**Text That Contains**dialog box will open up, write No in the first box and select your desired formatting style (here,**Light Red Fill with Dark Red Text**style has been selected) in the second box.

- Press
**OK**.

- Finally, you will be able to make yes green and no red in Excel as shown below.

**Read More:** **Excel Highlight Cell If Value Greater Than Another Cell (6 Ways)**

### 2. Using SEARCH Function

In this section, we will be using **the SEARCH function** in Conditional Formatting to do the task. We will use **the IF Function** to illustrate passed and failed scores in the Passed column. Let’s walk through the following steps to make yes green and no red in Excel.

**📌 Steps:**

- First of all, we will use the following formula in cell
**D5**to illustrate passed and failed scores in the**Passed**column.

`=IF(C5>40,"Yes","No")`

This formula will check whether the value of cell **C5** is greater than **40** or not. If the condition is met, the function will return **Yes**. Else it returns **No**.

- Then, press
**Enter**.

- Next, drag the
**Fill Handle**icon to fill the other cells with the formula. - As a result, you will get the following
**Passed**column.

- Next, select the ranges of cells, go to the
**Home**tab and select**Conditional Formatting**. - Then, from the drop-down menu select
**New Rule**.

- Then, the
**New Formatting Rule**window will open. - Select
**Use a formula to determine which cells to format**option. - Type the following formula in the
**Format values where this formula is true**box.

`=SEARCH("Yes",$D5)>0`

The **SEARCH** function will look for Yes in the cells of column **D** and for finding any matches it will return a value of **Yes** and so for finding the matches, it will return a value greater than **0**.

- Next, click on the
**Format**option.

- After that, the
**Format Cells**dialog box will open up. - Select the
**Fill**option, choose**Green**as**Background Color**, and then click on**OK**. - Then, click on
**OK**.

- Click on
**OK**.

- Consequently, you will be able to make yes green in Excel as shown below.

- Next, you have to follow the same procedure mentioned above in order to make no red in Excel.
- When the
**New Formatting Rule**window will open, select**Use a formula to determine which cells to format**option. - Type the following formula in the
**Format values where this formula is true**box.

`=SEARCH("No",$D5)>0`

The** SEARCH** function will look for Yes in the cells of column **D** and for finding any matches it will return a value of **No** and so for finding the matches, it will return a value greater than** 0**.

- Next, click on the
**Format**option.

- After that, the
**Format Cells**dialog box will open up. - Select the
**Fill**option, choose**Red**as**Background Color**, and then click on**OK**. - Then, click on
**OK**.

- Click on
**OK**.

- Finally, you will be able to make yes green and no red in Excel as shown below.

**Read More:** **Excel Conditional Formatting Formula**

### 3. Applying SEARCH and ISNUMBER Functions

In this section, we are going to use the combination of the **SEARCH** and **ISNUMBER** functions to **apply Conditional Formatting** to the cells of the Passed column. We will use **the IF Function** to illustrate passed and failed scores in the Passed column. Let’s walk through the following steps to make yes green and no red in Excel.

**📌 Steps:**

- First of all, we will use the following formula in cell
**D5**to illustrate passed and failed scores in the**Passed**column.

`=IF(C5>40,"Yes","No")`

This formula will check whether the value of cell **C5** is greater than **40** or not. If the condition is met, the function will return **Yes**. Else it returns **No**.

- Then, press
**Enter**.

- Next, drag the
**Fill Handle**icon to fill the other cells with the formula. - As a result, you will get the following
**Passed**column.

- Next, select the ranges of cells, go to the
**Home**tab and select**Conditional Formatting**. - Then, from the drop-down menu select
**New Rule**.

- Then, the
**New Formatting Rule**window will open. - Select
**Use a formula to determine which cells to format**option. - Type the following formula in the
**Format values where this formula is true**box.

`=ISNUMBER(SEARCH("Yes",$D5))`

The **SEARCH** function will look for** Yes** in the cells of column **D** and for finding any matches it will return a value of **No**. And so** ISNUMBER** will return a** TRUE** if it gets any numeric value otherwise **FALSE**.

- Next, choose
**Green**as**Background Color**from the**Format**option. - Then, click on
**OK**.

- Consequently, you will be able to make yes green in Excel as shown below.

- Next, you have to follow the same procedure mentioned above in order to make no red in Excel.
- When the
**New Formatting Rule**window will open, select**Use a formula to determine which cells to format**option. - Type the following formula in the
**Format values where this formula is true**box.

`=ISNUMBER(SEARCH("No",$D5))`

The** SEARCH** function will look for Yes in the cells of column **D** and for finding any matches it will return a value of **No**. And so **ISNUMBER** will return a **TRUE** if it gets any numeric value otherwise **FALSE**.

- Next, choose
**Red**as**Background Color**from the**Format**option. - Then, click on
**OK**.

- Finally, you will be able to make yes green and no red in Excel as shown below.

**Read More:** **Apply Conditional Formatting to Each Row Individually: 3 Tips**

### 4. Using FIND Function

We will use **the FIND function** in the Conditional Formatting here. We will use **the IF Function** to illustrate passed and failed scores in the Passed column. Let’s walk through the following steps to make yes green and no red in Excel.

**📌 Steps:**

- First of all, we will use the following formula in cell
**D5**to illustrate passed and failed scores in the**Passed**column.

`=IF(C5>40,"Yes","No")`

**C5** is greater than **40** or not. If the condition is met, the function will return **Yes**. Else it returns** No**.

- Then, press
**Enter**.

- Next, drag the
**Fill Handle**icon to fill the other cells with the formula. - As a result, you will get the following
**Passed**column.

- Next, select the ranges of cells, go to the
**Home**tab and select**Conditional Formatting**. - Then, from the drop-down menu select
**New Rule**.

- Then, the
**New Formatting Rule**window will open. - Select
**Use a formula to determine which cells to format**option. - Type the following formula in the
**Format values where this formula is true**box.

`=Find("Yes",$D5)`

The** FIND** function will look for **Yes** in the cells of column **D** and for finding any matches it will return **Yes**. For not matching with the cases of **Yes** properly, we will not get any value.

- Next, choose
**Green**as**Background Color**from the**Format**option. - Then, click on
**OK**.

- Consequently, you will be able to make yes green in Excel as shown below.

- Next, you have to follow the same procedure mentioned above in order to make no red in Excel.
- When the
**New Formatting Rule**window will open, select**Use a formula to determine which cells to format**option. - Type the following formula in the
**Format values where this formula is true**box.

`=Find("No",$D5)`

The **FIND** function will look for Yes in the cells of column** D** and for finding any matches it will return **Yes**. For not matching with the cases of **Yes** properly, we will not get any value.

- Next, choose
**Red**as**Background Color**from the**Format**option. - Then, click on
**OK**.

- Finally, you will be able to make yes green and no red in Excel as shown below.

**Read More:** **How to Do Conditional Formatting with Multiple Criteria (11 Ways)**

**Similar Readings**

**Excel Conditional Formatting for Dates within 30 Days (3 Examples)****Apply Conditional Formatting to the Selected Cells in Excel (6 Ways)****How to Use Conditional Formatting Based on VLOOKUP in Excel****Conditional Formatting on Multiple Rows Independently in Excel****Conditional Formatting on Text that Contains Multiple Words in Excel**

### 5. Utilizing COUNTIF Function

In this section, we are going to apply Conditional Formatting with the help of **the COUNTIF function** for making yes green and no red in Excel. We will use **the IF Function** to illustrate passed and failed scores in the Passed column. Let’s walk through the following steps to make yes green and no red in Excel.

**📌 Steps:**

- First of all, we will use the following formula in cell
**D5**to illustrate passed and failed scores in the Passed column.

`=IF(C5>40,"Yes","No")`

**C5** is greater than **40** or not. If the condition is met, the function will return **Yes**. Else it returns** No**.

- Then, press
**Enter**.

- Next, drag the
**Fill Handle**icon to fill the other cells with the formula. - As a result, you will get the following
**Passed**column.

- Next, select the ranges of cells, go to the
**Home**tab and select**Conditional Formatting**. - Then, from the drop-down menu select
**New Rule**.

- Then, the
**New Formatting Rule**window will open. - Select
**Use a formula to determine which cells to format**option. - Type the following formula in the
**Format values where this formula is true**box.

`=COUNTIF($D5,"*Yes*")`

By using the **wildcard symbol*** before and after **Yes** we are ensuring the partial matches here and the** COUNTIF** function will return the number of times this text portion appears in the cells of column** D**.

- Next, choose
**Green**as**Background Color**from the**Format**option. - Then, click on
**OK**.

- Consequently, you will be able to make yes green in Excel as shown below.

- Next, you have to follow the same procedure mentioned above in order to make no red in Excel.
- When the
**New Formatting Rule**window will open, select**Use a formula to determine which cells to format**option. - Type the following formula in the
**Format values where this formula is true**box.

`=COUNTIF($D5,"*No*")`

By using the **wildcard symbol *** before and after No, we are ensuring the partial matches here and the **COUNTIF** function will return the number of times this text portion appears in the cells of column **D**.

- Next, choose
**Red**as**Background Color**from the**Format**option. - Then, click on
**OK**.

- Finally, you will be able to make yes green and no red in Excel as shown below.

**Read More:** **Excel If Cell Color Is Green Then Show or Customize Outputs**

### 6. Combining COUNT and SEARCH Functions

Here, we will be using the combination of the **COUNT** and **SEARCH** functions to apply Conditional Formatting to the cells for making yes green and no red in Excel. We will use **the IF Function** to illustrate passed and failed scores in the Passed column. Let’s walk through the following steps to make yes green and no red in Excel.

**📌 Steps:**

- First of all, we will use the following formula in cell
**D5**to illustrate passed and failed scores in the**Passed**column.

`=IF(C5>40,"Yes","No")`

**C5** is greater than **40** or not. If the condition is met, the function will return **Yes**. Else it returns **No**.

- Then, press
**Enter**.

- Next, drag the
**Fill Handle**icon to fill the other cells with the formula. - As a result, you will get the following
**Passed**column.

- Next, select the ranges of cells, go to the
**Home**tab and select**Conditional Formatting**. - Then, from the drop-down menu select
**New Rule**.

- Then, the
**New Formatting Rule**window will open. - Select
**Use a formula to determine which cells to format**option. - Type the following formula in the
**Format values where this formula is true**box.

`=COUNT(SEARCH("Yes",$D5))`

The **SEARCH** function will look for **Yes** in the cells of column **D** and for finding any matches it will return a value in the full text. And then, the** COUNT** function will return **1** if it gets any number from the output of the **SEARCH** function otherwise **0**.

- Next, choose
**Green**as**Background Color**from the**Format**option. - Then, click on
**OK**.

- Consequently, you will be able to make yes green in Excel as shown below.

- Next, you have to follow the same procedure mentioned above in order to make no red in Excel.
- When the
**New Formatting Rule**window will open, select**Use a formula to determine which cells to format**option. - Type the following formula in the
**Format values where this formula is true**box.

`=COUNT(SEARCH("No",$D5))`

The **SEARCH** function will look for **No** in the cells of column **D** and for finding any matches it will return a value in the full text. And then, the **COUNT** function will return **1** if it gets any number from the output of the **SEARCH** function otherwise **0**.

- Next, choose
**Red**as**Background Color**from the**Format**option. - Then, click on
**OK**.

- Finally, you will be able to make yes green and no red in Excel as shown below.

**Read More:** **How to Sum in Excel If the Cell Color Is Red (4 Easy Methods)**

### 7. Applying IF and SEARCH Functions

In this section, we will be using the combination of the **IF** and **SEARCH** functions in Conditional Formatting to highlight the cells for making yes green and no red in Excel. We will use the** IF** Function to illustrate passed and failed scores in the Passed column. Let’s walk through the following steps to make yes green and no red in Excel.

**📌 Steps:**

- First of all, we will use the following formula in cell
**D5**to illustrate passed and failed scores in the**Passed**column.

`=IF(C5>40,"Yes","No")`

**C5** is greater than **40** or not. If the condition is met, the function will return **Yes**. Else it returns **No**.

- Then, press
**Enter**.

- Next, drag the
**Fill Handle**icon to fill the other cells with the formula. - As a result, you will get the following
**Passed**column.

- Next, select the ranges of cells, go to the
**Home**tab and select**Conditional Formatting**. - Then, from the drop-down menu select
**New Rule**.

- Then, the
**New Formatting Rule**window will open. - Select
**Use a formula to determine which cells to format**option. - Type the following formula in the
**Format values where this formula is true**box.

`=IF(SEARCH("Yes",$D5),1,0)>0`

The **SEARCH** function will look for** Yes** in the cells of column **D** and for finding any matches it will return a value in the full text. And then, IF will return **1** if the **SEARCH** function finds any matches otherwise **0**, and for values greater than **0** finally, we will get **TRUE** otherwise **FALSE**.

- Next, choose
**Green**as**Background Color**from the**Format**option. - Then, click on
**OK**.

- Consequently, you will be able to make yes green in Excel as shown below.

- Next, you have to follow the same procedure mentioned above in order to make no red in Excel.
- When the
**New Formatting Rule**window will open, select**Use a formula to determine which cells to format**option. - Type the following formula in the
**Format values where this formula is true**box.

`=IF(SEARCH("No",$D5),1,0)>0`

The **SEARCH** function will look for No in the cells of Column **D** and for finding any matches it will return a value in the full text. And then, the **IF** function will return **1** if the **SEARCH** function finds any matches otherwise **0** and for values greater than **0** finally, we will get** TRUE** otherwise **FALSE**.

- Next, choose
**Red**as**Background Color**from the Format option. - Then, click on
**OK**.

- Finally, you will be able to make yes green and no red in Excel as shown below.

**Read More:** **Excel Conditional Formatting Formula with IF**

### 8. Utilizing MATCH Function

We will use **the MATCH function** in Conditional Formatting for making yes green and no red in Excel. We will use **the IF Function** to illustrate passed and failed scores in the Passed column. Let’s walk through the following steps to make yes green and no red in Excel.

**📌 Steps:**

- First of all, we will use the following formula in cell
**D5**to illustrate passed and failed scores in the**Passed**column.

`=IF(C5>40,"Yes","No")`

**C5** is greater than **40** or not. If the condition is met, the function will return **Yes**. Else it returns **No**.

- Then, press
**Enter**.

- Next, drag the
**Fill Handle**icon to fill the other cells with the formula. - As a result, you will get the following Passed column.

- Next, select the ranges of cells, go to the
**Home**tab and select**Conditional Formatting**. - Then, from the drop-down menu select
**New Rule**.

- Then, the
**New Formatting Rule**window will open. - Select
**Use a formula to determine which cells to format**option. - Type the following formula in the
**Format values where this formula is true**box.

`=MATCH("*Yes*",$D5,0)`

By using the **wildcard symbol *** before and after **Yes**, we are ensuring the complete matches here and the **MATCH** function will return **1** for finding any partial matches in column **D**.

- Next, choose
**Green**as**Background Color**from the Format option. - Then, click on
**OK**.

- Consequently, you will be able to make yes green in Excel as shown below.

- Next, you have to follow the same procedure mentioned above in order to make no red in Excel.
- When the
**New Formatting Rule**window will open, select**Use a formula to determine which cells to format**option. - Type the following formula in the
**Format values where this formula is true**box.

`=MATCH("*No*",$D5,0)`

By using the** wildcard symbol *** before and after No, we are ensuring the complete matches here and the **MATCH** function will return 1 for finding any partial matches in column **D**.

- Next, choose
**Red**as**Background Color**from the**Format**option. - Then, click on
**OK**.

- Finally, you will be able to make yes green and no red in Excel as shown below.

**Read More:** **Conditional Formatting with INDEX-MATCH in Excel (4 Easy Formulas)**

## Conclusion

That’s the end of today’s session. I strongly believe that from now, you may be able to make yes green and no red in Excel. If you have any queries or recommendations, please share them in the comments section below.

Don’t forget to check our website **Exceldemy.com** for various Excel-related problems and solutions. Keep learning new methods and keep growing!

**Related Articles**

**Excel Alternating Row Color with Conditional Formatting [Video]****How to Make Negative Numbers Red in Excel (4 Easy Ways)****How to Compare Two Columns in Excel For Finding Differences****Excel conditional formatting based on another cell text [5 ways]****4 Quick Excel Formula to Change Cell Color Based on Date****Excel Conditional Formatting Dates Older than Today (3 Simple Ways)**