Author: Oscar Cronquist Article last updated on November 19, 2018

Question:

I need to delete duplicates in two different columns together. How do I highlight the second or more duplicates and not the "original" value? This makes it a no-brainer to select and delete duplicate cell values using CTRL + Left mouse button.

Answer:

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

Conditional formatting formula:

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

1. Select cell range
2. Press with left mouse button on "Home" tab on the ribbon
3. Press with left mouse button on "Conditional formatting"
4. Press with left mouse button on "New rule..."
5. Press with left mouse button on "Use a formula to determine which cells to format"
6. Press with left mouse button on "Format values where this formula is true" window.
7. Type =(COUNTIF(\$B\$3:\$B3,B3)+COUNTIF(\$D\$3:\$D3,B3))>1
8. Press with left mouse button on Format button
9. Press with left mouse button on "Fill" tab
10. Pick a color
11. Press with left mouse button on OK!
12. Press with left mouse button on 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.

#### Get excel *.xlsx file

Highlight duplicates in two lists using conditional formatting.xlsx