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:

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

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!

Download excel *.xlsx file

Highlight duplicates in two lists using conditional formatting.xlsx

Functions used in this blog post:

Learn to use the COUNTIF function

Counts the number of times a value exists in a cell range.