Author: Oscar Cronquist Article last updated on November 03, 2021

This article demonstrates a conditional formatting formula that will highlight the differences between two columns. The image above shows two cell ranges containing values, the first one is in column B and the second one is in column D.

Values "BB", "GG", and "GG" are highlighted, they only exist in column B. Values "DD" and "FF" are highlighted, they only exist in column D.

Conditional formatting is a built-in feature that allows you to format specific cells based on a condition or criteria.

The conditional formatting formula presented in this article highlights values that only exist in one column, in other words, they differ from the values in the other column. You can pick any format or fill color and the formula lets you build advanced criteria that must be met.

1. How to highlight values

  1. Select cell range B3:B7.
  2. Go to tab "Home" on the ribbon if you are not there already.
  3. Press with mouse on the Conditional Formatting button.
  4. Press with mouse on "New Rule...".
  5. Press with mouse on "Use a formula to determine which cells to format".
  6. Type: =AND($D$3:$D$7<>B3)
  7. Press with mouse on "Format..." button.
  8. Press with mouse on "Fill" tab.
  9. Pick a color.
  10. Press with left mouse button on OK.
  11. Press with left mouse button on OK.

Repeat above steps with column D, the formula is =AND($B$3:$B$7<>D3)

2. Explaining conditional formatting formula

Step 1 - Check if cell range D3:D7 is not equal to cell B3

Reference $D$3:$D$7 is an absolute cell reference meaning it is locked, it won't change when the CF moves to the next cell.

The less than and larger than sign combined means not equal to.

$D$3:$D$7<>B3

becomes

$D$3:$D$7<>B3

and returns {TRUE; TRUE; TRUE; TRUE; TRUE}.

Step 2 - Check if all boolean values are TRUE

AND($D$3:$D$7<>B3)

AND($D$3:$D$7<>B3)

becomes

AND({TRUE; TRUE; TRUE; TRUE; TRUE})

and returns TRUE. Cell B3 is highlighted.

3. Get Excel *.xlsx

Compare two columns and highlight differences.xlsx

Weekly Blog EMAIL

Welcome! I am Oscar and here to help you out.

Feel free to comment and ask Excel questions.

Make sure you subscribe to my newsletter so you don't miss new blog articles.

Related articles

Compare two columns and highlight values in common

A conditional formatting formula highlights values in column B that also exist in column D. =COUNTIF($D$3:$D$7,B3) The same thing happens […]

Highlight missing values between to columns

The picture above shows two lists. How do I highlight values in List 1 that are not in List 2? […]

How to highlight differences in price lists

Today I am going to show you how to quickly compare two tables using Conditional Formatting (CF). I am going […]