Excel Conditional Formatting for Yes/No Red and Green

Yes—Excel can automatically color “Yes” green and “No” red with conditional formatting. For a status list in B2:B100, select that range, choose Home > Conditional Formatting > Highlight Cells Rules > Text that Contains, and make one green rule for Yes and one red rule for No; formula rules are better when you need an exact match or want to color an entire row.

What conditional formatting does

Conditional formatting changes a cell’s appearance when its content meets a condition. The value remains Yes or No; Excel applies a fill, font color, or other format while the condition is true. If the value changes, Excel reevaluates the rule and updates the appearance. This is useful for checklists, approval statuses, attendance, survey answers, quality checks, and simple task trackers.

The colors are not built-in meanings that Excel assigns to Yes and No. You choose the format for each condition. Green commonly signals an affirmative or completed status, while red draws attention to a negative or incomplete one, but the rules can use any colors that suit the sheet.

Set up red and green rules without a formula

For a column containing the words Yes and No, the built-in text rules are the quickest setup in Excel for Microsoft 365 on Windows:

Select the cells to format, such as B2:B100. Avoid selecting the header if it should remain uncolored.
Choose Home > Conditional Formatting > Highlight Cells Rules > Text that Contains.
Enter Yes, choose a green format from the available options, or choose Custom Format to set a fill and font color, then confirm.
Repeat the process for No and choose a red format.

Excel creates a separate rule for each value. With the selection B2:B100, the rules apply to those cells and normally extend to existing entries in that selected range. If new entries will be added later, select a larger range in advance or adjust the rule’s Applies to range using Home > Conditional Formatting > Manage Rules.

Text that Contains is convenient, but it looks for the entered text within a cell rather than requiring the whole cell to equal that text. For example, a cell containing “Yes—pending review” can match a rule that looks for Yes. Use formula rules below if only the exact value Yes or No should trigger a color.

Use formula rules for exact Yes and No values

Suppose the status values are in B2:B100, with one status per cell. Select B2:B100, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter the first formula, choose Format, set a green fill, and confirm. Create a second rule with the other formula and a red fill.

With the status values in B2:B100 and B2 as the first cell in the selected range, use this formula for the green Yes rule: =B2="Yes"

With the same layout, use this formula for the red No rule: =B2="No"

The reference B2 is intentional: it is relative, so Excel checks B2 for the first row, B3 for the next, and so on. If the selected range begins somewhere else, use the top-left cell of that selection in the formula. For instance, if the range begins at D5, the test should refer to D5. Excel’s text comparisons in these formulas are not case-sensitive, so yes and YES also satisfy the Yes test. They still need to be the whole cell value; “Yes, thanks” does not equal Yes.

To review or edit the formulas and their ranges, choose Home > Conditional Formatting > Manage Rules. Check the “Applies to” range as well as the formula: a correct formula will not color cells outside the rule’s applied range.

Color a whole row based on its Yes/No status

You can use the status in one column to format other cells on the same row. For example, suppose each record occupies A2:F100 and the Yes/No status is in column D. Select A2:F100, create a formula rule, and set the format to a green fill. The dollar sign fixes the status column at D, while the row number can change as Excel evaluates each row.

With records in A2:F100 and the status in D2:D100, use this formula for rows marked Yes: =$D2="Yes"

With the same layout, use this formula for rows marked No: =$D2="No"

Apply the green format to the first rule and red to the second. If the formula instead uses D2 without the dollar sign before D, Excel may shift the tested column as it applies the rule across A:F. Conversely, locking both the column and row as $D$2 would make every row depend on the status in D2, which is usually not what a row-by-row rule is meant to do.

Make consistent Yes and No entries

Conditional formatting is more reliable when people enter the same two values in a consistent way. A dropdown list can reduce spelling variations such as “Y,” “Yes ” with a trailing space, or “Nope.” To create one in Excel for Microsoft 365 on Windows, select the entry cells and choose Data > Data Validation. In the Settings tab, set Allow to List, enter the permitted options in Source, and confirm. In an English-language setup, a source such as Yes,No creates those two choices; regional list separators can differ.

