Author: Oscar Cronquist Article last updated on October 02, 2018

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. 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: = COUNTIF($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 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.

Get excel *.xlsx file

Compare two columns and highlight matches.xlsx

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