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.

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

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

### 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 must follow the same procedure mentioned above 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:** Conditional Formatting with Formula in Excel

### 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 must follow the same procedure mentioned above 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:** How to Apply Conditional Formatting to Each Row Individually

### 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")`

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.

`=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 must follow the same procedure mentioned above 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:** Applying Conditional Formatting for Multiple Conditions 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 must follow the same procedure mentioned above 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.

### 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 must follow the same procedure mentioned above 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. 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.

### 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 must follow the same procedure mentioned above 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 must follow the same procedure mentioned above 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:** How to Apply Conditional Formatting with INDEX-MATCH in Excel

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

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

**Related Articles**

- How to Create a Rating Scale in Excel
- How to Use Conditional Formatting on Text Box in Excel
- How to Apply Borders in Excel with Conditional Formatting
- How to Apply Alignment in Excel Conditional Formatting
- How to Copy Conditional Formatting to Another Cell in Excel
- How to Copy Conditional Formatting Color to Another Cell in Excel
- How to Copy Conditional Formatting to Another Sheet
- How to Copy Conditional Formatting to Another Workbook in Excel
- How to Copy Conditional Formatting with Relative Cell References in Excel
- How to Copy Conditional Formatting But Change Reference Cell in Excel

**<< Go Back to Conditional Formatting | Learn Excel**