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

Download excel *.xlsx file

Highlight duplicates in two lists using conditional formatting.xlsx