How to Gray Out Entire Rows Based on a CLOSED Status in Excel
Question details
The user needs to automatically apply gray shading to entire rows from A through K whenever the corresponding cell in column G equals CLOSED, using a conditional formatting rule that can handle thousands of rows without a fixed status cell.

- Product
- WPS Office Spreadsheet
- Device & OS
- not provided
- Scenario
- The user manages a large worksheet with status entries in column G and needs rows A through K to visually indicate closed items automatically.
- Observed behavior
- When a row's column G value is CLOSED, every cell from A through K should become grayed out automatically without manual formatting.
Before creating the rule, confirm which row is the first row in your selected range and verify that column G uses the exact text "CLOSED" with the same capitalization.
Apply One Conditional Formatting Formula to the Entire A3:K1000 Range
Use this solution when you want a single conditional formatting rule to gray out each full row automatically based on its own column G value, without creating separate rules for each row.
The formula =($G3="CLOSED") locks the formatting test to column G while leaving the row number relative. When the selected range starts at row 3, each row from 3 to 1000 checks its own G cell; if your range starts at row 2, replace 3 with 2.
Select A3:K1000, or select the range you want to format, starting at the first data row that contains statuses.
Go to the Home tab, click Conditional Formatting, and choose New Rule.
In the New Formatting Rule dialog box, select Use a formula to determine which cells to format.
In the Format values where this formula is true box, type =($G3="CLOSED"). If your selection begins in row 2 instead of row 3, use =($G2="CLOSED").
Click Format, open the Fill tab, choose a gray shading color, and click OK to close the Format Cells dialog.
Click OK in the New Formatting Rule dialog to apply the rule to the selected range.

Create CLOSED Status Row Gray-Out in WPS Office Spreadsheet
WPS Office Spreadsheet handles the same conditional formatting rule and can open, edit, and save Excel workbooks directly. You can gray out entire rows based on a CLOSED status and keep the file compatible with Microsoft Excel.
- 1. Open your workbook: Launch WPS Office Spreadsheet and open the Excel file directly; existing conditional formatting and data remain intact.
- 2. Select the target range: Click the Home tab, then select A3:K1000 or the range you want to format.
- 3. Create the formula rule: Click Conditional Formatting > New Rule, select Use a formula to determine which cells to format, and enter =($G3="CLOSED").
- 4. Apply and save: Choose a gray fill, click OK, and save the workbook in .xlsx format so it remains fully compatible with Microsoft Excel.

Frequently Asked Questions
Why does the formula use $G3 instead of $G$3?
$G3 fixes the status column at G while leaving the row number relative; each row then tests its own G cell. $G$3 would make every row check only G3.
How do I apply this rule to thousands of rows like A3:K10000?
Select the full range before creating the rule, or go to Home > Conditional Formatting > Manage Rules, select the rule, and set the Applies to box to A3:K10000.
Will the formatting update automatically when I change a status to CLOSED?
Yes, conditional formatting recalculates automatically. When any cell in column G becomes CLOSED, the corresponding row through column K updates immediately.
Does the rule distinguish lowercase "closed" from uppercase "CLOSED"?
With =($G3="CLOSED"), Excel and WPS Spreadsheet treat text comparisons as case-insensitive by default, but standardizing values with a data validation list helps avoid mismatches.
How can I remove or change the gray row formatting later?
Go to Home > Conditional Formatting > Manage Rules, select the rule, and choose Edit Rule to change the formula or Delete Rule to remove the gray row effect.




