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.

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