Formatting Issues

WPS Spreadsheet: Highlight Blank Cells Red When Another Cell Says Yes

Olivia MillerOlivia Miller Nov 12, 2026 869 views

Question details

The user needs to highlight a blank cell in column X when the corresponding cell in column W contains Yes.

Product
WPS Spreadsheet
Device & OS
not provided
Scenario
Apply conditional formatting to a target range such as X2:X10 using the value in the adjacent column W.
Observed behavior
The goal is for cells in column X to display a red fill only when the matching cell in column W says Yes and the column X cell is blank.
Before you start

Confirm that the target cells are in column X and that the matching condition is in column W. Select the intended range, such as X2:X10, and keep X2 as the active cell before creating the formula rule.

Solution 1Recommended

Create a formula-based rule for blank cells in column X

Use this solution when column W controls the formatting and only blank cells in column X should turn red.

The formula uses relative row references so each cell in the selected X range checks the matching row in column W. The column letters remain fixed while the row number adjusts automatically.

1
Select the target range

In WPS Spreadsheet, select the cells to format, such as X2:X10, and make sure X2 is the active cell.

2
Open conditional formatting

Go to the Home tab, choose Conditional Formatting, and select the option to create a new rule or formula-based rule.

3
Enter the condition formula

Choose the formula rule and enter =AND(W2="Yes",X2=""). Use X2 as the first active cell reference so WPS adjusts the row references throughout the selected range.

4
Set the red appearance

Open the formatting options, choose a red fill for the cells that meet the condition, and click OK or Confirm to save the rule.

Expected result: A cell such as X2 is filled red only when W2 contains Yes and X2 is blank. The same logic applies to every row in the selected range.

Apply Formula-Based Conditional Formatting in WPS Spreadsheet

WPS Spreadsheet lets you create formula-based conditional formatting rules to compare cells across columns and highlight matching blank cells. The familiar spreadsheet interface makes it easy to select a range, define the AND formula, and apply a red fill.

  1. 1. Open a spreadsheet: Launch WPS Office and open the spreadsheet containing columns W and X.
  2. 2. Select the range: Select the target cells in column X, such as X2:X10, with X2 active.
  3. 3. Create the rule: Choose Home > Conditional Formatting, create a formula rule, and enter =AND(W2="Yes",X2="").
  4. 4. Apply the formatting: Set a red fill, confirm the rule, and test a row by entering Yes in column W while leaving the matching X cell blank.
Create conditional-formatting rules based on multiple cell conditions.Highlight blank cells automatically when a related cell contains Yes.Use a familiar interface with flexible formula-based formatting controls.Open and save Microsoft Excel .xlsx files with strong format compatibility.
microsoft office alternative - wps office

Frequently Asked Questions

What formula highlights a blank X cell when the matching W cell says Yes?

Use =AND(W2="Yes",X2="") in a formula-based conditional-formatting rule. Apply it to the selected X range, such as X2:X10.

Where can I create a formula-based conditional-formatting rule in WPS Spreadsheet?

Select the target range, open the Home tab, choose Conditional Formatting, and select the option for creating a new formula-based rule.

Why should X2 be the active cell when creating the rule?

The active cell establishes the starting references. With X2 active, W2 and X2 adjust correctly for each row in the selected range.

Will a cell containing spaces be treated as blank?

No. The condition X2="" generally detects an empty cell, while a cell containing spaces may not qualify. Remove the spaces or use a trimming formula if needed.

Can I change the red fill after creating the rule?

Yes. Open Home > Conditional Formatting, manage or edit the rule, choose a different format, and confirm the changes.