Formatting Issues

How to Conditional Format an Excel Column When Two Other Columns Differ

Camila Milosovich Oct 10, 2026 871 views

Question details

The user needs to apply an Excel conditional formatting rule to column I that triggers only if the values in columns J and K on the same row are different.

How to Conditional Format a Column When Two Other Columns Differ in Excel

Product: Excel
Device & OS: not provided
Scenario: Highlighting specific cells in one column based on a comparison of values in two other columns, often as part of a larger, complex rule.
Observed behavior: The user has an existing complex conditional formatting rule and needs to integrate a new inequality condition to compare column J and column K.

Before you start

Ensure your dataset does not contain hidden trailing spaces in columns J and K, as these can cause cells that look visually identical to be evaluated as different by the formula.

Solution 1Recommended

Add an Inequality Condition to the Formatting Rule

Use the not-equal operator (<>) within an AND function to check if values in two columns differ before applying the cell format.

To conditionally format a cell based on multiple criteria, including whether two other columns differ, you can incorporate the inequality operator into an AND statement. This ensures all conditions, including the column comparison, must be true for the format to apply.

Select the Target Range

Highlight the cells in Column I (for example, I8:I1000) where you want the formatting to appear.

Open Conditional Formatting

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

Choose Formula Option

In the dialog box, select 'Use a formula to determine which cells to format'.

Enter the Custom Formula

Input your formula using the AND function. To add to an existing rule, append $J8<>$K8. A basic comparison looks like =AND($J8<>$K8), while a complex one looks like =AND(existing_conditions..., $J8<>$K8).

Set the Format

Click the 'Format' button, choose your desired fill color or font style, and click 'OK' twice to apply the rule.

Add an Inequality Condition to the Formatting Rule

Relative Row References: Ensure you use mixed references like $J8 instead of absolute references like $J$8. This allows the rule to adjust dynamically for each row in your selected range.

Advanced Conditional Formatting

Use WPS Spreadsheet for Complex Conditional Formatting

WPS Spreadsheet fully supports complex conditional formatting rules, including custom AND/OR formulas, making it incredibly easy to highlight data based on multiple column conditions.

1. Select Your Data: Open your workbook in WPS Spreadsheet and highlight the target cells in Column I.
2. Access Conditional Formatting: Navigate to the Home tab, click 'Conditional Formatting', and choose 'New Rule'.
3. Apply Formula: Select 'Use a formula to determine which cells to format' and input the comparison formula =AND($J8<>$K8).
4. Customize Appearance: Set your preferred fill color or font format, then click 'OK' to apply the formatting to your sheet.

Fully compatible with Microsoft Excel conditional formatting formulasIntuitive rule manager for editing complex criteria effortlesslyLightweight software with fast performance on large datasetsFree to use for everyday spreadsheet tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why does my conditional formatting formula highlight the wrong rows?

This usually happens if the row reference in your formula does not match the first row of your selected range. Always ensure the row number in your formula (e.g., $J8) matches the top row of your current selection (e.g., row 8).

Can I format an entire row instead of just one column?

Yes. Select the entire data range (e.g., A8:K1000) instead of just Column I, and use the exact same formula with the column letters locked (e.g., =$J8<>$K8). The dollar signs ensure every cell in the row looks at columns J and K.

How do I handle blank cells being evaluated as different from zero?

Spreadsheet software may treat blank cells and zeroes differently depending on the context. To ignore blanks entirely, add another condition to your AND statement, such as $J8<>"" or $K8<>"".

What if I want to format the column when J and K are exactly the same?

Simply replace the not-equal operator (<>) with the equal sign (=) in your formula, making it =$J8=$K8.

Document & File Issues