Conditional Formatting: Compare Two Columns in Excel

Yes. In Excel for Microsoft 365, select the cells you want formatted, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format, and enter a row-by-row comparison such as =A2<>B2. This highlights differences between the values in columns A and B for each row; use =A2=B2 instead to highlight matches.

What comparing two columns means

Conditional formatting changes a cell’s appearance when a rule is true. Comparing two columns usually means checking the values in corresponding rows: A2 against B2, A3 against B3, and so on. A formula-based rule is useful because it evaluates each row automatically and updates the formatting when the compared values change.

First decide what “compare” means for your data. You might want to:

Find rows where two values should agree, such as an expected and received quantity.
Highlight values that differ, such as a current price beside a previous price.
Find entries in one list that do not appear anywhere in another list.
Flag rows where one side is blank and the other contains a value.

The first two are row-by-row comparisons. The third is a list-membership check, which is different: a value in A2 may match an item in B17 even though those cells are not on the same row. Choosing the right comparison prevents a rule from highlighting valid data simply because the lists are arranged differently.

Compare corresponding rows with a formula

For a basic difference check, assume the data begins in row 2, with values in A2:A100 and B2:B100. Select B2:B100, open Home > Conditional Formatting > New Rule, choose Use a formula to determine which cells to format, and enter =A2<>B2. Choose a format, such as a fill color, and confirm the rule. The rule highlights the cells in column B where that row’s A and B values differ.

The formula does not include dollar signs because the references should move as Excel evaluates each row. In the first selected cell, the rule tests A2 against B2; in the next row, it tests A3 against B3. The starting row in the formula must match the top row of the selected range. If your selection begins in row 5, use row 5 references instead.

To highlight matches instead, use the same assumed layout—values in A2:A100 and B2:B100, with B2:B100 selected—and enter =A2=B2. You can apply the rule to column A instead by selecting A2:A100; the comparison still works, but the formatted cells will be in A. A comparison using equals or not-equals is generally not case-sensitive for text, so entries such as “West” and “WEST” count as equal.

Highlight both cells in each differing row

If you want the difference to be visible in both columns, select A2:B100 before creating the rule. With the same layout, enter =$A2<>$B2. The dollar signs lock the column references so the rule always compares column A with column B, while the row number remains relative and changes for each row. Excel then applies the chosen format to both cells in a row whenever their values differ.

This is a common place for reference errors. If you use =A2<>B2 across a two-column selection, Excel adjusts references as it evaluates each cell. That can make the second column compare a different pair of cells than intended. Locking the columns in =$A2<>$B2 keeps the comparison consistent across the selected range. Keep the row number unlocked so each row is checked independently.

Handle blank cells deliberately

A plain not-equal rule can flag a row when one cell is blank and the other is not. That may be exactly what you want when checking for missing data. But when both cells are blank, the comparison normally evaluates as equal, so it is not highlighted as a difference. Decide whether empty rows should be ignored, treated as missing information, or included in the comparison.

To highlight differences only when both cells contain something, assume A2:A100 and B2:B100 hold the data and select B2:B100. Use =AND(A2<>"",B2<>"",A2<>B2). The two nonblank checks prevent a one-sided blank from being colored. This is helpful when the rule is intended to find conflicting completed entries rather than incomplete records.

To flag rows where exactly one of the two cells is blank, use the same layout and selection, with =OR(AND(A2="",B2<>""),AND(A2<>"",B2="")). In Excel, a cell containing a formula that returns an empty string can appear blank and is treated as empty by these comparisons. If a cell contains spaces, however, it is not truly empty; a space is a character. Clean or normalize such data if spaces should count as blanks.

Find values missing from another list

When the lists are not aligned row by row, compare membership rather than corresponding cells. For example, to highlight items in A2:A100 that do not occur anywhere in B2:B100, select A2:A100 and create a formula rule using =COUNTIF($B$2:$B$100,A2)=0. The fixed range keeps the search list in column B as Excel evaluates each selected item in column A. A zero count means the value was not found.

This rule is useful for reconciling customer IDs, product codes, or names when the two lists are sorted differently or have different lengths. To find items in B that are absent from A, select B2:B100 and use =COUNTIF($A$2:$A$100,B2)=0. COUNTIF comparisons are not case-sensitive. If capitalization matters, a case-sensitive membership check needs a different formula and should be tested against the workbook’s data before applying it broadly.

