Highlight Cells When a Row Contains X in Excel
Question details
Highlight a cell or range when one or more cells in a specified row contain X.

- 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.
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.
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.
In the worksheet, select N1:N7, or select your own target range starting on the same row as the formula.
Go to Home > Conditional Formatting > New Rule, then choose Use a formula to determine which cells to format.
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.
Select Format, choose a fill color or another style, and click OK to save the rule.
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.

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. Open and select: Open your workbook in WPS Spreadsheets and select N1:N7, or your intended target range.
- 2. Create a rule: On the Home tab, open Conditional Formatting, select New Rule, and choose the option to use a formula.
- 3. Enter the condition: Enter =COUNTIF($B1:$M1,"*X*")>0 to check whether any cell in B1:M1 contains X.
- 4. Set the format: Choose a fill or other format and confirm the rule. Check the applied range in Conditional Formatting if needed.

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.




