Formatting Issues

How to Use Conditional Formatting for Yes/No Dropdown Lists in Excel

Kushani Nimanthika Oct 12, 2026 871 views

Question details

The user wants to automatically apply background colors to cells based on whether 'Yes' or 'No' is selected from a data validation dropdown list.

How to Use Conditional Formatting for Yes/No Data Validation Values

Product: Spreadsheet
Device & OS: not provided
Scenario: Setting up visual trackers, status boards, or approval workflows where distinct color cues (like green for Yes and red for No) make data reading easier.
Observed behavior: The goal is to automatically change a cell's fill color to green upon selecting 'Yes' and red upon selecting 'No' from an existing dropdown list.

Before you start

Ensure that you have already created a data validation dropdown list containing the options 'Yes' and 'No' in the target cells before applying these color rules.

Solution 1Recommended

Use a Formula for Conditional Formatting

Use a custom formula within conditional formatting to accurately apply colors based on exact text matches.

This method gives you complete control over how rules are applied, especially if you need the formatting to depend on other cells in the future. Ensure you use relative cell references (like A1 instead of $A$1) so the rule applies correctly across multiple rows.

Select the Target Range

Select the cell or range of cells containing your Yes/No data validation dropdown lists.

Create a New Rule

Go to the Home tab on the top ribbon, click on 'Conditional Formatting', and select 'New Rule' from the dropdown menu.

Choose Formula Option

In the dialog box, select 'Use a formula to determine which cells to format'.

Apply Green Fill for Yes

Enter the formula =A1="Yes" (replace A1 with the first cell in your selected range). Click the 'Format' button, navigate to the 'Fill' tab, choose a green color, and click OK.

Apply Red Fill for No

Repeat the process to create a second new rule. Enter the formula =A1="No", click 'Format', choose a red fill color, and click OK to apply.

Use a Formula for Conditional Formatting

Relative References Matter: By using A1 instead of $A$1, the software will automatically adjust the formula references for every other cell in your selected range.

Solution 2

Format Cells Based on Specific Cell Value

Use the built-in cell value condition to format cells containing specific text without manually writing formulas.

Efficient Data Tracking

Color Code Dropdown Lists Easily in WPS Spreadsheet

WPS Spreadsheet fully supports advanced data validation and conditional formatting, allowing you to create visually appealing trackers and reports in seconds. It is a highly compatible, free alternative to Microsoft Excel.

1. Open Worksheet: Launch WPS Spreadsheet and highlight the cells containing your Yes/No dropdown list.
2. Access Conditional Formatting: Navigate to the 'Home' tab, click 'Conditional Formatting', and select 'New Rule'.
3. Add Rule for Yes: Choose 'Format only cells that contain', set the condition to 'Cell Value' 'equal to' and type 'Yes'. Select a green format.
4. Add Rule for No: Create a second rule for 'No' with a red format to complete your visual status tracker.

Fully compatible with Microsoft Excel (.xlsx) conditional formatting rules.Intuitive, easy-to-navigate interface for setting up custom rules.Lightweight software that runs smoothly on Windows, Mac, and Linux.Completely free alternative with robust data analysis tools.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my conditional formatting applying to blank cells?

If you use a formula like =A1<>"Yes", it will treat empty cells as 'not yes' and format them. To avoid formatting blank cells, use exact matches like =A1="No", or add a specific rule to leave blank cells unformatted.

Can I use conditional formatting to change the text color instead of the fill color?

Yes. When setting up your conditional formatting rule, click the 'Format' button and go to the 'Font' tab instead of the 'Fill' tab to select a specific text color.

Will conditional formatting work if I type 'yes' in lowercase?

Yes, Excel and WPS Spreadsheet conditional formatting text rules are generally case-insensitive. Typing 'Yes', 'yes', or 'YES' will all successfully trigger the rule.

How do I remove conditional formatting from my dropdown lists?

Select the cells you want to clear, go to Home > Conditional Formatting > Clear Rules, and choose either 'Clear Rules from Selected Cells' or 'Clear Rules from Entire Sheet'.

Document & File Issues