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. 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.