## Highlight duplicates in two columns

**Question:**

**Answer: **

The following animated image shows you two lists in column B and D.

**Conditional formatting formula:**

#### How to apply the conditional formatting formulas:

- Select cell range
- Click "Home" tab on the ribbon
- Click "Conditional formatting"
- Click "New rule..."
- Click "Use a formula to determine which cells to format"
- Click "Format values where this formula is true" window.
- Type =(COUNTIF($B$3:$B3,B3)+COUNTIF($D$3:$D3,B3))>1
- Click Format button
- Click "Fill" tab
- Pick a color
- Click OK!
- Click OK!

### Explaining formula

#### Step 1 - Check if current value B3 exists in cell range $B$3:$B3

The first argument in the COUNTIF function is a cell reference that expands downwards.

COUNTIF($B$3:$B3,B3)

Cell |
COUNTIF |
Result |

B3 | COUNTIF($B$3:$B3, B3) | 0 |

B4 | COUNTIF($B$3:$B4, B4) | 0 |

B5 | COUNTIF($B$3:$B5, B5) | 0 |

#### Step 2 - Check if current value B3 exists in cell range $D$3:$D3

The first argument in the COUNTIF function is a cell reference that expands downwards.

COUNTIF($D$3:$D3,B3)

Cell |
COUNTIF |
Result |

B3 | COUNTIF($D$3:$D3, B3) | 0 |

B4 | COUNTIF($D$3:$D4, B4) | 0 |

B5 | COUNTIF($D$3:$D5, B5) | 0 |

#### Step 3 - Add results

COUNTIF($B$3:$B3,B3)+COUNTIF($D$3:$D3,B3)

Cell |
COUNTIF |
Result |

B3 | COUNTIF($B$3:$B3,B3)+COUNTIF($D$3:$D3,B3) | 0 |

B4 | COUNTIF($B$3:$B4,B4)+COUNTIF($D$3:$D4,B4) | 0 |

B5 | COUNTIF($B$3:$B5,B5)+COUNTIF($D$3:$D5,B5) | 0 |

#### Step 4 - Check if result is larger than 1

The parentheses makes sure that the order odf calculatiuon is correct.

(COUNTIF($B$3:$B3,B3)+COUNTIF($D$3:$D3,B3))>1

becomes

(0 (zero) + 0 (zero))>1

0>1

and returns FALSE. Cell B3 is not highlighted.

#### Download excel *.xlsx file

Highlight duplicates in two lists using conditional formatting.xlsx

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

Highlight duplicates in a filtered Excel Table

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

Highlight duplicates with same date, week or month

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

Highlight duplicate values in a cell range

The following conditional formula highlights only the second instance or more of a value in a cell range. Conditional formatting […]

How to highlight duplicate values

The picture above shows duplicate values in column B, only the second or more duplicates are colored and easily identified. […]

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