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 in column D, a conditional formatting formula highlights values in common between column B and D.

=COUNTIF($B$3:$B$7,D3)

How to highlight common 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: = COUNTIF($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 at the top of this article.

Explaining conditional formatting formula

=COUNTIF($D$3:$D$7,B3)

The COUNTIF function counts how many times value in cell B3 is found in cell range $D$3:$D$7. B3 changes to B4 when Excel moves on to next cell below, however, that is not the case with $D$3:$D$7.

The $ dollar signs make this cell reference locked, in other words, it doesn't change.

The Conditional Formatting in Excel interprets all numbers except 0 as TRUE so if the COUNTIF function finds a value twice and returns 2 doesn't matter, it still highlights the cell.

Download excel *.xlsx file

Compare two columns and highlight matches.xlsx

This blog article is one out of five articles on the same subject.