Author: Oscar Cronquist Article last updated on February 01, 2019

A conditional formatting formula highlights values that only exist in one column. Example, BB and GG exist only in column B.

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 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)

Explaining conditional formatting formula

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

The less than and greater than signs combined means not equal to. The value in cell B3 is compared to values in cell range $D$3:$D$7.

$D$3:$D$7<>B3 is the same as $D$3<>B3, $D$4<>B3, $D$5<>B3, $D$6<>B3 and $D$7<>B3.

The logical expression returns this array {TRUE; TRUE; TRUE; TRUE; TRUE}.

AND($D$3:$D$7<>B3) becomes AND({TRUE; TRUE; TRUE; TRUE; TRUE}) and returns TRUE. Cell B3 is highlighted.

Get Excel *.xlsx

Compare two columns and highlight differences.xlsx