Formatting Issues

How to Gray Out Entire Rows Based on a CLOSED Status in Excel

Anum MushtaqAnum Mushtaq Nov 13, 2026 868 views

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.

How to Gray Out Entire Rows Based on a CLOSED Status in Excel
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 you start

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.

Solution 1Recommended

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.

1
Select the full data range

Select A3:K1000, or select the range you want to format, starting at the first data row that contains statuses.

2
Open a new conditional formatting rule

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

3
Choose the formula rule type

In the New Formatting Rule dialog box, select Use a formula to determine which cells to format.

4
Enter the row-based formula

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").

5
Set the gray fill

Click Format, open the Fill tab, choose a gray shading color, and click OK to close the Format Cells dialog.

6
Confirm and apply

Click OK in the New Formatting Rule dialog to apply the rule to the selected range.

Apply One Conditional Formatting Formula to the Entire A3:K1000 Range
Rule applied: When a cell in column G changes to CLOSED, the entire row from column A through column K immediately receives the gray fill.
WPS Office Spreadsheet

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. 1. Open your workbook: Launch WPS Office Spreadsheet and open the Excel file directly; existing conditional formatting and data remain intact.
  2. 2. Select the target range: Click the Home tab, then select A3:K1000 or the range you want to format.
  3. 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. 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.
Automatically grays out entire rows A through K when column G is CLOSEDSingle rule works across thousands of rows without fixed cell referencesFull compatibility with Microsoft Excel .xlsx and .xls filesFree, lightweight spreadsheet app with a familiar ribbon-style interface
microsoft office alternative - wps office

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.