How to Apply Excel Conditional Formatting to Entire Rows by Status
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.

- 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.
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.
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.
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.
Go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
Enter =OR($J2="Complete",$J2="Completed"), click Format, choose a blue fill color, and click OK.
Click Conditional Formatting > New Rule again, enter =$J2="Cancelled", choose a grey fill color, and click OK.
Add another rule with =$J2="Failed", choose a red fill color, and click OK to apply all three rules.

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. Open your workbook in WPS Office: Launch WPS Office Spreadsheets and open the workbook that contains the status values in column J.
- 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. 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. Save as an Excel-compatible file: Save the workbook as .xlsx so the conditional formatting rules remain intact when opened in Microsoft Excel.

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.




