Formatting Issues

WPS Spreadsheet: Highlight an Entire Row When All Cells Are Submitted

Chanuka GeekiyanageChanuka Geekiyanage Nov 12, 2026 868 views

Question details

The user needs to highlight an entire spreadsheet row after every cell in columns O through X contains the required submission marker.

WPS Spreadsheet: Highlight an Entire Row When All Cells Are Submitted
Product
WPS Spreadsheet
Device & OS
not provided
Scenario
The worksheet uses columns O through X to track submission status, and completed rows should be visually marked with a green fill.
Observed behavior
The row is not automatically highlighted based on whether all cells from O through X contain x.
Before you start

Identify the first data row and the last row that should be formatted. Select the full row area to be highlighted, starting at row 2, and make sure the active cell in the selection is in row 2.

Solution 1Recommended

Create a formula-based rule to highlight completed rows

Use this solution when a row should turn green only after every cell from O through X contains x.

Apply the conditional formatting rule to the columns that make up the entire row, such as A2:X1000. The relative row reference 2 changes automatically for each subsequent row, while the O:X column range remains fixed.

1
Select the row range

In WPS Spreadsheet, select the rows and columns to format, such as A2:X1000, beginning with row 2.

2
Open conditional formatting

Go to the Home tab, select Conditional Formatting, and click New Rule.

3
Choose a formula rule

Select Use a formula to determine which cells to format, then enter =COUNTIF($O2:$X2,"x")=COLUMNS($O2:$X2).

4
Set the row color

Click Format, open the Fill tab, choose a green fill, and click OK.

5
Apply the rule

Click OK again to create the rule. Each selected row is highlighted when all cells from O through X contain x.

Check the marker text: The formula checks for a lowercase x. If your worksheet uses another marker, replace "x" in the formula with the exact required text.

Highlight Completed Submission Rows in WPS Spreadsheet

WPS Spreadsheet lets you create formula-based conditional formatting rules that evaluate every status cell in a row and apply a fill color across the selected row when all required markers are present.

  1. 1. Select the data area: Select the complete row range to format, beginning with the first data row, such as A2:X1000.
  2. 2. Create a new rule: Open Home > Conditional Formatting > New Rule in WPS Spreadsheet.
  3. 3. Enter the completion formula: Choose Use a formula to determine which cells to format and enter =COUNTIF($O2:$X2,"x")=COLUMNS($O2:$X2).
  4. 4. Choose a fill color: Click Format, select a green fill on the Fill tab, and confirm with OK.
  5. 5. Verify the result: Enter x in each cell from O through X in a test row and confirm that the selected row changes to green.
Automatically highlights a full row when all cells in columns O through X contain x.Uses a clear COUNTIF and COLUMNS formula that can be adjusted for different status ranges.Supports familiar spreadsheet tools such as Home > Conditional Formatting > New Rule.Opens and saves Microsoft Excel .xlsx files with broad format compatibility.
microsoft office alternative - wps office

Frequently Asked Questions

What formula highlights a row when every cell from O through X contains x?

Use =COUNTIF($O2:$X2,"x")=COLUMNS($O2:$X2) in a formula-based conditional formatting rule. The row number 2 should match the first row in the selected formatting range.

How do I apply the rule to the entire row instead of only columns O through X?

Select the full area you want colored, such as A2:X1000, before opening Home > Conditional Formatting > New Rule. Keep the formula references focused on $O2:$X2 so the status cells control the fill for the whole selected row.

Can I use a different submission marker instead of x?

Yes. Replace "x" in the formula with the exact marker used in your sheet, such as "Submitted". Text matching may depend on the capitalization and spacing of the entered value.

Why is a row not highlighted when some cells look completed?

Check that every cell from O through X contains the exact required marker and that the conditional formatting range starts on the same row referenced in the formula. Extra spaces or different text can prevent COUNTIF from matching.