Function Problems

How to Highlight the Larger Value Between Two Excel Columns

Camila Milosovich Oct 16, 2026 869 views

Question details

The user needs to compare two Excel columns row by row and dynamically highlight the larger value in one of the columns.

How to Highlight the Larger Value Between Two Excel Columns

Product: Excel
Device & OS: not provided
Scenario: Comparing two lists of data to visually identify which column contains the greater value for each corresponding row.
Observed behavior: A cell in Column A should be highlighted in a specific color if its value is greater than the adjacent cell in Column B.

Before you start

Ensure your data is organized in adjacent columns without any merged cells in the comparison range, as merged cells can prevent conditional formatting rules from adjusting row numbers correctly.

Solution 1Recommended

Use a Formula in Conditional Formatting to Highlight the Larger Value

Apply a custom conditional formatting rule using relative references to compare values row by row.

Using relative references (without dollar signs) is critical for this method. It allows Excel to adjust the row numbers automatically as it applies the formatting down the column.

Select the Data Range

Select the required range in Column A, such as A2:A6. Ensure that the top cell (A2) is the active cell in your selection.

Open Conditional Formatting

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

Enter the Comparison Formula

Choose 'Use a formula to determine which cells to format'. In the formula box, enter =A2>B2 to check if the value in Column A is greater than the value in Column B.

Apply Cell Formatting

Click the 'Format' button, navigate to the Fill tab, select a green fill color (or your preferred color), and click OK to apply the rule.

Use a Formula in Conditional Formatting to Highlight the Larger Value

Automatic Row Adjustment: Because the formula uses relative references (=A2>B2 instead of =$A$2>$B$2), Excel will automatically evaluate A3>B3, A4>B4, and so on for the rest of the selected range.

Solution 2

Apply Formatting to Highlight Smaller or Equal Values

Create an additional rule to highlight cells in Column A that are less than or equal to Column B.

Solution 3

Format Column B Based on Column A Values

Use the standard 'Cell Value' conditional formatting rule if you prefer to format the second column when it is smaller than the first column.

Work efficiently with WPS Office

Easily Highlight Excel Data with WPS Office

WPS Spreadsheet provides powerful and intuitive conditional formatting tools that perfectly support complex custom formulas, allowing you to highlight, compare, and analyze data quickly.

1. Open Your Spreadsheet: Launch WPS Spreadsheet and open the file containing the two columns you wish to compare.
2. Select the Data Range: Click and drag to select the data in the primary column you want to highlight.
3. Apply Conditional Formatting: Navigate to the 'Home' tab, click 'Conditional Formatting', and select 'New Rule'.
4. Input the Rule: Choose the formula option, input your relative comparison formula (e.g., =A2>B2), pick a highlight color, and click OK.

Fully compatible with Microsoft Excel (.xlsx, .xls) files and conditional formatting rules.Intuitive Conditional Formatting menu for quick and easy data visualization.Lightweight installation with blazing-fast performance on any device.Completely free and powerful alternative for everyday spreadsheet tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my conditional formatting highlighting the wrong rows?

This usually happens if you use absolute references (like =$A$2>$B$2) instead of relative references (like =A2>B2). Ensure there are no dollar signs before the row numbers so the formula can adjust dynamically for each row in your selected range.

Can I highlight the entire row instead of just the cell?

Yes. To highlight the entire row, select the entire data table (e.g., A2:Z10) before creating the rule. Then, use an absolute column reference in your formula, like =$A2>$B2. The dollar signs lock the comparison to columns A and B, but allow the formatting to apply across all selected columns.

Can I compare data across two different worksheets?

Yes, you can use a formula in conditional formatting that references another sheet. For example, enter =A2>Sheet2!B2 in your conditional formatting rule. Ensure both ranges match in size and layout for an accurate row-by-row comparison.

Excel & Data Problems