Formatting Issues

Color an Excel Column A Cell When B:G Values Are All X

Elise WilliamsElise Williams Nov 12, 2026 868 views

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.

Color an Excel Column A Cell When B:G Values Are All 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.
Before you start

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.

Solution 1Recommended

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.

1
Select the target cells in Column A

In Excel, select the range to color, such as A2:A5.

2
Open the conditional-formatting rule dialog

Go to Home > Conditional Formatting > New Rule, then choose Use a formula to determine which cells to format.

3
Enter the row-checking formula

In the formula box, enter =COUNTIF($B2:$G2,"x")=6. Keep the first row number aligned with the first cell in the selected range.

4
Choose the cell color

Select Format, open the Fill tab, choose the color for matching Column A cells, and select OK.

5
Save the rule

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.

Create a COUNTIF conditional-formatting rule for Column A
Adjust the range when needed: If you apply the rule to A2:A100, use the same formula with row 2: =COUNTIF($B2:$G2,"x")=6.
Conditional Formatting in WPS Spreadsheet

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. 1. Select the Column A cells: In WPS Spreadsheet, select the destination range, such as A2:A100.
  2. 2. Open conditional formatting: On the Home tab, select Conditional Formatting and choose New Rule.
  3. 3. Choose a formula rule: Select the option to use a formula to determine which cells to format.
  4. 4. Add the COUNTIF formula: Enter =COUNTIF($B2:$G2,"x")=6, making sure the row number matches the first selected row.
  5. 5. Set the fill and confirm: Choose Format or Fill, select a color, and confirm the rule to highlight qualifying Column A cells.
Create formula-based conditional formatting for row-by-row checks.Use =COUNTIF($B2:$G2,"x")=6 to evaluate all six cells in each row.Open and edit Microsoft Excel .xlsx and .xls workbooks with formatting support.Use a familiar, lightweight spreadsheet interface for Excel-style tasks.
microsoft office alternative - wps office

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.