Author: Oscar Cronquist Article last updated on February 01, 2019

A conditional formatting formula highlights values that only exist in one column. Example, BB and GG exist only in column B.

### How to highlight 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: =AND(\$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 =AND(\$B\$3:\$B\$7<>D3)

### Explaining conditional formatting formula

AND(\$D\$3:\$D\$7<>B3)

The less than and greater than signs combined means not equal to. The value in cell B3 is compared to values in cell range \$D\$3:\$D\$7.

\$D\$3:\$D\$7<>B3 is the same as \$D\$3<>B3, \$D\$4<>B3, \$D\$5<>B3, \$D\$6<>B3 and \$D\$7<>B3.

The logical expression returns this array {TRUE; TRUE; TRUE; TRUE; TRUE}.

AND(\$D\$3:\$D\$7<>B3) becomes AND({TRUE; TRUE; TRUE; TRUE; TRUE}) and returns TRUE. Cell B3 is highlighted.