Do not confuse a missing-from-list rule with a same-row difference rule. If A2 contains “P-104” and B2 contains “P-209,” the row-by-row rule marks a difference even if “P-104” appears in B17. The membership rule answers whether a value appears anywhere in the other specified range, not whether it is beside the same value.

Choose the right text and number comparison

Excel’s standard equals and not-equals comparisons treat text without regard to capitalization. For a case-sensitive row comparison, assume the values are in A2:A100 and B2:B100 and select B2:B100. Use =NOT(EXACT(A2,B2)) to highlight rows where the text differs, including differences in letter case. EXACT compares text case-sensitively; it is not a substitute for deciding how numbers, blanks, or extra spaces should be handled.

Leading or trailing spaces can make two entries look identical while the underlying values differ. If those spaces should be ignored, and the data is text in A2:A100 and B2:B100 with B2:B100 selected, use =TRIM(A2)<>TRIM(B2). TRIM removes leading and trailing spaces and reduces repeated internal spaces to single spaces. It does not remove every possible nonprinting character, such as some imported nonbreaking spaces, so imported data may need cleaning before comparison.

Numbers that look alike can also be stored differently, such as a numeric value in one cell and a text string in another. Check the source data and number formats if a rule produces unexpected results. Formatting changes how a value looks but does not always change the value stored in the cell. For dates, for example, compare actual date values rather than relying only on displayed formats; two cells can show different date formats while representing the same date.

Set the rule range and format

The formula and the range in Applies to work together. A formula written for row 2 should generally be paired with an Applies to range that begins on row 2. To inspect or adjust an existing rule in Excel for Microsoft 365 on Windows, open Home > Conditional Formatting > Manage Rules. In the rules manager, choose the relevant worksheet in Show formatting rules for, then review the formula, the Applies to range, and the rule order.

For a useful comparison, choose a format that makes exceptions visible without obscuring the cell contents. A pale fill or a font color is often easier to scan than a very dark fill. If several conditional-formatting rules affect the same cells, their order and the Stop If True setting can affect which formats appear. Review overlapping rules when a comparison seems not to display the expected color.

Excel for Mac has conditional-formatting controls under the Home tab as well, though the dialogs and rule-management layout can differ slightly from Windows. Excel for the web may present a more limited or differently arranged interface. If a rule is difficult to create or troubleshoot there, open the workbook in the desktop version and check the formula and Applies to range.

Common problems and ways to troubleshoot

Every row is highlighted or none are: Check that the formula’s first row matches the top row of the Applies to range. A rule beginning with A2 and B2 will be offset if it applies starting at row 3.
The wrong column is colored: The selected range determines where the format appears. Select one column to highlight one side, or both columns and lock the comparison columns with dollar signs to color the whole differing row.
Values appear the same but are flagged: Look for extra spaces, text-versus-number differences, or underlying values that differ despite similar display formats.
A list check misses expected matches: Confirm that the COUNTIF search range covers the full list and that the compared values are normalized consistently. Extra spaces and different data types can undermine a visual check.
The rule works only for the first row: Check for an accidentally fixed row reference such as $A$2. For a row-by-row comparison, lock columns when needed but leave the row number relative.

Conditional formatting only changes how cells appear; it does not edit, delete, or reconcile the underlying data. For important comparisons, use the color as a review aid and verify the flagged values before making changes. You can also filter a range by cell color to focus on formatted exceptions, but a visible color is not a substitute for checking the comparison logic and source values.

Frequently Asked Questions

How do I highlight differences between two columns in Excel?
For values compared row by row, select the cells to format and create a formula-based rule with =A2<>B2, adjusting the row number to match the top of the selected range. Choose a fill or font format for the differences.

How do I highlight matching values in two columns?
For corresponding rows, use =A2=B2 in a formula-based conditional-formatting rule. This compares A and B on the same row; it does not search the entire second column for a match.

How can I compare two columns when the lists are in a different order?
Use a membership check. To highlight values in A that are absent from B, select the values in column A and use =COUNTIF($B$2:$B$100,A2)=0, changing the range to cover your actual list.

Can conditional formatting compare two columns and highlight both?
Yes. Select both columns across the relevant rows, then use =$A2<>$B2. The locked column references keep the comparison fixed while the relative row reference advances through the selection.

Why does Excel highlight cells that look identical?
The stored values may differ because of spaces, capitalization when using a case-sensitive formula, text-versus-number storage, or different underlying values hidden by number formatting. Inspect the cell contents and choose a comparison rule that matches the data-cleaning requirements.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.