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
- Select cell range B3:B7
- Go to tab "Home" on the ribbon if you are not there already
- Click on Conditional Formatting button
Click on "New Rule..."
- Click on "Use a formula to determine which cells to format"
Type: = COUNTIF($D$3:$D$7, B3)
- Click on "Format..." button
- Click on "Fill" tab.
- Pick a color
- Click OK
- 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.