Formula Errors

How to Compare Adjacent Cells Using Excel Conditional Formatting

Olivia Miller Oct 19, 2026 868 views

Question details

The user wants to automatically compare values in adjacent columns row by row using a single conditional formatting rule.

How to Compare Adjacent Cells Using Excel Conditional Formatting

Product: Excel
Device & OS: not provided
Scenario: Setting up a dynamic visual indicator that highlights specific cells in a column based on how they compare to the neighboring cell in the same row.
Observed behavior: Instead of creating a separate rule for every single row, a single rule needs to be established that uses relative cell references to automatically adjust for the entire selected data range.

Before you start

Ensure your data is organized into clear columns without merged cells, and determine the exact logic for your comparison (such as greater than, less than, or equal to the adjacent value).

Solution 1Recommended

Apply a Formula-Based Conditional Formatting Rule to the Entire Range

This is the most efficient method, applying a single formatting rule over a highlighted range and relying on Excel's relative row referencing to compare values row by row.

By utilizing mixed references (locking the column but leaving the row relative), Excel understands that the formula needs to adapt dynamically as it checks each row within your selected range.

Select the target range

Highlight the entire range of cells you want to format. For example, select A1:A10.

Open Conditional Formatting

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

Choose the formula option

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

Enter the comparison formula

In the formula bar, type your comparison logic. To highlight cells in column A that are greater than column B, type =A1>B1 or =$A1>$B1. Ensure the row number matches the first row of your selection.

Set the desired format

Click the Format button, choose your preferred fill color, font style, or borders, then click OK to apply the rule.

Apply a Formula-Based Conditional Formatting Rule to the Entire Range

Relative Referencing: Because the row numbers in the formula do not have dollar signs (e.g., $A1), Excel automatically increases the row number (e.g., $A2>$B2) for each subsequent row in the selection.

Solution 2

Format a Single Cell and Use Paste Special

If you already have a working conditional formatting rule in the first cell, you can quickly copy its format to the remaining cells in the column.

Efficient Spreadsheet Management

Easily Compare Cells with WPS Spreadsheet

WPS Office offers a robust Spreadsheet application that fully supports advanced conditional formatting formulas, making it simple to analyze and highlight data based on adjacent cell comparisons.

1. Highlight your data: Open your spreadsheet in WPS Office and select the column range you wish to conditionally format.
2. Access Conditional Formatting: Go to the Home tab, click on Conditional Formatting, and choose New Rule.
3. Insert the comparison formula: Select 'Use a formula to determine which cells to format', enter your logical comparison (like =$A1>$B1), and click Format to pick a highlight color.
4. Apply and analyze: Click OK. WPS Spreadsheet will instantly highlight all cells meeting the condition row by row.

100% compatible with Microsoft Excel (.xlsx, .xls) filesFull support for custom conditional formatting rules and relative formulasLightweight, fast, and completely free to downloadFamiliar tabbed user interface for seamless workflow migration

microsoft office alternative - wps office

Frequently Asked Questions

Why is my conditional formatting highlighting the wrong rows?

This commonly occurs when the row referenced in your formula doesn't match the top row of your selected range. For example, if you highlight A2:A10 but your formula says =A1>B1, the formatting will be offset by one row. Always ensure the formula matches the active (top) cell of your selection.

How do I lock the column reference in the conditional formatting formula?

You can lock a column by placing a dollar sign ($) directly in front of the column letter in your formula, such as =$A1. This mixed reference tells the spreadsheet to always look at Column A, even if the rule evaluates multiple columns in a range, while allowing the row number to change.

Can I highlight both adjacent cells if their values match?

Yes. Select both columns (e.g., A1:B10) and use a formula like =$A1=$B1. By locking the columns with the dollar sign, the rule checks if column A equals column B for that specific row, and if true, applies the highlight to both cells in that row.

Excel & Data Problems