## Compare two columns and highlight values in common

*Article last updated on December 21, 2017*

A conditional formatting formula highlights values in column B that also exist in column D.

The same thing happens in column D, a conditional formatting formula highlights values in common between column B and D.

### How to highlight common 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: = COUNTIF($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 at the top of this article.

### Explaining conditional formatting formula

The COUNTIF function counts how many times value in cell B3 is found in cell range $D$3:$D$7. B3 changes to B4 when Excel moves on to next cell below, however, that is not the case with $D$3:$D$7.

The $ dollar signs make this cell reference locked, in other words, it doesn't change.

The Conditional Formatting in Excel interprets all numbers except 0 as TRUE so if the COUNTIF function finds a value twice and returns 2 doesn't matter, it still highlights the cell.

### Download excel *.xlsx file

Compare two columns and highlight matches.xlsx

*This blog article is one out of five articles on the same subject.*

**Filter values existing in range 1 but not in range 2 using array formula in excel****Filter common values between two ranges using array formula in excel****How to remove common values between two columns****How to find common values from two lists****Highlight common values in two lists using conditional formatting in excel**

Counting conditionally formatted cells (vba)

UPDATE: It is not possible to count conditionally formatted cells using vba as far as I know, I recommend you […]

Geoff asks: Hi Oscar, I have a cross reference table we use for shift scheduling. The x-axis is comprised of […]

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 decribes how to highlight records with closest value to a criterion. You can also choose to highlight the […]

This blog post shows you how to easily identify duplicate rows or records in a list. Conditional formatting formula: =COUNTIFS($B$3:$B$15, […]

How to highlight MAX and MIN value based on month

Conditional formatting formula to highlight max value in every month: =B2=MAX(IF(MONTH(A2)=MONTH(Date_rng), Close, "")) Conditional formatting formula to highlight min value […]

Here is how to highlight every other row using conditional formatting. Conditional formatting formula: =ISEVEN(ROW())*OR($B3:$D3<>"") Alternative CF formula: =EVEN(ROW())=ROW() This […]

Question: I have a list that I keep adding rows to. How do i create a border that expands as […]

Highlight records – multiple criteria [OR logic]

This blog post shows you how to highlight rows with multiple criteria using OR logic. The criteria is found in […]

Highlight duplicates in a filtered Excel defined table

You can highlight duplicates in an excel defined table using conditional formatting. However, that won´t work if you only want […]

### One Response to “Compare two columns and highlight values in common”

### 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

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

[…] Highlight common values in two lists using conditional formatting […]