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")
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.
=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")
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.
=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")
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.
=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")
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.
=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
Get FREE Advanced Excel Exercises with Solutions!
