How to Make Yes Green and No Red in Excel (8 Examples)

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.


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.

Highlighting Cells Rules 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.

use IF function

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

select TEXT that Contains option

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

Finally able to Make Yes Green and No Red in Excel

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.

Using SEARCH Function 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.

apply IF function

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

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.

apply the formula

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

select desired color

  • Click on OK.

format the color

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

get the output

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

select red color

  • Click on OK.

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

Finally able to Make Yes Green and No Red in Excel

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.

Applying SEARCH and ISNUMBER Functions 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.

select conditional formatting to Make Yes Green and No Red in Excel

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

format the color

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

Finally able to Make Yes Green and No Red

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.

Using FIND Function 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.

using IF function

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

select conditional formatting

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

format the color to Make Yes Green and No Red in Excel

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

format red color to Make Yes Green and No Red in Excel

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

Finally able to Make Yes Green and No Red in Excel

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


Similar Readings


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.

Utilizing COUNTIF Function 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.

Using IF function

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

select conditional formatting

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

format the color

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

Finally able to Make Yes Green and No Red in Excel

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.

Combining COUNT and SEARCH Functions 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.

select conditional formatting

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

format the color

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

format No as red color

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

Finally able to Make Yes Green and No Red in Excel

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.

Applying IF and SEARCH Functions 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.

apply IF function

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

select conditional formatting

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

format the YES as Green color

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

Finally able to Make Yes Green and No Red in Excel

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.

Utilizing MATCH Function 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.

apply IF function

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

select conditional formatting

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

format Yes as green color

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

Make No Red in Excel

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

Finally able to Make Yes Green and No Red in Excel

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

Saquib

Saquib

Welcome to my Profile. I am working on and researching Microsoft Excel right now, and I will be posting articles about it here. I received a B.Sc. in Naval Architecture and Marine Engineering from the Bangladesh University of Engineering and Technology (BUET). Having studied naval architecture, I have a strong interest in research and development. Always try to learn from different sources and come up with creative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo