How to Black Out an Excel Row in WPS Office When Column A Is NA and Column W Is 1–8
Question details
The user wants to apply conditional formatting in WPS Office Excel so that an entire row is filled black when column A contains NA and column W contains a number from 1 through 8.
- Product
- WPS Office Excel
- Device & OS
- not provided
- Scenario
- The user is setting up a worksheet where rows need to be visually flagged or blacked out based on two column conditions.
- Observed behavior
- The desired behavior is for rows to turn black automatically when column A is NA and column W is between 1 and 8.
Before creating the rule, identify the exact first row and last row of the range you want to format, and make sure the values in column A and column W are consistent, for example column A contains text NA and column W contains numbers rather than text.
Create a Formula-Based Conditional Formatting Rule to Black Out Rows
Use this method when you need the entire row shaded black based on values in columns A and W; the absolute column references keep the rule checking the correct columns as it adjusts for each row.
The formula =AND($A2="NA",$W2>=1,$W2<=8) checks three conditions for each row: column A equals NA, column W is at least 1, and column W is at most 8. The $ signs lock the columns, while the row number updates automatically across the selected range.
Select the full range of rows to format, for example A2:W100, and make sure the active cell is in the first row of the selected range.
In WPS Office Excel, go to the Home tab and click Conditional Formatting > New Rule.
Select "Use a formula to determine which cells to format" and enter =AND($A2="NA",$W2>=1,$W2<=8).
Click Format, open the Fill tab, choose black as the fill color, and confirm with OK.
Click OK again, then check several rows where column A is NA and column W is 1 through 8 to confirm the whole row turns black.
Use WPS Office Excel to Black Out Rows with Conditional Formatting
You can complete this formatting task directly in WPS Office Excel by creating one formula-based conditional formatting rule. WPS Office supports Excel formulas and formatting tools, so you can apply the black row fill without switching to another spreadsheet app.
- 1. Open the workbook in WPS Office Excel: Launch WPS Office Excel and open the workbook that contains the rows you want to format.
- 2. Select the data range: Select the full range of rows where the black fill should appear, such as A2:W100.
- 3. Create the conditional formatting rule: Go to Home > Conditional Formatting > New Rule, choose a formula-based rule, and enter =AND($A2="NA",$W2>=1,$W2<=8).
- 4. Apply black fill and save: Set the fill color to black, confirm the rule, and save the workbook to keep the formatting.
Frequently Asked Questions
Why does my rule fill only one cell instead of the whole row?
Make sure you select the full range before creating the rule and use absolute column references such as $A and $W with a relative row reference such as $A2 and $W2.
What should I change if my data starts in row 5?
Replace 2 with the first row of your selected range, for example =AND($A5="NA",$W5>=1,$W5<=8).
Can I use a different fill color instead of black?
Yes, after clicking Format in the New Formatting Rule dialog, open the Fill tab and choose any color you prefer.
Does this formula treat column W values as numbers?
The formula uses >=1 and <=8, so it works when column W contains numbers. If the entries are stored as text, convert them to numbers or adjust the rule accordingly.
