get-digital-help.com

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.

Download Excel *.xlsx

Compare two columns and highlight differences.xlsx

Related articles

---

Compare two columns and highlight values in common

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 […]

Highlight missing values between to columns

The picture above shows two lists. How do I highlight values in List 1 that are not in List 2? […]

How to highlight differences in price lists

Today I am going to show you how to quickly compare two tables using Conditional Formatting (CF). I am going […]