Color an Excel Column A Cell When B:G Values Are All X
Question details
Apply conditional formatting to cells in column A when all six cells in the same row from columns B through G contain the value x.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user wants to highlight a cell in column A for each row that has x in every cell across columns B to G.
- Observed behavior
- The target cell in column A should receive the selected format only when the corresponding B:G range contains six occurrences of x.
Confirm that the target rows begin at row 2 and that the values in columns B through G are entered consistently as x. Select the Column A cells you want to format before creating the rule.
Create a COUNTIF conditional-formatting rule for Column A
Use this method when a Column A cell should be colored only if all six cells from columns B through G in the same row contain x.
The row number in $B2:$G2 is relative, so Excel evaluates the B:G cells for each row separately. The columns remain fixed, ensuring that the rule always checks the same six columns.
In Excel, select the range to color, such as A2:A5.
Go to Home > Conditional Formatting > New Rule, then choose Use a formula to determine which cells to format.
In the formula box, enter =COUNTIF($B2:$G2,"x")=6. Keep the first row number aligned with the first cell in the selected range.
Select Format, open the Fill tab, choose the color for matching Column A cells, and select OK.
Select OK to create the rule. Excel colors each cell in the applied A range when all six B:G cells on that row equal x.

Use a case-sensitive formula when uppercase and lowercase values must differ
Use this alternative if x and X must be treated as different values, because COUNTIF is not case-sensitive.
Highlight Column A Cells When Every B:G Cell Contains X in WPS Spreadsheet
WPS Spreadsheet can create the same formula-based conditional-formatting rule to highlight a Column A cell when all six cells in columns B through G contain x. Its familiar spreadsheet interface makes it easy to apply the rule to a full list of rows.
- 1. Select the Column A cells: In WPS Spreadsheet, select the destination range, such as A2:A100.
- 2. Open conditional formatting: On the Home tab, select Conditional Formatting and choose New Rule.
- 3. Choose a formula rule: Select the option to use a formula to determine which cells to format.
- 4. Add the COUNTIF formula: Enter =COUNTIF($B2:$G2,"x")=6, making sure the row number matches the first selected row.
- 5. Set the fill and confirm: Choose Format or Fill, select a color, and confirm the rule to highlight qualifying Column A cells.

Frequently Asked Questions
Why does the formula use =6?
Columns B through G contain six cells. =COUNTIF($B2:$G2,"x")=6 returns TRUE only when all six cells contain x.
How do I apply the rule to more rows?
Select a larger applied range, such as A2:A1000, in Home > Conditional Formatting > Manage Rules. Keep the formula starting at the first selected row: =COUNTIF($B2:$G2,"x")=6.
Will the rule color the entire row instead of only Column A?
Not with an applied range of A2:A5 or A2:A100. To color the entire row, set the Applies to range to the row columns you want, such as $A$2:$G$100, while keeping the same formula.
Does COUNTIF distinguish between x and X?
No. COUNTIF treats x and X as the same. If case matters, use =SUMPRODUCT(--EXACT($B2:$G2,"x"))=6 in the conditional-formatting rule.




