Highlight column differences

To highlight the differences between two columns of data with conditional formatting you can use a simple formula that uses the "not equal to" operator (e.g. ) and mixed references. In the example shown, the formula used to highlight differences in the ranges B2:B11 and C2:C11 looks like this: =$B2$C2 Note: with conditional formatting, it's important that the formula be entered relative to the "active cell" in the selection, which is assumed to be B2 in this case.

April 14, 2025 · 1 min · 91 words · Roy Harper

This means only the row number will change as the formula is evaluated.

TheNOT functionreverses this logic so that the formula returns TRUE only when the values don’t match.

Another approach is to count instances that appear in one range but do not appear in another.

Excel formula: Highlight missing values

For details on this approach, seehighlight missing values.

NOT Function

The Excel NOT function returns the opposite of a given logical or Boolean value.

When given TRUE, NOT returns FALSE.

Excel EXACT function

When given FALSE, NOT returns TRUE.

Use the NOT function to reverse a logical value.

Excel NOT function

NOT Function#

NOT Function