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.

📌 Steps:

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

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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)