Function Problems

Highlight Cells When a Row Contains X in Excel

Chanuka GeekiyanageChanuka Geekiyanage Nov 18, 2026 869 views

Question details

Highlight a cell or range when one or more cells in a specified row contain X.

Highlight Cells When a Row Contains X in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
Use conditional formatting to check cells in each row and highlight a separate cell or range when the condition is met.
Observed behavior
The original COUNTIFS formula uses an invalid multi-range reference.
Before you start

Identify the continuous range to check in each row and the cells to highlight. The examples below check B1:M1 and apply formatting to N1:N7, with the first formula row matching the first row of the formatting range.

Solution 1Recommended

Create a COUNTIF Conditional Formatting Rule in Excel

Use a continuous row range to highlight cells when at least one checked cell contains X.

COUNTIF is case-insensitive. The wildcard criterion "*X*" matches X anywhere in a cell; use "X" instead if the cell must contain only X.

1
Select the cells to highlight

In the worksheet, select N1:N7, or select your own target range starting on the same row as the formula.

2
Create a formula rule

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

3
Enter the formula

Enter =COUNTIF($B1:$M1,"*X*")>0 to match any cell containing X. For cells that equal X exactly, enter =COUNTIF($B1:$M1,"X")>0 instead.

4
Choose the formatting

Select Format, choose a fill color or another style, and click OK to save the rule.

5
Check the applied range

Go to Home > Conditional Formatting > Manage Rules and confirm that Applies to is =$N$1:$N$7, or change it to your intended target range.

Create a COUNTIF Conditional Formatting Rule in Excel
Match exactly one cell: To format only when exactly one checked cell equals X, use =COUNTIF($B1:$M1,"X")=1. This counts matching cells, not individual occurrences of X.
Conditional formatting in WPS Spreadsheets

Highlight Rows with X in WPS Spreadsheets

Use a COUNTIF formula in WPS Spreadsheets conditional formatting to check each row and highlight a separate target range.

  1. 1. Open and select: Open your workbook in WPS Spreadsheets and select N1:N7, or your intended target range.
  2. 2. Create a rule: On the Home tab, open Conditional Formatting, select New Rule, and choose the option to use a formula.
  3. 3. Enter the condition: Enter =COUNTIF($B1:$M1,"*X*")>0 to check whether any cell in B1:M1 contains X.
  4. 4. Set the format: Choose a fill or other format and confirm the rule. Check the applied range in Conditional Formatting if needed.
Highlight target cells when a checked row contains XOpen and edit Microsoft Excel XLSX and XLS filesUse familiar spreadsheet formulas and formatting controls
microsoft office alternative - wps office

Frequently Asked Questions

How do I match a cell that contains only X?

Use =COUNTIF($B1:$M1,"X")>0 in the conditional formatting formula. Unlike "*X*", the criterion "X" does not match longer text containing X.

How can I format a cell only when exactly one cell contains X?

Use =COUNTIF($B1:$M1,"*X*")=1 to count cells containing X, or use =COUNTIF($B1:$M1,"X")=1 for an exact cell match.

Why does the formula use dollar signs before B and M?

In $B1:$M1, the dollar signs keep the checked columns fixed while the row number changes for each row in the applied range.

Where do I change the cells that receive the formatting?

In Excel, go to Home > Conditional Formatting > Manage Rules and edit the Applies to range. Keep the formula's row number aligned with the first row of that range.