A dropdown helps prevent unexpected text but does not automatically fix values already in the cells. Existing entries may need to be corrected. Also consider whether the cells contain the text Yes and No or logical values such as TRUE and FALSE. They are different kinds of values: a text rule for Yes will not match TRUE. If the data is genuinely Boolean, write rules for TRUE and FALSE instead, or convert the values to the text labels your workbook expects.

For a shared workbook, explain what each color means and keep the underlying Yes/No text visible. That makes the result understandable when someone prints the sheet, views it with colors disabled, or uses accessibility tools.

Choose colors that remain clear

Color is a fast visual cue, but color alone can be hard to interpret. Some readers have color-vision differences, and a red or green fill may reproduce poorly in grayscale. Keep the Yes or No text in the cell, use readable text contrast, and consider adding a separate status label or symbol when the result is important. Avoid very dark fills with dark text or pale text on a pale fill.

For a deliberate appearance, choose Custom Format in the rule dialog and set the fill and font options. Test the formatting on both values and on a blank cell. You can use different shades, bold text, or borders, but make sure the actual cell content remains easy to read. The conditional format affects display, not the stored value, and changing a fill manually does not change the status.

Common problems and how to fix them

No color appears: Check that the cell exactly meets the rule, that the correct range is selected, and that the rule’s Applies to range includes the cell. In Manage Rules, verify the formula refers to the top-left cell of the applied range.
Unexpected cells change color: A formula may use the wrong relative or absolute references. For a status in D and a row-wide range starting at A2, use $D2 so the column stays fixed while the row adjusts.
Text contains extra words: A Text that Contains rule can match a phrase that includes Yes or No. Replace it with the exact-match formula rules if that is not intended.
A value looks right but does not match: Check for leading or trailing spaces, punctuation, or a different data type. A cell displaying TRUE is not the same as the text Yes. Correct the source value or choose a rule that matches the actual data.
One color seems to override another: Review overlapping rules and their order in Manage Rules. Rules with conflicting formats can produce results that depend on priority; where appropriate, adjust the order or use Stop If True for a rule that should prevent lower-priority rules from applying.
New rows are not formatted: The new cells may fall outside the rule’s Applies to range. Expand that range or include likely future entries when setting up the rules.

Conditional formatting is a display feature, not a data-cleaning tool. It will not make inconsistent entries identical, and it does not change a value just because the cell turns red or green. For a workbook that feeds reports or other formulas, keep the source values standardized as well as the visual rules.

Excel for Mac and Excel for the web

The conditional-formatting idea and formula logic are the same in current Excel for Mac and Excel for the web, although the dialogs and available controls can look different from the Windows desktop steps above. In the Mac desktop app, use the Home tab’s Conditional Formatting controls and create a formula-based rule if you need an exact match or row-wide formatting. In Excel for the web, select the cells and use the Home tab’s Conditional Formatting menu; if a particular rule or formatting control is not available in the web interface, open the workbook in the desktop app to manage it.

When moving a workbook between desktop and web versions, check the result after editing. The rule applies to the workbook data, but available editing options can vary by platform and update. The colors should not be treated as a substitute for the underlying status values, particularly if the workbook will be used in different applications.

Frequently Asked Questions

Can Excel automatically turn Yes green and No red?
Yes. Create two conditional-formatting rules for the status range, choosing green for Yes and red for No. Use built-in text rules for a quick setup or formula rules for exact matches.

Can I color the entire row based on a Yes or No cell?
Yes. Select the full table range and create a formula rule that checks the status column, fixing that column with a dollar sign. For a status in D and a range beginning on row 2, the Yes test is =$D2="Yes".

Will a rule for Yes also match YES or yes?
Excel’s text comparisons in conditional-formatting formulas are case-insensitive, so those capitalization changes match. A formula test for equality still requires the cell’s value to be Yes, rather than a longer phrase containing that word.

Why does my Yes/No formatting not work with a dropdown?
Check that the dropdown’s actual choices match the rule and that the rule applies to the dropdown cells. Also verify that the entries are text rather than Boolean TRUE or FALSE, and look for extra spaces or punctuation.

Does conditional formatting change the cell value?
No. It changes how Excel displays the cell while the condition is true. The stored text or value remains unchanged, so formulas and other workbook features can still read the original entry.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.