Formatting Issues

How to Apply Excel Conditional Formatting to Entire Rows by Status

Natalie TaylorNatalie Taylor Nov 13, 2026 869 views

Question details

The user wants to apply conditional formatting to an entire row based on status values in column J, so Complete, Completed, Cancelled, and Failed rows receive different fill colors.

How to Apply Excel Conditional Formatting to Entire Rows by Status
Product
WPS Office Spreadsheets (Excel-compatible)
Device & OS
not provided
Scenario
Coloring full spreadsheet rows by status for easier tracking and visual scanning.
Observed behavior
Rows need to change fill color automatically based on the status value entered in column J.
Before you start

Confirm that the status values in column J are exactly Complete, Completed, Cancelled, or Failed, and identify the first data row of the range because the formula uses that row as a relative reference.

Solution 1Recommended

Use Formula-Based Conditional Formatting to Color Entire Rows by Status

Use this solution when you want the whole row to change fill color automatically whenever column J contains Complete, Completed, Cancelled, or Failed.

The formulas lock column J with an absolute reference, while the row number remains relative. This lets each row be evaluated against its own status value, so the formatting moves correctly from row to row.

1
Select the data range

Select the data range that contains the rows you want to format, starting with the first data row, for example A2:M100. Make sure the first cell in the selection is the active cell.

2
Open a new conditional formatting rule

Go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.

3
Create the blue rule for Complete and Completed

Enter =OR($J2="Complete",$J2="Completed"), click Format, choose a blue fill color, and click OK.

4
Create the grey rule for Cancelled

Click Conditional Formatting > New Rule again, enter =$J2="Cancelled", choose a grey fill color, and click OK.

5
Create the red rule for Failed

Add another rule with =$J2="Failed", choose a red fill color, and click OK to apply all three rules.

Use Formula-Based Conditional Formatting to Color Entire Rows by Status
Formatting Applied: After you finish, each row changes fill color automatically when the value in column J is updated or changed.
WPS Office Excel formatting

Highlight Entire Rows by Status with WPS Office Conditional Formatting

WPS Office Spreadsheets supports formula-based conditional formatting, so you can apply the same Complete, Completed, Cancelled, and Failed row colors without complex macros or VBA. The workflow follows Excel’s familiar Home > Conditional Formatting path and keeps your workbook compatible with Microsoft Excel formats.

  1. 1. Open your workbook in WPS Office: Launch WPS Office Spreadsheets and open the workbook that contains the status values in column J.
  2. 2. Select the data range: Select the cells you want to format, keeping the first data row active so the formula row reference is correct.
  3. 3. Apply the three formatting rules: Go to Home > Conditional Formatting > New Rule > Use a formula, then create the blue, grey, and red rules for Complete/Completed, Cancelled, and Failed.
  4. 4. Save as an Excel-compatible file: Save the workbook as .xlsx so the conditional formatting rules remain intact when opened in Microsoft Excel.
Automatically color entire rows based on column J status valuesFull .xlsx and .xls compatibility with Microsoft ExcelNo VBA or complex scripting requiredFree and lightweight WPS Office spreadsheet toolFamiliar Excel-style conditional formatting workflow
microsoft office alternative - wps office

Frequently Asked Questions

Why does the formula use $J2 instead of J2?

The $J locks the column to J while the row number remains relative. This means each row is tested against its own value in column J, allowing the formatting to move down through the selected range.

Can I use a different status column instead of column J?

Yes. Replace J in the formula with the letter of your status column, and adjust the row number to match the first row in your selected data range.

Can I combine Complete and Completed in a single formatting rule?

Yes. Use =OR($J2="Complete",$J2="Completed") to highlight both status values with the same fill color.

How do I edit or remove one of these conditional formatting rules?

Go to Home > Conditional Formatting > Manage Rules, select the rule you want to change, and choose Edit Rule or Delete Rule.

Will these conditional formatting rules work when I open the file in Microsoft Excel?

Yes. The formula-based conditional formatting rules are saved in the workbook as part of the .xlsx format, so they work in both WPS Office and Microsoft Excel.