Skip to Content

How do I change the color of a cell in Excel based on yes or no?

By: Author Color With Leo


Changing the color of cells in Excel based on whether the cell contains a “Yes” or “No” value is a common task when building conditional formatting rules. With conditional formatting, you can quickly visualize data by highlighting cells that meet certain criteria with color, icons, data bars, and more.

In this article, we’ll walk through the steps to create a conditional formatting rule that will check if a cell contains “Yes” or “No” and change the fill color accordingly. We’ll cover creating rules using the Conditional Formatting dialog box as well as with the New Formatting Rule menu.

Prerequisites

Before we get started, there are a couple things you need:

  • Excel 2016 or later
  • A worksheet with data you want to format. This should contain cells with either “Yes” or “No” values.

The examples below use Excel for Microsoft 365, but the steps are similar in previous versions.

Use the Conditional Formatting Dialog Box

Here are the steps to create a new conditional formatting rule using the Conditional Formatting dialog box:

  1. Select the cells you want to format. This would be the cells that contain either “Yes” or “No”.
  2. On the Home tab, click Conditional Formatting > New Rule.
  3. In the New Formatting Rule dialog box, click Use a formula to determine which cells to format.
  4. In the formula field, enter the formula:
    =$A1="Yes"
  5. Click Format and select the Fill tab. Choose the color you want cells with “Yes” to be filled.
  6. Click OK to close the Format Cells dialog.
  7. Click OK again to close the New Formatting Rule dialog.

This will create a rule that checks if the cell in column A equals “Yes” and applies the fill color you selected to those cells.

Now we need to create another rule for cells containing “No”:

  1. With the same cells selected, click Conditional Formatting > New Rule again.
  2. Enter the formula:
    =$A1="No"
  3. Click Format and choose a different fill color for “No” cells.
  4. Click OK twice to close the dialogs.

Now your cells will be formatted with different colored fills based on their “Yes” or “No” values!

Use New Formatting Rule Menu

You can also create the conditional formatting rules using the New Formatting Rule menu:

  1. Select the cells to format.
  2. On the Home tab, click Conditional Formatting > New Rule.
  3. Hover over the rule types and select Format only cells that contain.
  4. In the dropdowns, select Cell Value | equal to | “Yes”.
  5. Click Format and choose the fill color for “Yes” values.
  6. Click OK to close the Format Cells dialog.
  7. Click OK again to create the rule.
  8. Repeat steps 2-6, but select “No” for the cell value in step 4. Pick a different fill color.
  9. Click OK to create the “No” rule.

This achieves the same result as using the Conditional Formatting dialog box. The New Formatting Rule menu provides a more guided experience.

Additional Options

There are couple additional options you may want to consider when creating these types of conditional formatting rules:

  • Applies to range: You can specify the rule applies to a different range than currently selected. This allows you to create one rule that can be applied to multiple ranges.
  • Stop if True: Checking this will stop evaluating rules after the first match. This can improve performance with multiple rules.
  • Clear existing rules: Use this option to clear any existing rules before creating your new ones.

You can also customize the rules further by using custom formulas or different formats like font color, borders, and icons.

Using a Formula for Any Text Value

Instead of creating separate rules for “Yes” and “No”, you can use a single formula rule to check for any text value:

=ISTEXT($A1)

This will format cells if they contain any text value. You could combine this with font color formatting to highlight all text cells.

Delete Conditional Formatting Rules

To delete a conditional formatting rule:

  1. Select the cells with the rule applied.
  2. On the Home tab, click Conditional Formatting > Manage Rules.
  3. In the Conditional Formatting Rules Manager, select the rules you want to delete.
  4. Click Delete Rule.
  5. Click OK.

This will remove the selected rules from the cells.

Conclusion

Conditional formatting in Excel provides a handy visual way to highlight values that meet certain criteria. For text values like “Yes” and “No”, you can create custom rules based on formulas to change the fill color of cells containing those values.

The key steps are:

  • Select cells to format
  • Create a new conditional formatting rule using a formula to check for “Yes” or “No”
  • Choose the cell fill color
  • Adjust rule options like Applies to range or Stop if True
  • Repeat for the other text value

Following these steps, you can build conditional formatting rules that automatically color code cells to visualize your data based on yes/no values.