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.

Conditional formatting formula A2:A6: =COUNTIF(\$A\$2:\$A2, \$A2)>1

Conditional formatting formula B2:B6: =OR(COUNTIF(A2:A6,\$B2)>0, COUNTIF(\$B\$2:\$B2,\$B2)>1)

Customizing excel formulas to your workbook
If your first list starts at C3 and ends at C7, the formula would be =COUNTIF(\$C\$3:\$C3, \$C3)>1. If the second list starts at F3 ,the conditional formatting formula would be =OR(COUNTIF(C3:C7,\$F3)>0, COUNTIF(\$F\$3:\$F3,\$F3)>1)

How to apply the conditional formatting formulas in excel 2007:

1. Select the first list 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(\$A\$2:\$A2, \$A2)>1
8. Click Format button
9. Click "Fill" tab
10. Select a color
11. Click OK!
12. Click OK!
13. Repeat 1-12 with the second list range and second conditional formatting formula

highlight-second-or-more-duplicates-in-two-lists-using-conditional-formatting-in-excel.xls
(Excel 97-2003 Workbook *.xls)

Functions used in this blog post:

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

OR(logical1, logical2, ...)
Checks whether any argument are TRUE and returns TRUE or FALSE. Returns FALSE

### Related posts:

Highlight duplicates using conditional formatting in excel

Highlight common values in two lists using conditional formatting in excel

Highlight duplicates where adjacent cell value meets criteria using conditional formatting in excel part 2

Highlight duplicates where adjacent cell value meets criteria using conditional formatting in excel

Highlight duplicates where an adjacent column is in a date interval using conditional formatting in excel