Formatting Issues

How to Apply Conditional Formatting to Odd and Even Rows Containing 'X' in Excel

Phi Hung Vo Oct 10, 2026 871 views

Question details

The user needs to apply different conditional formatting colors to odd and even rows in Excel, but only when a target cell within that row contains a specific character, such as the letter "x".

How to Apply Conditional Formatting to Odd and Even Rows Containing 'X' in Excel

Product: Excel
Device & OS: not provided
Scenario: Highlighting alternating rows in a dataset based on a dual condition: the row number (parity) and the specific text content inside one of the row's cells.
Observed behavior: Requires a combined formula in the conditional formatting rules to evaluate both the row's parity and the cell's content simultaneously.

Before you start

Ensure you select the entire data range you want to format before applying the rules. Take note of the specific column letter that contains the "x" criteria so you can adjust the formula accordingly.

Solution 1Recommended

Use AND, MOD, and ROW Functions in Conditional Formatting

By combining the AND function with MOD and ROW, you can create a rule that simultaneously checks if a cell contains "x" and whether the row is even or odd.

The AND function allows Excel to test multiple conditions at once. In this scenario, one condition checks if the target cell has an "x", and the other uses the MOD and ROW functions to determine if the row number is divisible by 2 (even) or has a remainder of 1 (odd).

Select the Data Range

Highlight the entire range of cells or rows you want to apply the formatting to, for example, A1:D100.

Create a New Rule

Navigate to the 'Home' tab on the ribbon, click 'Conditional Formatting', and select 'New Rule' from the dropdown menu.

Select Formula Option

In the New Formatting Rule dialog box, choose 'Use a formula to determine which cells to format'.

Enter the Formula for Even Rows

To format even rows containing an "x" in column A, enter the formula: =AND($A1="x",MOD(ROW(),2)=0). Make sure to adjust $A1 to match your target column and starting row.

Format and Apply

Click the 'Format' button, choose your desired fill color for the even rows, click 'OK', and then 'OK' again to apply the rule.

Repeat for Odd Rows

Repeat steps 2 through 5 to create a second rule for odd rows, but use the formula: =AND($A1="x",MOD(ROW(),2)=1) and select a different fill color.

Use AND, MOD, and ROW Functions in Conditional Formatting

Absolute Column Reference: Using the dollar sign ($) before the column letter (e.g., $A1) is crucial. It locks the reference to that specific column, ensuring the formatting is applied across the entire row rather than just a single cell.

Powerful Data Analysis

Easily Apply Advanced Conditional Formatting in WPS Spreadsheet

WPS Spreadsheet offers a robust, free suite for handling complex data visualization, including advanced formula-based conditional formatting. You can seamlessly use MOD, ROW, and AND functions exactly as you would in Microsoft Excel.

1. Open Your Workbook: Launch WPS Spreadsheet and select the data range you want to highlight.
2. Access Conditional Formatting: Go to the 'Home' tab and click on 'Conditional Formatting' > 'New Rule'.
3. Choose the Formula Option: Select 'Use a formula to determine which cells to format' from the list of rule types.
4. Input the Formulas: Enter =AND($A1="x", MOD(ROW(), 2)=0) and set your preferred cell format. Click 'OK' to save.
5. Add the Odd Row Rule: Add another rule using the formula =AND($A1="x", MOD(ROW(), 2)=1) with a different color to complete the alternating format.

Free to use with full formula and formatting supportFully compatible with Microsoft Excel (.xlsx) formatsIntuitive interface for managing multiple conditional formatting rules

microsoft office alternative - wps office

Frequently Asked Questions

Why is my conditional formatting only highlighting one cell instead of the whole row?

This happens if you forget to use an absolute column reference in your formula. Ensure you place a dollar sign before the column letter (e.g., $A1 instead of A1) to lock the reference to that column.

Does this conditional formatting formula work if my "x" is uppercase or lowercase?

Yes, standard text comparisons in Excel conditional formatting are not case-sensitive. The formula =AND($A1="x",MOD(ROW(),2)=0) will highlight the row whether it contains a lowercase "x" or an uppercase "X". If you need case-sensitivity, you must use the EXACT function: =AND(EXACT($A1,"x"),MOD(ROW(),2)=0).

How do I change the target column in the formatting formula?

Simply replace the $A part of the formula with the letter of the column you want to check. For example, if you are checking for "x" in column C, your formula should be =AND($C1="x",MOD(ROW(),2)=0).

Can I use this method to highlight every third row instead of odd and even?

Yes, you can modify the MOD function to divide by 3 instead of 2. For example, replacing MOD(ROW(),2)=0 with MOD(ROW(),3)=0 will target every third row (row 3, 6, 9, etc.) that also contains an "x".

Document & File Issues