## Compare two columns and highlight values in common

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
- Press with mouse on Conditional Formatting button
- Press with mouse on "New Rule..."

- Press with mouse on "Use a formula to determine which cells to format"
- Type: = COUNTIF($D$3:$D$7, B3)

- Press with mouse on "Format..." button
- Press with mouse on "Fill" tab.
- Pick a color
- Press with left mouse button on OK
- Press with left mouse button on 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.

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

### Cf compare category

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

In this blog post I will demonstrate a conditional formatting formula that will highlight common records in two lists. The […]

The image above demonstrates a conditional formatting formula that highlights records that only exist in one table. There are two […]

This article demonstrates a conditional formatting formula that will highlight the differences between two columns. The image above shows two […]

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

### Conditional formatting category

The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that […]

This article explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range […]

adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]

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

The image above shows rows highlighted based on value in column C being the largest or smallest in that particular […]

This article shows you how to easily identify duplicate rows or records in a list. What's on this webpage Conditional […]

The image above demonstrates a conditional formatting formula applied to an Excel Table containing random data. The Excel Table has […]

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

The image above shows you how to highlight rows with multiple criteria using OR logic. The criteria are found in […]

In this article, I will demonstrate how to search a table using conditional formatting. The criteria highlight matching column and […]

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

The image above demonstrates a conditional formatting formula that highlights duplicate items based on date. The first instance is not highlighted, […]

In this post I am going to try to explain formula basics in conditional formatting. It is really good if […]

The image above shows conditional formatting highlighting unique distinct values, duplicates are not highlighted. Conditional Formatting Formula: =COUNTIF($B$3:B3, B3)=1 The […]

This article demonstrates how to apply different cell formatting to a cell range based on a Drop Down list, column […]

In this blog post I will demonstrate a conditional formatting formula that will highlight common records in two lists. The […]

The image above demonstrates a conditional formatting formula that highlights records that only exist in one table. There are two […]

## Functions in this article

More than 1300 Excel formulas

## Conditional Formatting categories

## Excel categories

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

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

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

Hai

What a surprise..!

I have visited so many websites, youtube channels for my problem.

I have a excel data that is 2 columns,

column 1 is 2,72,132 records

column 2 is 519 records

I have to highlight or select the records that 519 records which are column no.1

But No body have given correct suggesion.

All are given =Match() or =vlookup() formulas.

but not succeded.

At last your help has given correct result

Thank you for great help which is simple but valuable formula i.e., = COUNTIF($D$3:$D$7, B3).

Thanks once again

.. RAVINDER.

Hi

I have the data in two columns and I want to highlight column B value with using column A value if the value is higher than columns A highlight in column B by using condition formatting.

A B

20.00 3.68

100.00 138.84

85.00 102.70

51.00 2.04

8.00 3.27

10.00 14.00

15.00 10.62

kindly suggest the same.