Compare two columns and highlight differences
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
- Select cell range B3:B7
- Go to tab "Home" on the ribbon if you are not there already
- Click on Conditional Formatting button
-
Click on "New Rule..."
- Click on "Use a formula to determine which cells to format"
- Type: =AND($D$3:$D$7<>B3)
- Click on "Format..." button
- Click on "Fill" tab.
- Pick a color
- Click OK
- 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 […]
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 […]
Compare two lists of data: Highlight common records
In this blog post I will demonstrate a conditional formatting formula that will highlight common records in two lists. The […]
Compare tables: Highlight records not in both tables
The image above demonstrates a conditional formatting formula that highlights records that only exist in one table. There are two […]
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 overlapping date ranges using conditional formatting
The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that […]
Highlight records based on overlapping date ranges and a condition
adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]
Highlight dates in a date range
Question: How do I highlight dates that meet criteria using conditional formatting? Table of contents Highlight values in a column […]
This post demonstrates how to highlight records with the closest value to a criterion, you can also choose to highlight […]
This blog post shows you how to easily identify duplicate rows or records in a list. Conditional formatting formula: =COUNTIFS($B$3:$B$15, […]
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
English
Spanish
