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: See picture below.

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

Download excel example file
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:

1. Highlight duplicates using conditional formatting in excel
2. Highlight duplicates where adjacent cell value meets criteria using conditional formatting in excel
3. Highlight duplicates where an adjacent column is in a date interval using conditional formatting in excel
4. Highlight common values in two lists using conditional formatting in excel
5. Highlight dates within a date range using conditional formatting
6. Highlight smallest duplicate value in a column using conditional formatting in excel
7. Prevent duplicates using dynamic conditional formatting in excel
8. Highlight duplicates where adjacent cell value meets criteria using conditional formatting in excel part 2
9. Highlight duplicate values in a range using conditional formatting in excel
10. Highlight unique values and unique distinct values in a range using conditional formatting in excel