Color an Excel Column Based on Yes or No Values in Three Columns
Question details
The user wants to color cells in one column according to yes or no combinations in three other columns.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Apply different fill colors to a target column based on the values in three condition columns on the same row.
- Observed behavior
- Each target cell should display the color assigned to its row's yes or no combination.
Identify the target range and the three columns containing the yes or no values. The example below uses D2:D1000 as the target and columns E, F, and G as the condition columns.
Create Excel conditional formatting rules for each combination
Use a formula-based rule for each yes or no combination that needs a distinct color.
In Excel, select D2:D1000, or select your own target range starting with its first data row.
Go to Home > Conditional Formatting > New Rule. Select Use a formula to determine which cells to format.
Enter =AND($E2="yes",$F2="yes",$G2="yes"). The dollar signs fix the condition columns, while the row number changes for each target cell.
Select Format, open the Fill tab, choose a color, and click OK to save the format and rule.
With the target range selected, create another rule for each combination you want to color. For example, use =AND($E2="yes",$F2="no",$G2="yes") for yes/no/yes, and choose a different fill color.

Color cells by yes or no combinations in WPS Spreadsheets
Use formula-based conditional formatting in WPS Spreadsheets to assign fill colors to a target column based on values in three other columns.
- 1. Open the spreadsheet: Open your workbook in WPS Spreadsheets and select the target range, such as D2:D1000.
- 2. Start a conditional formatting rule: Go to Home > Conditional Formatting > New Rule and choose the option to use a formula to determine which cells to format.
- 3. Enter the formula and color: Enter =AND($E2="yes",$F2="yes",$G2="yes"), select a fill color in the format settings, and save the rule.
- 4. Add other combinations: Use Conditional Formatting > New Rule to add formulas for the other yes or no combinations, selecting a fill color for each.

Frequently Asked Questions
How many rules do I need for three yes or no columns?
There are eight possible combinations. Create a separate conditional formatting rule for each combination you want to display in a distinct color.
Why do the formulas use $E2 instead of E2?
The dollar sign locks the reference to column E, while the row number remains relative. This lets the rule check the corresponding row for each cell in the target range.
What if my target range starts on row 5?
Select the range starting at row 5 and use row 5 in the formula, for example =AND($E5="yes",$F5="yes",$G5="yes").
Will blank cells match a yes or no rule?
No. A formula that checks for "yes" or "no" does not match a blank cell. Add a separate rule if you want to format rows with blanks.




