A conditional formatting formula highlights values in column B that also exist in column D.


The same thing happens in column D, a conditional formatting formula highlights values in common between column B and D.


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


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.