This article demonstrates a conditional formatting formula that will highlight the differences between two columns. The image above shows two cell ranges containing values, the first one is in column B and the second one is in column D.

Values "BB", "GG", and "GG" are highlighted, they only exist in column B. Values "DD" and "FF" are highlighted, they only exist in column D.

Conditional formatting is a built-in feature that allows you to format specific cells based on a condition or criteria.

The conditional formatting formula presented in this article highlights values that only exist in one column, in other words, they differ from the values in the other column. You can pick any format or fill color and the formula lets you build advanced criteria that must be met.

1. 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. Press with mouse on the Conditional Formatting button.
4. Press with mouse on "New Rule...".
   
5. Press with mouse on "Use a formula to determine which cells to format".
6. Type: =AND($D$3:$D$7<>B3)
7. Press with mouse on "Format..." button.
8. Press with mouse on "Fill" tab.
9. Pick a color.
10. Press with left mouse button on OK.
11. Press with left mouse button on OK.

Repeat above steps with column D, the formula is =AND($B$3:$B$7<>D3)

2. Explaining conditional formatting formula

Step 1 - Check if cell range D3:D7 is not equal to cell B3

Reference $D$3:$D$7 is an absolute cell reference meaning it is locked, it won't change when the CF moves to the next cell.

The less than and larger than sign combined means not equal to.

$D$3:$D$7<>B3

becomes

$D$3:$D$7<>B3

and returns {TRUE; TRUE; TRUE; TRUE; TRUE}.

Step 2 - Check if all boolean values are TRUE

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

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

becomes

AND({TRUE; TRUE; TRUE; TRUE; TRUE})

and returns TRUE. Cell B3 is highlighted.

3. Get Excel *.xlsx

Compare two columns and highlight differences.xlsx