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. Click on Conditional Formatting button
  4. Click on "New Rule..."
  5. Click on "Use a formula to determine which cells to format"
  6. Type: =AND($D$3:$D$7<>B3)
  7. Click on "Format..." button
  8. Click on "Fill" tab.
  9. Pick a color
  10. Click OK
  11. Click OK

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

Explaining conditional formatting formula


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.

Download Excel *.xlsx

Compare two columns and highlight differences.xlsx