Formatting Issues

How to Create Excel Conditional Formatting Rules for Two Columns

Maira Mehtab Sep 22, 2026 868 views

Question details

The user wants to visually compare values between two adjacent Excel columns by automatically applying distinct colors based on whether the value is larger, smaller, or equal.

Product: Excel
Device & OS: not provided
Scenario: Comparing numeric or textual data points across two adjacent columns to quickly identify variances.
Observed behavior: Values in the columns need to be color-coded dynamically: green for the larger value, red for the smaller value, and yellow if they are perfectly equal.

Before you start

Ensure your selected data range does not contain merged cells, as this can cause conditional formatting references to break or apply inconsistently across rows.

Solution 1Recommended

Apply Formula-Based Formatting to Individual Columns

Set up separate conditional formatting rules for each column to independently define greater than, less than, and equal to conditions.

This approach requires creating three rules for the first column and three rules for the second column. It is highly reliable because it explicitly defines the relationship between specific cells on the same row.

Select the first column

Highlight the data range in Column A (for example, A2:A50) where you want the colors applied.

Create a new rule

Navigate to the Home tab, click on Conditional Formatting, and choose New Rule. Select 'Use a formula to determine which cells to format'.

Apply formula for larger values

Enter the formula `=A2>B2`, click on the Format button, choose a Green fill color, and click OK.

Add rules for smaller and equal values

Repeat the New Rule process twice for Column A. Use `=A2<B2` and format it with Red, then use `=A2=B2` and format it with Yellow.

Repeat for the second column

Select the data range in Column B (e.g., B2:B50) and repeat the entire process using the inverse formulas: `=B2>A2` for Green, `=B2<A2` for Red, and `=B2=A2` for Yellow.

Row References: Always ensure the row number in your formula matches the very first row of your highlighted selection. If you select A2:A50, your formula must start with row 2.

Solution 2

Use MAX and MIN Formulas for the Entire Data Range

Apply a single set of rules to both columns simultaneously by using the MAX and MIN functions to determine the higher and lower values.

Efficient Data Analysis

Easily Highlight Data Variations in WPS Spreadsheet

You can quickly apply advanced conditional formatting rules to compare multiple columns using WPS Spreadsheet. It provides a user-friendly interface for managing complex rules without slowing down your computer.

1. Open data in WPS Spreadsheet: Launch WPS Office and open the spreadsheet containing the columns you wish to compare.
2. Select the data range: Highlight the cells in the columns you want to apply the color-coding to.
3. Access Conditional Formatting: Go to the Home tab on the top ribbon, click Conditional Formatting, and select New Rule.
4. Enter the comparison formula: Choose the formula option and input your desired comparison (such as `=A2>B2`), then set the cell fill color.
5. Manage your rules: If you need to adjust or add conditions, use the Manage Rules option under Conditional Formatting to view all active rules in your worksheet.

Seamless compatibility with Microsoft Excel (.xlsx) formats and conditional formatting rules.Intuitive Conditional Formatting manager for easily editing or deleting multiple rules.Lightweight software that handles large datasets without lagging.Free to use with comprehensive data analysis tools.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my conditional formatting applying colors to the wrong rows?

This usually happens when the row references in your formula do not match the first row of your selected data range. Ensure that if your selection starts at row 2, your formula strictly uses row 2 (e.g., `=A2>B2`).

Should I use absolute references like $A$2 in my conditional formatting formula?

For comparing row-by-row across two columns, you should use relative references for the rows (like `$A2` or `A2`). Absolute references (`$A$2`) will lock the formula to a single cell, applying that single cell's result to your entire selected range.

How do I clear conditional formatting rules if I make a mistake?

Select the cells with the incorrect formatting, go to the Home tab, click Conditional Formatting, hover over Clear Rules, and choose 'Clear Rules from Selected Cells'.

Will this conditional formatting update automatically if I change the data?

Yes, formula-based conditional formatting is highly dynamic. If you change a value in either column, the background colors will automatically update based on the new comparison.

Document & File Issues